|
Management has a report that shows revenue per customer for each month of a 13-month period. The primary data appears in a crosstab. In the output, the row headings are customer names and the column headings are month names, with the data being the revenue for that customer for that month.
Now they want me to interleave a comparison of the prior year's total and percentage change from the prior year--at the row level. That is, they want, for each row, to see the existing total for the month, below that, the total from the same month the prior year, and below that the percentage change.
Within a regular report, I would just do two subreports within a detail section--one for the prior year total and the other for the percentage change, but is there a way to do a subreport within a crosstab? If so, I cannot see it.
Feel free to tell me this is impossible (at least using a crosstab) so that I can just tell management that!
I thought of doing two crosstab subreports, one for the requested year, the other for the year before. But that would provide no way to interleave the data per row as they want; it would simply be one year's report, then the next.
I could create a formula that contains the current total, then a carriage return, then the prior total, another carriage return, and the percentage change from prior to current. That way, I could get one field to have three lines. But I can still see no way to get that into a crosstab.
The only way I think I can do this is to drop the crosstab entirely, format the report with 13 columns, and use formulas and subreports to populate it.
Am I stupid, or just crazy?
|
|
No answer yet, so I will post what I ended up using. Rather than linking the tables visually in CR and relying on CR to crosstab by month, I simply created a Command consisting of a complex SQL statement that extracted the totals by month for each required month--both for the prior year and current year. I then embedded the date-related parameter into the Command.
I used a formula for each column heading that generates the monthname based on the month # for that column. In my detail section, I stacked the prior year's total above the same month for the current year to interleave the data.
Of course, I lost the efficiency inherent in having CR do the by-month crosstab summarization, instead having the Command SQL statement summarize all of it for me, but at least I have the flexibility that does not existing within a crosstab to do what I want with the data.
|
IP Logged