sql - "Rolling up" groups in Jaspersoft iReport

Question

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.

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.

Thanks, Lisa

EDITED TO ADD CODE:

 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

Answer 1

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.

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 "

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}

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.

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

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.