“滚动” Jaspersoft iReport中的群组 [英] "Rolling up" groups in Jaspersoft iReport

问题描述

我有一份盈亏报告，目前有三个级别的分组：
1.药房
2.客户
3.包装类型

I have a profit and loss report that currently has three levels of grouping: 1. Pharmacy 2. Customer 3. Packaging Type

我已设置报告以提示用户为药房，客户和包装类型提供值。细节带显示收入和保证金等指标。选择药房，客户或包装类型的默认值将返回组中的所有值。

I've set up the report to prompt the user to provide values for pharmacy, customer, and packaging type. The detail band displays measures like revenue and margin. Selecting the default for pharmacy, customer, or packaging type returns all values in the group.

我想知道 - 如果用户选择使用默认值，有什么办法吗？ 对于一个参数，告诉iReport汇总分组？现在，如果我选择药房和客户但是使用默认的包装类型，我仍然会为每种包装类型获得一个细节带。我怎样才能告诉iReport总结所有包装类型？或者，如果没有指定客户，请对给定药房的所有客户求和？或者我可以在查询中执行此操作吗？我有一个Oracle数据库，我正在使用iReport Professional 4.5.1。

I'm wondering - is there any way, if the user selects "use default" for a parameter, to tell iReport to roll up the grouping? Right now, if I select a pharmacy and a customer but use the default for packaging type, I still get a detail band for each of several packaging types. How can I tell iReport to sum on ALL packaging types? Or, if no customer is specified, sum on ALL customers for a given pharmacy? Or could I do it in the query? I have an Oracle database and am using iReport Professional 4.5.1.

谢谢，
Lisa

Thanks, Lisa

已编辑添加代码：

 SELECT
      FAC.FILL_MONTH AS FILL_MO,
      FAC.PHAR_CODE AS PHAR_CODE,
      FAC.FAC_ID AS FAC_ID,
      FAC.PACKTYPE_CODE AS PACKTYPE,
      SUM(FAC.TOT_RXCOUNT_NUM) AS RX_COUNT,
      SUM(FAC.TOT_REVENUE_AMT) AS REVENUE,
      SUM(FAC.TOT_COGS_AMT) AS COGS,
      SUM(FAC.TOT_MARGIN_AMT) AS MARGIN
 FROM
      MySchema.Table FAC
 WHERE
      FAC.FILL_MONTH BETWEEN $P{startdate} AND $P{enddate}
      AND $X{IN, FAC.PHAR_CODE, pharmacy}
      AND $X{IN, FAC.FAC_ID, facility}
      AND $X{IN, FAC.PACKTYPE_CODE, packtype}
 GROUP BY
      FAC.PHAR_CODE,
      FAC.FAC_ID,
      FAC.PACKTYPE_CODE,
      FAC.FILL_MONTH
 ORDER BY
      PHAR_CODE ASC,
      FAC_ID ASC,
      PACKTYPE ASC,
      FILL_MO ASC

推荐答案

你有正确的SQL来获得原始的你想要的数据。但是你需要修改它以强制 PACKTYPE （和其他字段）在用户指定没有 PACKTYPE <的特殊情况下进行不同的分组/ code>。

You have the SQL correct to get exactly the raw data that you want. But you'll need to modify it to force the PACKTYPE (and other fields) to group differently in the special case where the user specifies no PACKTYPE.

创建一个新参数 $ P {PACKTYPE_SELECT_SQL} 。其默认值直接基于现有参数 $ P {packtype} 的值。 （这意味着 $ P {packtype} 必须首先出现在.jrxml中。）设置 $ P {PACKTYPE_SELECT_SQL} ' s的默认值是这样的：

Create a new parameter $P{PACKTYPE_SELECT_SQL}. Its default value is directly based on the value of your existing parameter $P{packtype}. (This means $P{packtype} must appear first in the .jrxml.) Set $P{PACKTYPE_SELECT_SQL}'s default value to something like this:

$P{packtype}==null ? " 'All Package Types' " : " FAC.PACKTYPE_CODE "

然后像这样修改SQL查询（只修改了一行，其余只是上下文）：

Then modify the SQL query like this (only a single line is modified, the rest is just for context):

SELECT
     FAC.FILL_MONTH AS FILL_MO,
     FAC.PHAR_CODE AS PHAR_CODE,
     FAC.FAC_ID AS FAC_ID,
     $P!{PACKTYPE_SELECT_SQL} AS PACKTYPE,
...
WHERE
     FAC.FILL_MONTH BETWEEN $P{startdate} AND $P{enddate}
     AND $X{IN, FAC.PHAR_CODE, pharmacy}
     AND $X{IN, FAC.FAC_ID, facility}
     AND $X{IN, FAC.PACKTYPE_CODE, packtype}

在 $ P {packtype} 不为null，则生成的SQL将与之前完全一样。但是在 $ P {packtype} 为空的情况下，您现在将获得一个硬编码的字符串来代替PACKTYPE_CODE。

In the case where $P{packtype} is not null then the generated SQL will be exactly as it was before. But in the case where $P{packtype} is null you'll now get a hard-coded string in place of the PACKTYPE_CODE.

据推测，您的报告正在 PACKTYPE 上进行分组。因此，您应该能够保持报告中的布局和分组不变。

Presumably your report is grouping on PACKTYPE. So you should be able to leave the layout and grouping in the report unchanged.

您可以想象添加布尔输入控件等变量，以明确让用户选择是否保留报告中的PACKTYPE与否。但是这个基本想法应该能满足你的需求。

You could imagine variations like adding a Boolean input control to explicitly let the user choose whether to keep the PACKTYPEs in the report or not. But this basic idea should get you what you need.

这篇关于“滚动” Jaspersoft iReport中的群组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！