Hey all,
I have a crosstab to design which looks like the following:
Total Item A Item B Item C
Credit Card 90 20 30 40
Cash 210 60 70 80
Check 330 100 110 120
Money Order 0 0 0 0
Grand Total 630 180 210 240
100% xx% yy% zz%
I don't know how to calculate those percentages. I tried with a data item and then inserting the data item into the crosstab but Its not working....mainly because the data item works for the whole crosstab as opposed to just one column
so, of I do a data item that does a (Item B/cash+check+credit card+money order)*100, the same number appears below Item A B and C obviously
Any suggestions?
Also, is it possible to insert sub total rows of say, credit card+cash before the check row and then a subtotal of check before the money order row? I'm thinking no, but thought I would ask anyway ;)
Thanks for help/suggestions
just a thought, but couldn't you set up the crosstab in Analysis Studio (add in the subtotals as well as the % of column total) then open the report in Report Studio for the final aesthetics?
If you have Multi-Dimensional metadata (MDR, PowerCube, CDS, BW, etc) You will need to use the tuple( ) function and wrap the Value( ) function around that. Tuple allows you to state explicitly what intersection you want to use and Value will return the Value at that intersection.
Please do a search in RS help for more detail... there is a really good explanation of these and other functions.
Value(Tuple(member,{member}))
so... in your case, the un-named % Row edge's expression would be something like:
[Grand Total]/Value(Tuple([Total],[Grand Total]))
If you have relational metadata, then you will probably have to add some aggregated columns to the model that can be used to perform your calculation.
HTH.... Mark
Thanks for your reply.
Jguevin - Opening up an AS report in report Studio causes a lot of problems, especially if a drill through is required.
rockytopmark - I tried the tuple thing, but I keep getting errors about members on the edge. I tried changing the order etc.
MMaybe I'm doing something wrong. Can you please tell me again how I could get the xx% for Item A? Because, the yy% and zz% would follow too.
Thanks
As I wrote previously, use this calculation:
[Grand Total]/Value(Tuple([Total],[Grand Total]))
Your CrossTab will have 2 Column Edges... one is the Member of the Parent, [Total] and the other is the Level which will return the Items. You will have 2 Row Edges, 1 for the level to return the types and the other is the member of the parent, [Grand Total]
You may need to substitute the correct names. What I have given you is based on your example