Calculation in Crosstab in Report Studio 8

[Swathi37]

Hi All,
I am a newbie in Report Studio. I am having toughtime bringing in the subtotals and grand total in a simple nested crosstab report. The data with the report looks like the following.

Cnt of ProdID ABC PQR TOTAL
2005 2006 DELTA 2005 2006 DELTA 2005 2006 DELTA
Q1 JAN 20 30 (30/20)-1 10 20 (20/10)-1 25 33 (33/25)-1
Q1 FEB 10 30 (30/10)-1 10 30 (30/10)-1 20 33 (33/20)-1
Q1 MAR 20 30 (30/20)-1 10 20 (20/10)-1 25 33 (33/25)-1
SUBTOTAL 50 90 (90/50)-1 30 60 (60/30)-1 70 99 (99/70)-1
Q1 APR 20 0 (30/20)-1 10 20 (20/10)-1 25 33 (33/25)-1
Q1 MAY 10 0 (30/10)-1 10 30 (30/10)-1 20 33 (33/20)-1
Q1 JUN 20 30 (30/20)-1 10 20 (20/10)-1 25 33 (33/25)-1
SUBTOTAL 50 90 (90/50)-1 30 60 (60/30)-1 70 99 (99/70)-1
GRAND TOTAL same as the subtotal DELTA is calculation.

* how to insert calculation(for delta for each product)? I could insert the calculation for the first product but doesn't reflect that for all the products. should I insert the calculation for each of the product? Is there a way to insert the calculation for one product and reflect the same for n number of products in the crosstab automatically?

* How to get the subtotals, grand total and summary column totals with delta on the above nested crosstab? This was so simple in impromptu crosstab using advanced..

Your help is greatly appreciated!!!
Thanks
Swathi

 

[Swathi37]

Hi All,

Does anyone has any workaround for this problem? Please help!!

Thanks
Swathi

 

[almeids]

Swathi,
Re your first question: if your product list is not dynamic, you can get away with inserting calculations for each as you indicated. If it is dynamic, the only way I know of to get the crosstab to do the work is to create another tabular set in the report which will yield a same-shaped result set with the string 'DELTA' in the year position, and the delta value in the measure position, and use a tabular set to union this with your existing query (since digits collate before alphas the delta should come up appropriately after the years, in other situations you may need to calculate a sort column).
Re your second question: I too had a heck of a time figuring out how to add subtotals/totals to a crosstab, for me it was a simple matter of realizing I could select objects in the report layout and click the total icon on the button bar, perhaps the same issue for you? I don't recall whether it's dimensions or measures that need to be selected...
good luck
Steve

 

[Swathi37]

Thanks a lot Steve for your response !!!
I finally could get a workaround for my issue. I found that I had to declare individual dimensions like Quarters and Months in my powerplay cube so that it is not broken down again by 2005 Q1, 2005Q2 etc and then publish that cube for creating reports on Report Studio (I used a PP cube for my report). Then it was so simple to get the report I wanted. Also bringing in my sub totals and grand totals was simple too( Just drag and drop!!)
Thank you!!
Swathi