Pentaho Report Designer生火指南

author @fyunli

一、认识Pentaho Report Designer

Pentaho Report Designer

二、设计第一个报表

1、添加数据源

1、点击右侧Data选项卡添加数据源:

2、在打开的界面中创建一个新的连接:

3、然后新建查询

点击Preview可预览数据

2、设计报表格式

2.1 逐行数据显示

将数据项拖动到Detail Body区块即可

2.2 增加一个图表

1、从左侧工具栏将图表拖动到Report Header区域

2、双击图表进去设置属性

2.3 分组

右侧Structure选项卡右键点击Master Report,选择Add Group即可。

3、预览报表

点击预览设计区左上角预览图标进行预览:

三、为报表添加参数

  • 右侧Data选项卡下方Parameters - Add Parameter
  • 打开的界面定义参数:
  • 数据源中引用参数

使用${param}格式嵌入Query中即可,如定义参数名称为category,则类似Query如下

select * from product where catagory = ${category}

四、函数与表达式

右侧Data选项卡Functions - Add Function

选择内置的函数,同行选择Running函数做一些聚合计算。

然后点击新建的函数,在下方属性中定义名称及表达式:

五、发布报表

菜单选择File - Publish

然后输入Pentaho BI地址和用户名、密码:

然后填写发布名称、路径等:

点击"OK“等待发布完成。

发布完成后即可在BI Server上浏览报表:

六、项目中引用报表

  1. 使用Java API调用Pentaho Report Engine直接渲染输出

如:

        try {
            URL urlToReport = new URL("file:" + request.getServletContext().getRealPath("WEB-INF/report/" + reportFile));

            ResourceManager manager = new ResourceManager();
            manager.registerDefaults();
            Resource res = manager.createDirectly(urlToReport, MasterReport.class);
            MasterReport report = (MasterReport) res.getResource();
            if (MapUtils.isNotEmpty(params)) {
                for (String key : params.keySet()) {
                    report.getParameterValues().put(key, params.get(key));
                }
            }

            if ("pdf".equalsIgnoreCase(rendererType)) {
                response.setHeader("Content-disposition", "filename=" + System.currentTimeMillis() + ".pdf");
                response.setContentType("application/pdf");
                PdfReportUtil.createPDF(report, response.getOutputStream());
            } else {
                response.getWriter().write("不支持的报表格式!");
            }
        } catch (Exception e) {
            try {
                e.printStackTrace();
                response.getWriter().write("报表生成失败!");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }

  1. 通过BI Server的URL进行嵌入

URL格式如下:

http://10.142.80.231:8080/pentaho/api/repos/%3Ademo%3Abtopic2.prpt/report

七、TroubleShooting

  • PDF格式中文乱码问题

对中文元素使用设置为中文字体,如宋体等。

  • 引用Report地址,Pentaho BI Server弹出框要求输入用户名,密码

修改pentaho-solutions/system/applicationContext-spring-security.xml,在filterChainProxy中api, plugin对应增加requestParameterProcessingFilter。修改后如下:

  <bean id="filterChainProxy" class="org.springframework.security.util.FilterChainProxy">
    <property name="filterInvocationDefinitionSource">
      <value>
        <![CDATA[CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
        PATTERN_TYPE_APACHE_ANT
        /webservices/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS
        /api/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,requestParameterProcessingFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS
        /plugin/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,requestParameterProcessingFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS
        /**=securityContextHolderAwareRequestFilter,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,httpSessionReuseDetectionFilter,logoutFilter,authenticationProcessingFilter,basicProcessingFilter,requestParameterProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilter,filterInvocationInterceptor]]>
      </value>
    </property>
  </bean>

推荐阅读更多精彩内容