转-Pentaho技术白皮书中文版（四）--创建 dashboards

偏执的太偏执、 2022-01-15 01:01 175阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

这篇文档描述了如何使用 Pentaho BI 套件创建 dashboards。它描述了 Pentaho Demo 服务器提供的sample dashboard。

sample dashboard 以 JSP 和 PHP 形式提供。两个 dashboards 均使用相同的内容，提供相同的功能。JSP 实例显示了如何在 Pentaho UI 组件中使用 Java API，PHP 实例显示了如何在 Pentaho UI 组件中使用 Web 服务。

Java API 实例可用于 JSPs，Servlets，或 Java 应用。Web 服务实例可用于可发布 web 服务调用的任何技术，例如 PHP，IBM Domino 等。

**1. Sample Dashboard**

This page last changed on Mar 01, 2007 by wgorman.

Sample Dashboard 位于这个 URL (假设你将 Pentaho demo 安装于你本机的 8080 端口) 。[http://localhost:8080/pentaho/jsp/SampleDashboard][http_localhost_8080_pentaho_jsp_SampleDashboard]

sample dashboard 首先显示了一张饼图，其显示了四个区域中每个的 headcount costs。

![image002.jpg][]

关于如何定义饼图的更多信息请参考下面的 ‘Headcount Spending by Region Pie Chart’。

关于如何获得饼图的数据的更多信息请参考下面的 ‘Headcount Spending by Region Action Sequence’。

当用户点击饼图上的一个切片时，出现了另一张图表，对于选中的区域的每个部门，显示了 headcount budget 和 actual cost 间的不同。

![image004.jpg][]

饼图如何被定义的更多信息请参考下面的 ‘Headcount Variance Pie Chart’。

如何获取饼图的数据的更多信息请参考下面的 ‘Headcount Variance Action Sequence’。

当用户点击一个部门的 bar 时，dashboard 在图表下，显示了一个 dial 和一个数据表。dial 基于当前时间(因此它随着时间改变) 显示了一个值，表显示了选中部门内的 positions 的详细信息。

![image006.jpg][]

如何定义 dial 的更多信息请参考下面的 ‘动态 Dial’。

如何定义嵌入式报表的更多信息请参考下面的 ‘嵌入式报表’。

当用户点击表中的一个 position 的名字时，dashboard drills 到另一个页面，并传送 region，department 和 position 作为参数。

![image008.jpg][]

这一页的源代码请参考附录 8：Drill JSP

**2.** **体系结构**

This page last changed on Dec 04, 2006 by mdamour.

![image010.jpg][]

**3.** **内容定义**

This page last changed on Dec 04, 2006 by mdamour.

**动态** **Dial**

This page last changed on Dec 05, 2006 by mdamour.

这个 dial 的定义在 pentaho-demo/pentaho-solutions/samples/dashboard/sampledial.widget.xml

这个 XML 文件定义了：

<table> 
 <tbody> 
 <tr> 
 <td> 设置 </td> 
 <td> 描述 </td> 
 <td> 值 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/units </td> 
 <td> units of dial value </td> 
 <td> $ </td> 
 </tr> 
 <tr> 
 <td> widget/dial/plot-background/text </td> 
 <td> dial area&nbsp;的&nbsp;re-image&nbsp;背景 </td> 
 <td> /samples/portal/dial_03 .gif </td> 
 </tr> 
 <tr> 
 <td> widget/dial/tick-interval </td> 
 <td> gap between ticks </td> 
 <td> 5 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/tick-color </td> 
 <td> ticks&nbsp;的颜色 </td> 
 <td> #808080 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/needle-color </td> 
 <td> Needle&nbsp;的颜色 </td> 
 <td> #808080 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/chart-background/texture-image </td> 
 <td> Background chart image </td> 
 <td> /samples/portal/dial_03 .gif </td> 
 </tr> 
 <tr> 
 <td> widget/dial/interval/label </td> 
 <td> Interval name </td> 
 <td> 'under' </td> 
 </tr> 
 <tr> 
 <td> widget/dial/interval/minimum </td> 
 <td> Interval lower limit </td> 
 <td> -15 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/interval/maximum </td> 
 <td> Interval upper limit </td> 
 <td> 0 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/interval/color </td> 
 <td> Interval background color </td> 
 <td> #FFFFFF </td> 
 </tr> 
 <tr> 
 <td> widget/dial/interval/text-color </td> 
 <td> Interval text and tick color </td> 
 <td> #40BB40 </td> 
 </tr> 
 <tr> 
 <td> widget/dial/interval/stroke-width </td> 
 <td> Interval line thickness </td> 
 <td> 5 </td> 
 </tr> 
 </tbody> 
</table>

注意在文件中定义了两个 intervals：一个覆盖了从 -15 到 0 的范围，另一个是 0 到 +15。

除了在 widget XML 文件中定义的这些设置，caller 还指定了这些设置：

在 sample dashboard 中，基于当前时间，页面为 dial 创建了一个值。

**嵌入式报表**

This page last changed on Jan 30, 2007 by wgorman.

报表模版的定义在：pentaho-demo/pentaho-solutions/samples/dashboard/jsp/embedded\_report.xml

报表模版被报表引擎(JFreeReport) 用于布局嵌入式报表。你可使用 Pentaho Report Wizard 设计一个报表定义。

你创建的是 HTML 的一个片段，而不是一个完整的 HTML 页面。你需要编辑报表定义 XML 文件以在 report/configuration 的报表配置区域添加一个属性。

为将报表内容中的一个 drill link 添加进一个 URL，你需要编辑报表定义，并添加一个 URL 表达式，例如 sample dashboard 中的这个。

<expression name="URLCreateExpression"

class="org. jfree.report.function.TextFormatExpression">

<property

name="pattern">SampleDrill? region=\{ 0\} &position=\{ 1\} & department=\{ 2\} </property>

<property name="field\[ 0\] ">REGION</property>

<property name="field\[ 1\] ">POSITIONTITLE</property> <property name="field\[ 2\]">DEPARTMENT</property>

</properties>

</expression>

**Headcount Spending By Region Pie Chart**

This page last changed on Jan 30, 2007 by wgorman.

这张饼图的定义在： pentaho-demo/pentaho-solutions/samples/dashboard/regions.widget.xml

这个 XML 文件定义了：

<table> 
 <tbody> 
 <tr> 
 <td> 设置 </td> 
 <td> 描述 </td> 
 <td> 值 </td> 
 </tr> 
 <tr> 
 <td> chart/title </td> 
 <td> 图的标题 </td> 
 <td> Headcount Spending by Region </td> 
 </tr> 
 <tr> 
 <td> chart/title-position </td> 
 <td> 图的标题的位置(上，下，左或右) </td> 
 <td> top </td> 
 </tr> 
 <tr> 
 <td> chart/title-font/font-family </td> 
 <td> 标题字体的名字 </td> 
 <td> Ariel </td> 
 </tr> 
 <tr> 
 <td> chart/title-font/size </td> 
 <td> 标题字体的大小 </td> 
 <td> 20 </td> 
 </tr> 
 <tr> 
 <td> chart/title-font/is-bold </td> 
 <td> 标题字体是粗体么？ </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> chart/title-font/is-italic </td> 
 <td> 标题字体是斜体么？ </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> chart/is-3D </td> 
 <td> 饼图是&nbsp;3D&nbsp;的么？ </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> chart/border-visible </td> 
 <td> Does chart have a border? </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> chart/include-legend </td> 
 <td> Does chart have a legend? </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-solution </td> 
 <td> solution for action sequence providing data for this chart </td> 
 <td> samples </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-path </td> 
 <td> action sequence&nbsp;的路径 </td> 
 <td> dashboard </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-action </td> 
 <td> action sequence&nbsp;的名字 </td> 
 <td> regions_headcount_data.xaction </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-output </td> 
 <td> action sequence&nbsp;输出 </td> 
 <td> rule-result </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-name </td> 
 <td> 包含&nbsp;pie slice&nbsp;名称的数据列 </td> 
 <td> REGION </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-value </td> 
 <td> 包含&nbsp;pie slice&nbsp;值的数据列 </td> 
 <td> ACTUAL </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-orientation </td> 
 <td> direction in which to process data to get pie slices (rows/columns) </td> 
 <td> rows </td> 
 </tr> 
 </tbody> 
</table>

除了 widget XML 中定义的这些设置，调用者还可指定这些设置：

chart 组件从这个文件中读取所有的设置，然后执行指定的 action sequence 为图表获取数据。sample dashboard 中的 action sequences 仅使用一个单一的组件生成数据，但是 BI 组件的任何序列可用来执行复杂的(例如 scripting 或 in-line ETL) 或远程的(例如 Web 服务) 任务。

**Headcount Variance Bar Chart**

This page last changed on Jan 30, 2007 by wgorman.

这个 bar chart 的定义在：

pentaho-demo/pentaho-solutions/samples/dashboard/departments.widget.xml

这个 XML 文件定义了：

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; </td> 
 <td> 设置 </td> 
 <td> 描述 </td> 
 <td> 值 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/type </td> 
 <td> 图的类型(BarChart, LineChart, AreaChart) </td> 
 <td> BarChart </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/title </td> 
 <td> 图的标题 </td> 
 <td> Headcount Variance </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/title-position </td> 
 <td> 图的标题的位置(上，下，左，右) </td> 
 <td> top </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/title-font/font-family </td> 
 <td> 标题字体的名字 </td> 
 <td> Ariel </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/title-font/size </td> 
 <td> 标题字体的大小 </td> 
 <td> 20 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/title-font/is-bold </td> 
 <td> 图的标题是粗体么？ </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/title-font/is-italic </td> 
 <td> 图的标题是斜体么？ </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/is-3D </td> 
 <td> 图是&nbsp;3D&nbsp;的么？ </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/border-visible </td> 
 <td> Does chart have a border? </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/include-legend </td> 
 <td> Does chart have a legend? </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/chart-background </td> 
 <td> chart image&nbsp;的背景。你可定义gradients，&nbsp;textures，images&nbsp;或flat colors </td> 
 <td> type=color #FFFFFF </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/plot-background </td> 
 <td> plot area&nbsp;的背景 </td> 
 <td> type=color #EEEEEE </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/orientation </td> 
 <td> chart&nbsp;的方向(垂直或水平) </td> 
 <td> horizontal </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/is-stacked </td> 
 <td> Is chart stacked? </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/color-palette/color </td> 
 <td> color to use for first series </td> 
 <td> #336699 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/color-palette/color </td> 
 <td> color to use for second series </td> 
 <td> #99CCFF </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/data/data-solution </td> 
 <td> solution for action&nbsp;sequence providing data for this chart </td> 
 <td> Samples </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/data/data-path </td> 
 <td> action sequence&nbsp;的路径 </td> 
 <td> dashboard </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> chart/data/data-action </td> 
 <td> action sequence&nbsp;的名字 </td> 
 <td> regions_headcount_data.xaction </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-output </td> 
 <td> action sequence&nbsp;的输出 </td> 
 <td> rule-result </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-name </td> 
 <td> 包含&nbsp;pie slice names&nbsp;的数据列 </td> 
 <td> REGION </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-value </td> 
 <td> 包含&nbsp;pie slice values&nbsp;的数据列 </td> 
 <td> ACTUAL </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> chart/data/data-orientation </td> 
 <td> direction in which to process&nbsp;data to get pie slices (rows/columns) </td> 
 <td> rows </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

除了在 widget XML 文件中定义的这些设置，caller 还指定了这些设置：

REGION 参数被传送进 action sequence 用作一个查询查询过滤器。

**4. Action Sequences**

This page last changed on Dec 04, 2006 by mdamour.

**嵌入式报表** **Action Sequence**

This page last changed on Dec 04, 2006 by mdamour.

这个报表的定义在： pentaho-demo/pentaho-solutions/samples/dashboard/jsp/embedded\_report.xaction

你可以在 Eclipse IDE 中使用 Pentaho Design Studio 编辑这个文件。这个 action sequence 包含一个 JFree Report 组件，其从 sample 数据库获取数据，使用一个报表模版生成一个HTML 片段。

action sequence 带有参数 ‘region’ 和 ‘department’，其用作查询中的过滤器。报表组件执行这个查询：

<table> 
 <tbody> 
 <tr> 
 <td> select QUADRANT _ACTUALS . REGION, QUADRANT _ACTUALS . DEPARTMENT,&nbsp;QUADRANT _ACTUALS . POSITIONTITLE, QUADRANT _ACTUALS . ACTUAL, QUADRANT _ACTUALS . BUDGET,QUADRANT _ACTUALS.VARIANCE from QUADRANT _ACTUALS where QUADRANT _ACTUALS.REGION = '{region}' and QUADRANT _ACTUALS.DEPARTMENT = '{department}' order by QUADRANT _ACTUALS.REGION, QUADRANT_ACTUALS. DEPARTMENT </td> 
 </tr> 
 </tbody> 
</table>

使用一个报表模板：pentaho-demo/pentaho-solutions/samples/dashboard/jsp/embedded\_report.xml (参考上面的嵌入式报表)

报表组件在一个名为 ‘report’ 的输出中返回 HTML，action sequence 将这个输出转向到 response 的 output stream。

**Headcount Spending by Region Action Sequence**

This page last changed on Dec 04, 2006 by mdamour.

这个 action sequence 的定义在： pentaho-demo/pentaho-solutions/samples/dashboard/regions\_headcount\_data .xaction

你可以在 Eclipse IDE 中使用 Pentaho Design Studio 以编辑这个文件。

这个 action sequence 包含一个 SQL Query Rule，其从 Pentaho demo 服务器提供的 sample 数据库获取一些数据。action sequence 不带任何参数。

SQL Query Rule 执行这个查询：

<table> 
 <tbody> 
 <tr> 
 <td> select REGION, sum(ACTUAL) as ACTUAL from quadrant _actuals group by REGION order by ACTUAL </td> 
 </tr> 
 </tbody> 
</table>

并在一个名为 ‘rule-result’ 的输出中返回数据。这个数据用于生成 ‘Headcount Spending By Region’ 饼图。

**Headcount Variance Action Sequence**

This page last changed on Dec 04, 2006 by mdamour.

这个 action sequence 的定义在：pentaho-demo/pentaho-solutions/samples/dashboard/department\_variance\_data .xaction

你可以在 Eclipse IDE 中使用 Pentaho Design Studio 以编辑这个文件。

这个 action sequence 包含一个 SQL Query Rule，其从 Pentaho demo 服务器提供的 sample 数据库获取一些数据。

action sequence 带有一个参数 REGION，其在查询中用作过滤器，SQL Query Rule 执行这个查询：

<table> 
 <tbody> 
 <tr> 
 <td> select department, sum(variance)&nbsp;from quadrant _actuals where region='{ REGION}' group by department </td> 
 </tr> 
 </tbody> 
</table>

在名为 ‘rule-result’ 的输出中返回数据。这个数据用于生成 ‘Headcount Variance By Region’ 饼图。

**5.** **定制 PCI JSP Dashboard**

This page last changed on Feb 01, 2007 by wgorman.

**实例** **1: 将饼图变成柱状图**

这个实例描述了在 SampleDashboard solution 中，将一个饼图转换成一个柱状图所需要进行的修改。为了修改，必须同时更新 widget xml 和 SampleDashboard.jsp 中的 java 代码。

**步骤** **1: 更新 SampleDashboard. jsp 文件**

不要调用 ChartHelper.doPieChart()，而是对 ChartHelper.doChart() 进行 java调用。方法标识是一样的， 包括 widget xml 的位置，可定制参数，输出要写进的 string buffer，user session，存储消息的 messages 对象，和一个 logger 对象（如果可用）。

修改 widget.xml 指向我们的新 \_regions.widget.xml 文件，其在下一步被创建。

柱状图稍不同于饼图，当涉及到它们的 templated inner param 时。用 "\{SERIES\}" 替换 "drill-url" 参数的 "\{REGIONS\}" 部分，其允许图表适当生成 outgoing 链接。

**步骤** **2: 创建 new\_regions .widget.xml 文件**

饼图和柱状图 <chart> xml 文件包含用于定制的不同参数集合。不需要检查所有参数，将departments .widget.xml 复制到新的 \_regions.widget.xml文件，其是一个预存在的柱状图实例。

注意 chart-type 元素，以及各种用于定制柱状图的 style 元素。将 title 元素修改回 "Headcount Spending by Region"，data -> data-action 元素修改回 "regions\_headcount\_data.xaction"。现在我们就有一个配置好的柱状图了。

注意：你必须在 admin 工具中刷新 solution repository，使得对于 widget 的修改生效。

**实例** **2: Swapping Regions 和 Departments**

这个实例描述了在 dashboard 中 swapping regions with departments。

**步骤** **1: 更新 SampleDashboard.jsp 文件**

如果你从上面的实例 1 开始操作的，从实例中删除对于 JSP 的修改。

替换这段代码：

<table> 
 <tbody> 
 <tr> 
 <td> String title = "Select a region"; if( department != null ) { title = "This is headcount spending for " + region + ", " + department; } else if ( region != null ) { title = "This is headcount spending for " + region; } </td> 
 </tr> 
 </tbody> 
</table>

用这段代码：

<table> 
 <tbody> 
 <tr> 
 <td> String title = "Select a department"; if( region != null ) { title = "This is headcount spending for " + department + ", " + region; } else if ( department != null ) { title = "This is headcount spending for " + department; } </td> 
 </tr> 
 </tbody> 
</table>

为将 region pie chart 改成 departments：

用 "Select a Department By Clicking on Pie Chart" 替换 "Select a Region By Clicking on Pie Chart"。

用 "SampleDashboard?department= \{DEPARTMENT\}" 替换 "SampleDashboard?region =\{REGION\}"。

替换这行：

parameters.setParameter( "inner-param", "REGION");

用：

parameters.setParameter( "inner-param", "DEPARTMENT");

用 "new\_departments.widget.xml" 替换 "regions.widget.xml"。

为将 department bar chart 改变成 regions：

替换这行：

if( region != null ) \{

用：

if( department != null ) \{

用 "Select a Region By Clicking on Bar Chart" 替换 "Select a Department By Clicking on Bar Chart "。

替换这行：

parameters. setParameter ( "drill-url", "SampleDashboard? region="+region+"&department=\{ SERIES\}" );

用：

parameters. setParameter ( "drill-url",

"SampleDashboard? department="+department+"&region=\{ SERIES\} " );

替换这行：

parameters.setParameter( "REGION", region );

用：

parameters.setParameter( "DEPARTMENT", department );

替换这行：

parameters.setParameter( "outer-params", "REGION" );

用：

parameters. setParameter ( "outer-params", "DEPARTMENT" );

替换这行：

parameters.setParameter( "inner-param", "DEPARTMENT");

用：

parameters.setParameter( "inner-param", "REGION");

用 "new\_regions.widget.xml" 替换 "departments.widget.xml"。

替换包含这句的两行：

if( department != null ) \{

用：

if( region != null ) \{

**步骤** **2: 创建 new\_departments.widget.xml 文件**

复制 regions.widget.xml 文件成新的 \_departments.widget.xml。

用 "Headcount Spending by Department" 替换 "Headcount Spending by Region"。

用 "departments\_headcount\_data.xaction" 替换 "regions\_headcount\_data .xaction"。

替换这句：

<data-name>REGION< / data-name>

用：

<data-name> DEPARTMENT< / data-name>

**步骤** **3: 创建 departments\_headcou nt\_data.xaction 文件**

复制 regions \_headcount \_data.xaction 文件成 departments \_headcount \_data.xaction。

用 "Return actual headcount costs total for each department" 替换 "Return actual headcount costs total for each region"。

替换 SQL 查询：

select REGION, sum(ACTUAL) as ACTUAL from QUADRANT \_ACTUALS group by REGION order by ACTUAL

用：

select DEPARTMENT, sum(ACTUAL) as ACTUAL from QUADRANT \_ACTUALS group by DEPARTMENT order by ACTUAL

**步骤** **4: 创建 new\_regions.widget.xml 文件**

将文件 departments.widget.xml 复制成新的 \_regions.widget.xml 文件。替换这句：

<data-action>department \_variance \_data. xaction</data-action>

用：

<data-action>region \_variance \_data. xaction</data-action>

**步骤** **5: 创建 region\_variance\_data.xaction 文件**

复制 department \_variance \_data.xaction 文件成 region \_variance \_data.xaction。

用 "Return variance between headcount actual and budget for every region in specified department" 替换 "Return variance between headcount actual and budget for every department inspecified region"。

替换：

<table> 
 <tbody> 
 <tr> 
 <td> &lt;inputs&gt; &lt;REGION type="string"&gt; &lt;sources&gt; &lt;request&gt;REGION&lt;/request&gt;&lt;/ sources&gt; &lt;/REGION&gt; &lt;/inputs&gt; </td> 
 </tr> 
 </tbody> 
</table>

用：

<table> 
 <tbody> 
 <tr> 
 <td> &lt;inputs&gt; &lt;DEPARTMENT type="string"&gt; &lt;sources&gt; &lt;request&gt;DEPARTMENT&lt; /request&gt;&nbsp;&lt;/ sources&gt; &lt;/DEPARTMENT&gt; &lt;/inputs&gt; </td> 
 </tr> 
 </tbody> 
</table>

用 "Define an input called 'DEPARTMENT'." 替换 "Define an input called 'REGION'."。

替换

用：

替换 SQL 查询：

select department, sum(variance) from QUADRANT \_ACTUALS where region in ( \{PREPARE:REGION\} )

group by department

用：

select region, sum(variance) from QUADRANT \_ACTUALS where department in ( \{PREPARE:DEPARTMENT\} ) group by region

**附录****01 Dashboard JSP**

This page last changed on Jan 30, 2007 by wgorman.

这是 JSP Sample Dashboard 的源代码：

<%@ page language="java"

import="java.util.ArrayList,

java.util.Date,

[java.io][].ByteArrayOutputStream,

org.pentaho.core.ui.SimpleUrlFactory,

org.pentaho.messages.Messages,

org.pentaho.core.system.PentahoSystem,

org.pentaho.ui.component.DashboardWidgetComponent,

org. pentaho.core. solution. HttpRequestParameterProvider, org.pentaho.core.solution.HttpSessionParameterProvider,

org.pentaho.core.session.IPentahoSession,

org.pentaho.core.util.UIUtil,

org.pentaho.util.VersionHelper,

org.pentaho.messages.util.LocaleHelper,

org.pentaho.core.solution.ActionResource,

org.pentaho.core.solution.IActionResource,

org.pentaho .core. solution. SimpleParameterProvider, org.pentaho.ui.ChartHelper,

[java. io][java.io] .\*"

%><%

/\*

\* This software was developed by Pentaho Corporation and is provided under the terms

\* of the Mozilla Public License, Version 1.1, or any later version. You may not use

\* this file except in compliance with the license. If you need a copy of the license,

\* please go to [http://www.mozilla.org/MPL/MPL-1.1.txt][http_www.mozilla.org_MPL_MPL-1.1.txt]. The Original Code is the Pentaho

\* BI Platform. The Initial Developer is Pentaho Corporation.

\* Software distributed under the Mozilla Public License is distributed on an "AS IS"

\* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to

\* the license for the specific language governing your rights and limitations.

\* Created Feb 16, 2006

\* [@author][author] James Dixon

\*/

/\*

\* This JSP is an example of how to use Pentaho components to build a dashboard.

\* The script in this file controls the layout and content generation of the dashboard.

\* See the document 'Dashboard Builder Guide' for more details

\*/

// set the character encoding e.g. UFT-8

response. setCharacterEncoding (LocaleHelper.getSystemEncoding ());

// get the current Pentaho session or create a new one if needed

IPentahoSession userSession = UIUtil.getPentahoSession( request );

<html>

<head>

<title>Pentaho Sample Dashboard - JSP</title>

</head> <body>

// See if we have a 'department' parameter String department = request.getParameter ("department"); // See if we have a 'region' parameter

String region = request.getParameter("region");

// Create the title for the top of the page

String title = "Select a region"; if( department != null ) \{

title = "This is headcount spending for " + region + ", " + department;

else if ( region != null ) \{

title = "This is headcount spending for " + region;

<table>

<tr>

<td valign="top">Select a Region By Clicking on the Pie Chart

// Make a pie chart showing the regions // create the parameres for the pie chart

SimpleParameterProvider parameters = new SimpleParameterProvider();

// define the click url template

parameters.setParameter( "drill-url", "SampleDashboard?region=\{ REGION\} " );

// define the slices of the pie chart

parameters. setParameter ( "inner-param", "REGION"); //$NON-NLS-1$ //$NON-NLS-2$

// set the width and the height

parameters. setParameter ( "image-width", "450") ; //$NON-NLS-1$ //$NON-NLS-2$ parameters.setParameter( "image-height", "300"); //$NON-NLS-1$ //$NON-NLS-2$ StringBuffer content = new StringBuffer();

ArrayList messages = new ArrayList();

// call the chart helper to generate the pie chart image and to get the HTML

content

// use the chart definition in 'samples/dashboard/regions.widget.xml' ChartHelper.doPieChart ( "samples", "dashboard", "regions .widget.xml", parameters, content, userSession, messages, null );

<%= content.toString() %>

</td>

if( region != null ) \{

// if the user has clicked on a slice of the pie chart we should have a

region to work with

Select a Department By Clicking on the Bar Chart

// Make a bar chart showing the department

// create the parameres for the bar chart

parameters = new SimpleParameterProvider();

// define the click url template

parameters. setParameter ( "drill-url",

"SampleDashboard? region="+region+"& department=\{ SERIES\} " );

parameters.setParameter( "REGION", region );

parameters.setParameter( "outer-params", "REGION" );

// define the category axis of the bar chart

parameters. setParameter ( "inner-param", "DEPARTMENT"); //$NON-NLS-1$

// $NON-NLS-2 $

// set the width and the height

parameters. setParameter ( "image-width", "450") ; //$NON-NLS-1$ //$NON-NLS-2$ parameters.setParameter( "image-height", "300"); //$NON-NLS-1$ //$NON-NLS-2$ content = new StringBuffer();

messages = new ArrayList();

// call the chart helper to generate the pie chart image and to get the

HTML content

// use the chart definition in 'samples/dashboard/regions.widget.xml'

ChartHelper.doChart ( "samples", "dashboard", "departments .widget.xml", parameters, content, userSession, messages, null );

<%= content.toString() %>

%> </tr>

<tr>

<td colspan="2" valign="top" style="font-family:Arial; font-weight:bold"><hr

size="1"/>

</tr>

<tr>

if( department != null ) \{

// if the user has clicked on a bar of the bar chart we should have a region and department to work with

// create a dial and supply a value we create from the current time

Date now = new Date();

int seconds = now.getSeconds();

// create a value from -15 to +15

int dialValue = -15+seconds/2;

// create the parameres for the bar chart

parameters = new SimpleParameterProvider();

// set the value displayed on the dial

parameters. setParameter ( "value", ""+dialValue );

// set the title for the dial

parameters.setParameter( "title", "My Dial" );

// set the width and the height

parameters. setParameter ( "image-width", "105") ; //$NON-NLS-1$ //$NON-NLS-2$ parameters.setParameter( "image-height", "105"); //$NON-NLS-1$ //$NON-NLS-2$ content = new StringBuffer();

messages = new ArrayList();

// call the chart helper to generate the pie chart image and to get the

HTML content

// use the chart definition in 'samples/dashboard/regions.widget.xml'

ChartHelper.doDial ( "samples", "dashboard", "sampledial.widget .xml", parameters, content, userSession, messages, null );

The value of this dial is based on the current time

<%= content.toString() %>

</td>

if( department != null ) \{

// if the user has clicked on a bar of the bar chart we should have a region and department to work with

// run a report and embed the content into this page

// create the parameres for the report

parameters = new SimpleParameterProvider();

// pass the region and department to the report parameters.setParameter( "region", region );

parameters.setParameter( "department", department ); // create an output stream for the report content

ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); messages = new ArrayList();

// run the action sequence 'samples/dashboard/jsp/report.xaction'

ChartHelper.doAction ( "samples", "dashboard/jsp", "embedded\_report .xaction",

"SampleDashboard", parameters, outputStream , userSession, messages, null ); // write the report content into this page

Click on a position title to drill to another page

<% out.write( outputStream.toString() ); %>

</td>

</tr>

</table>

</body> </html>

**附录****02 Pie Chart 定义**

This page last changed on Jan 30, 2007 by wgorman.

<chart>

<title>Headcount Spending by Region</title>  <title-position>none</title-position>  <title-font>

<font-family>Ariel< /font-family> <size>20</size>

<is-bold>false</is-bold>

<is-italic>false</is-italic> </title-font>

<is-3D> fal s e< / is-3D>

<border-vis ible>false</border-visible> <border-paint>\#bbbbff</border-paint>

<include-legend>false</ include-legend>

<data>

<data-solution>samples</data-solution> <data-path>dashboard</data-path>

<data-action>regions\_headcount\_data.xaction</data-action>

<data-output>rule-result</data-output> <data-name>POSITIONTITLE</data-name>

<data-value>ACTUAL< /data-value>

<data-orientation>columns</data-orientation>

</data>

</chart>

**附录****03 Region 数据**

This page last changed on Jan 30, 2007 by wgorman.

<action-sequence>

<title>Regions and departments</title> <logging-level>debug</logging-level> <documentation>

<author>James Dixon</author>

<description>Return the actual headcount costs total for each region</description>

</documentation>

<outputs>

<rule-result>

</rule-result>

</outputs>

<resources/>

<action-definition>

<action-inputs/>

<action-outputs>

<rule-result type="list"/>

</action-outputs>

<component-name> SQLLookupRule< / component-name> <action-type>rule</action-type>

<component-definition>

<jndi>SampleData</j ndi>

<!-- Define the query to execute. Note the parameter

\{REGION\} in the query -->

<query><!\[ CDATA\[

select REGION, sum(ACTUAL)

from quadrant \_actuals

group by REGION order by ACTUAL\]\]> </query>

</component-definition>

</action-definition>

</actions>

</action-sequence>

**附录****04 Department Bar Chart 定义**

This page last changed on Jan 30, 2007 by wgorman.

<chart>

<chart-type>BarChart</chart-type>

<title>Headcount Variance</title>

<title-position>TOP</title-position>  <title-font>

<font-family>Ariel< /font-family>

<is-bold>false</is-bold> <is-italic>false</is-italic>

</title-font>

<chart-background-color>\#FF80FF</chart-background-color>

<plot-background-color> \#FFFF00< /plot-background-color>

<orientat ion>Ho ri z ontal< / orientation>

<is-3D> fal s e< / is-3D>

<is-stacked>false</is-stacked>

<border-vis ible>true</border-visible> <border-paint>\#000000</border-paint>

<include-legend>true</include-legend>

<color-palette>

</color-palette>

<data>

<data-solution>samples</data-solution>

<data-path>dashboard</data-path> <data-action>department\_variance\_data.xaction</data-action>

<data-output>rule-result</data-output>

<data-orientation>rows< /data-orientation>

</data>

</chart>

**附录****05 Department Variance 数据**

This page last changed on Jan 30, 2007 by wgorman.

这个 action sequence 指定一个 SQL 查询，获取一个指定区域的每个部门的 headcount 的真实值和预算的差值。

action sequence 定义于：

~/pentaho-demo/pentaho-solutions/samples/dashboard/department\_variance\_dial .xaction.

使用 Pentaho BI Studio

<?xml version="1 .0" encoding="UTF-8"?> <action-sequence>

<title>Regions and departments</title> <logging-level>debug</logging-level> <documentation>

<author>James Dixon</author>

<description>Return the variance between headcount actual and budget for every

department in the specified region</description> <help>just testing. . .</help>

</documentation>

<request>REGION</request> </ sources>

</REGION>

</inputs>

<outputs>

<rule-result>

</rule-result>

</outputs>

<action-definition>

<action-inputs>

<action-outputs>

<rule-result type="list"/> </action-outputs>

<component-name> SQLLookupRule</component-name> <action-type>rule</action-type>

<component-definition>

<jndi>SampleData</jndi>

<!-- Define the query to execute. Note the parameter

\{REGION\} in the query -->

</query>

</component-definition>

</action-definition>

</actions>

</action-sequence>

**附录****06 Dial 定义**

This page last changed on Jan 30, 2007 by wgorman.

<dial>

<name>Gauge 1</name>

<plot-background type="texture"> <texture-image>/samples/portal/dial\_03.gif</texture-image> </plot-background>

<tick-interval> 5</tick-interval> <value-color>\#9 999bb</value-color> <tick-color>\#808080</tick-color>

<needle-color>\#808080</needle-color> <chart-background type= "texture">

<texture-image>/samples/portal/dial\_03.gif</texture-image> </chart-background>

<label>under</label>

<color>\#ffffff</color>

<!-- this is the color of the text for the range value and tick

marks -->

<text-color>\#40bb40</text-color>

<stroke-width>5</stroke-width>

</interval>

<color>\#ffffff</color>

<text-color>\#bb4040</text-color> <stroke-width>5</stroke-width> </interval>

</dial>

</widget>

**附录****07 嵌入式报表模板**

This page last changed on Jan 30, 2007 by wgorman.

<?xml version="1 .0" encoding="UTF-8"?>

<action-sequence>

<title>JFreeReport HTML Example</title> <logging-level>debug</logging-level> <documentation>

<author>James Dixon</author> <description><!\[ CDATA\[

This is an example of an HTML report produced by JFreeReport.

It shows the actual headcount cost, budgeted headcount

cost, and variance for every position in the specified

department and region \] \] ></description>

<icon>/style/icons/jfree1 .png</icon>

</documentation>

<request>region</request>

</sources>

</region>

<request>department</request>

</sources>

</department>

</inputs>

<response>content</response>

</destinations> </report>

</outputs>

<resources>

<report-definition>

<solution-file>

<location>embedded\_report.xml</location>

<mime-type> text/xml</mime-type>

</solution-file> </report-definition></resources>

<action-definition>

<action-inputs>

</action-inputs>

<action-outputs>

</action-outputs>

<component-name>JFreeReportComponent< /component -name> <action-type>report</action-type>

<component-definition>

<live>false</live>

<jndi>SampleData</j ndi>

</ query>

<output-type>html< / output-type>

</component-definition>

</action-definition>

</actions> </action-sequence>

**附录****08 Drill JSP**

This page last changed on Jan 30, 2007 by wgorman.

<%@ page language="java"

import="java.util.ArrayList,

java.util.Date,

[java.io][].ByteArrayOutput Stream,

org.pentaho.core.ui.SimpleUrlFactory,

org.pentaho.messages.Messages,

org.pentaho.core.system.PentahoSystem,

org.pentaho.ui.component.DashboardWidgetComponent,

org. pentaho .core. solution. HttpRequestParameterProvider, org. pentaho.core.solution.HttpSessionParameterProvider,org. pentaho.core.session.IPentahoSession,

org.pentaho.core.util.UIUtil,

org.pentaho.util.VersionHelper,

org.pentaho.messages.util.LocaleHelper,

org.pentaho.core.solution.ActionResource,

org.pentaho.core.solution.IActionResource,

org.pentaho .core. solution. SimpleParameterProvider, org. pentaho.ui.ChartHelper,