Reverse Columns/Rows

[riftware]

We have a situation where it is desirable to have a table such that the rows in the database become columns on the report and columns are the rows. So if you had in the database

 

-------Jan-Feb-March-april-may-june-july-august-sept

#days

#hol

event1

event2

event3

 

----------------------------------------------------

 

Would like to display the table like:

 

------#days -#hol-event1-event2-event3

jan

feb

march

april

may

june

july

august

sept

 

 

Where the columns on the display are "dynamic" driven by the rows in the databse..... Any help is appreciated - we're using the latest jasper reports and the latest iReports designer although other designers are certainly an option if they give an additional capability.

[lucianc]

You could try to achieve this using a crosstab, it's the only feature in JR that has a "dynamic" column dimension.

 

Regards,

Lucian

[riftware]

Thanks but unless I've really missed something we can't use cross tabs simply because we haven't figured out a way to get them to display the data - they keep trying to calculate sums of columns and rows etc - btw I think what we're actually looking for is officially called a pivot table - they are quite common - the approach we are considering is a custom query object that returns a dataset that has been "pivoted" - still won't fix everything but I think we're going to make the table as wide as it could possibly be and just set it to not show fields with "no data" so that it looks like the table dynamically expands. If anyone has a better idea I'd love to hear it - or if I'm drastically wrong on the crosstab I'd love to be wrong too. Sample dataset to possibly display:

 

Code:

Database table:

DaysWorked
         1st Day | 2nd day | 3rd day | 4th day
week1:   monday  |  wed.   | Friday  | saturday
week2:   wed     |  thur   | Friday  | saturday
week3:   monday  |  tue    | wed     | thur 



Want display
Days worked

          week1  |  week2  | week3
------------------------------------
1st Day   monday    wed      monday
2nd day   wed       thur     tue
3rd day   Friday    Friday   wed
4th Day   saturday  saturday thur.

Obviously this is made up data but its the idea of what our customers want in their reports.

Post edited by: riftware, at: 2006/10/24 14:22

[lucianc]

You can use a crosstab for this, I've attached a sample report.

 

The only trickier part is to create the static (day 1, day 2, etc) rows. For this, you can either split a cell so that it looks like there are several rows (using this approach, you can't have stretching texts), or you can create dummy row groups and use their total rows.

 

HTH,

Lucian [file name=cross.zip size=1125]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/cross.zip[/file]

[riftware]

Thanks - I've looked at what you did and I think I understand how it works - however we've come up with a solution now I think that works and may be worth contributing back - its multipart but has the advantage of being "automated" if you will inside iReport. It consists of

1: update to ireport wizard so that if the following comment is in the sql query it triggers a little extra field logic -- PivotTable Columns=X where X is the maximum number of columns you want/ever expect.

 

This triggers a change in the ReadFields logic to automatically generate fields with _# appended such that you get a "field" for each element of your grid.

 

2: We created a plugin that will automatically align all these "fields" into a table format with correct headers

 

3: We created a custom query executor that will automatically "rotate" the sql result set so that columns become rows and rows become columns - it performs the same logic we put into step 1's wizard update so that the resultset comes back and matches the fields.

 

Sample workflow:

Generate normal SQL Query that returns back the rows from your normal database table - put the specific comment into the sql statement --PivotTable Columns = 4 (or some number). - Use this query in the normal IReports report wizard. This will give you a list of all the elements of the table - select them all onto right hand side. Finish wizard as normal.

 

Next execute the plugin - this will visually layout the report

 

Finally update the report to use the custom query executor (language type is arbitrarily called "PivotSQL")

 

You are now done and can run your report.