iReport - organizing column output?

4
votes

I am working on a profit and loss report that should look like this: enter image description here

And my data table looks like this:
enter image description here

For this P&L I have query #1 that populates the data for the current month and the 3 months prior. Then I have query #2 to calculate a 6-month rolling average, and then query #3 to calculate a year-to-date total. They're all joined with union statements.

I can't figure out the best way to get this to render in iReport. Right now I am using a crosstab, and I was able to finagle the "measures" labels into the customer column using Bekwam's "Expanding a Crosstab" technique.

So my questions are - is there any easier way to do this? Every time I add a measure to the crosstab I have to rearrange the measures labels and fields. If I made a pivot table in Excel, I could click a drop-down box and choose to show or hide certain months - is there any way to do that in iReport? I feel like I must be missing something. If anyone else out there is displaying data in columns like this, how are you doing it? If you set up your detail band in columns, any tips for organizing output so it looks professional?

Thanks, Lisa

1
jasper-reportsireport
Ok, I think I need to create a columnar report. Looking in the iReports Ultimate Guide, page 26, figure 2.15, I realize that I don't have option 6 (Layout) in my version of iReports (Professional, 4.5.1). Does that option still exist in iReports 4.5.1? – Lisa
I changed my template type of columnar but it would seem that the only difference is that now my field labels get put into the detail band instead of the column header. Is there any way to tell iReport to print only my static labels in column 1, only May-12 in column 2, only Apr-12 in column 3, etc? – Lisa

1 Answers

1
votes

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.