Crosstab in group header

[recoverix]

I'm new in Jasper Reports and I need some help.

I have a report that shows amount of sales for EACH DAY IN MONTH, GROUPED BY COUNTRY(it's important).

forks-and-spoons.jrxml

forks-and-spoons.sql

But crosstabs are the same in each group 

How to fix it? I want each crosstab to display data for specific country of that group.

 

[Raphaël Peguet]

Hello,

If you haven’t found the answer to your question, here is the solution.

1. Add a parameter (let’s call it “country”) to the subDataset.
Note: it is good practice for testing to set a default value for it, so let’s use “France” as the default value.

2. Edit the subDataset query to filter the data on that parameter value and add the following to the WHERE clause of the query

country = $P{country}[/code]

Note: you can drap and drop the country parameter from the parameter tab at the bottom to the right place in the query designer so you don’t risk any syntax errors

3. Edit the main report query to solely do a distinct select of “country”, from your sample there is no need to query more.

select distinct country from items_sales[/code]

Now that we’ve filtered the data in the crosstab to a specific country, we need to pass the country value of the group header (from the main query) to the crosstab (and its subDataset).

3. Select the crosstab and go to the dataset tab in its properties

4. At the bottom of the Parameter panel, click the button “Parameters” (it should say “Parameters”, not “Parameters Map”)

5. Add a mapping and link the country parameter which you should find in the Parameter Name dropdown with the value of the value of the country field ($F{country}, it can be found in the expression designer under Fields)

Once that’s done this should work as expected

[recoverix]

Thanks! That's exactly what I was looking for. I've had no idea, that parameter could be used in dataset sql.

But here's the thing. In existing reports we use JRBeanCollectionDataSource. Can I  somehow replace sql data source with JRBeanCollectionDataSource? I don't understand how to implement this approach when I have JRBeanCollectionDataSource. Or maybe I can group records by country in Crosstab, without group in main report?

[Raphaël Peguet]

An alternative to filtering data in the SQL query is by using the in memory filtering.
Head back to the query editor and at the bottom of the pop up you will find the "Filter Expression" tab where you can limit the output of the records to limit it to when the the field Country is equal to the value of the parameter.
It can be something like:

$F{country}.equals($P{country})[/code]