Need some help with a crosstab off a cube in RS

[Arsenal]

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

[jguevin]

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?

[rockytopmark]

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

[Arsenal]

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

[rockytopmark]

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