Cross Tab Query

[sthakur11]

We  are using Ireports version 3.1 and we are using cross tab query in our report. also we want to have grouping on the fields. can any one tell me how to use Cross Tab in Ireports. 

[phantastes]

Hi,

you should probably be a little more precise in your question. What part of crosstabs do you have problem with?

Best Regards

Magnus

[kchaudhry]

Here are the steps that you would follow to create a simple crosstab report.

1. The query below will show you the Name of the Sales person, the name of the items they have sold and the quantity of items they have sold. Place this query in the main report query section.

select 'John Doe' as 'SalesPerson', 'Tennis Balls' as 'ItemsSold', 2 as 'Quantity'
union all
select 'Jane Doe', 'Baseball Gloves', 3
union all
select 'Jane Doe', 'Pool Table', 5
union all
select 'John Doe', 'Pool Table', 2

2. Go to create new Crosstab and place this in your "Summary" band
3. Select the available dataset. In this example it will be the main report query then click next
4. Select the "SalePerson" field for Row group 1 then click next
5. Select the "ItemsSold" field for Column group 1 then click next
6. Select the "Quantity" field for Detail field and select "Sum" for Function and then click next. **Note** If you are already doing the  calculation in your query then you can set the Function to "Nothing".
7. Depending on your requirement, select the appropriate group totals. For this example we will have both row and column totals then click next or finish
8. Now run the report
9. For formatting changes either go to the "crosstab-1" tab on the top or simply right click on click on "Go to crosstab design tab..."
10. This is all you need to do to create a simple cross tab.

For your convenience, I am also attaching the jrxml of the crosstab that I created using the above mentioned steps.

Hope this helps!

[glassner]

Do you know whether the crosstab has to be always in the summary section of the report ?

Do you know whether one can combine groups and crosstabs (i.e. to print out a crosstab for each group).

 

Thank you !

[shivareddyk]

4 both questions answer is "yes"