"Rolling Up" Groups in Jaspersoft Ireport

Rolling up groups in Jaspersoft iReport

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.

Groups to show many-to-one relationships

As DaDaDom suggested, use one report and two subreports:

Report 1 - Person

1234    John Smith

Subreport 1.1 - Certifications

        01/01/2010 MCSE
        03/01/2010 CISSP

Subreport 1.2 - Degrees

        4/4/2001 B.S. Information Systems
        5/5/2004 M.S. Comupter Science

Combined Results

Then:

1. Add a Group band to the main report.
2. Group By the employee ID.
3. Include the Certificates subreport in the Group band.
4. Include the Degrees subreport in the Group band.
5. Pass the employee ID from the main report to the subreports.
6. Have each subreport query the requisite information based on the employee ID parameter that they receive.

Jaspersoft iReport Can we do subtotals in a list component?

There are many limitations of List component (calculations, return values, headers and footers, ...).
See section "13.1.3 List Component Issues" in ireport-ultimate-guide.

Try to use subreport instead list. It is more suited for computation on a subset of data

iReports Grouping Bug - Multiple Occurrences?

I think this is a data sorting problem.

The quote from iReport Ultimate Guide:

JasperReports groups records by evaluating the group expression. Every
time the expression's value changes, a new group instance is created.
The engine does not perform any record sorting (if not explicitly
requested), so when we define groups we should always take care of the
records sorting. That is, if we want to group a set of addresses by
country, the records we select for the report should already by
ordered by country. It is simple to sort data when using an SQL query
by using the ORDER BY clause. When this is not possible (that is, when
obtaining the records from an XML document), we can request that
JasperReports sort the data for us. This can be done using the sort
options available in the query window

---

You can sort data in these ways:

• in case using of Database jdbc connection datasource type you can add ORDER BY customerId clause to the report's query, where customerId - column name of field with customer id
• in case using of File csv connection or something like this you can organize data sorting by adding sortField property for field to the report's template (jrxml file):

<jasperReport ...>
    ...
    <field name="customerId" class="java.lang.String"/>
    <sortField name="customerId"/>

iReport - organizing column output?

I decided to create columns in the detail band. I put my measure labels in the first column and set them to print in that column only. Then I have my measures values print in each column. I wish I could tell iReport to print the measures values in columns 2-n, but I don't think that's possible.

Some customers will not have a value for each month in Query #1, so I've decided to join Query #1 on a calendar table (calendar table left join query #1) to add blanks as placeholders to preserve my formatting.

---

Related Topics