Percentage calculation in nested crosstab populated by PPlay

[Ricardo Julio "Ricky" Villa]

Hi,

I have a Report Studio query populated from a PowerPlay Cube with a crosstab on the report page.

Crosstab consists of:

Nested rows: Row A, Row B, Row C
Columns: Measure D

Row A and Row B are from the same Dimension / Hierarchy.
Row A is parent of Row B in Dimension / Hierarchy.
Row C is from a different Dimension / Hierarchy.

I need to add a new column to show a Measure D percentage defined as:

Measure D / total ( Measure D for each Row A member )

i.e. subtotal for each Row A member = 100%

Been playing around, but struggling to achieve... Any suggestions very much appreciated.

Cheers,

Ricky (COYS)

[MFGF]

Hi,

I have a Report Studio query populated from a PowerPlay Cube with a crosstab on the report page.

Crosstab consists of:

Nested rows: Row A, Row B, Row C
Columns: Measure D

Row A and Row B are from the same Dimension / Hierarchy.
Row A is parent of Row B in Dimension / Hierarchy.
Row C is from a different Dimension / Hierarchy.

I need to add a new column to show a Measure D percentage defined as:

Measure D / total ( Measure D for each Row A member )

i.e. subtotal for each Row A member = 100%

Been playing around, but struggling to achieve... Any suggestions very much appreciated.

Cheers,

Ricky (COYS)

Still working on that report Ricky my old Argentinian footballer friend? :)

Try this approach:

[Measure D] / completeTuple([Measure D], ancestor(currentMember([your Products hierarchy]),[Your Row A level from the Products hierarchy]))

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Hi,

Thanks for your reply.

A different report which is a variation of the last one ;)

Unfortunately, it is not working.

It produces percentages, but they are not the expected results and I'm struggling to work out what percent (of what) they are showing.

Have you been able to get this working using the great outdoors?  In which case, there is an issue with my cube / report.

By [your Products hierarchy] do you mean the hierarchy that row A belongs to?

Cheers,

Ricky

[MFGF]

Hi,

Thanks for your reply.

A different report which is a variation of the last one ;)

Unfortunately, it is not working.

It produces percentages, but they are not the expected results and I'm struggling to work out what percent (of what) they are showing.

Have you been able to get this working using the great outdoors?  In which case, there is an issue with my cube / report.

By [your Products hierarchy] do you mean the hierarchy that row A belongs to?

Cheers,

Ricky

What's the solve order of the percent calculation in your column? Have you tried setting it to 2?

Yes - I mean the hierarchy A (and B) belong to.

It works fine for me using the great_outdoors_sales_en Powercube. I have attached the report spec for your delight and entertainment :)

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Hi,

Yes just tried solve order = 2 but same incorrect results.

Thanks for the report spec.

Can you please send me a copy of the great outdoors cube?

Is it too big to attach here?

Thanks

[MFGF]

Hi,

Yes just tried solve order = 2 but same incorrect results.

Thanks for the report spec.

Can you please send me a copy of the great outdoors cube?

Is it too big to attach here?

Thanks

Actually, I'm willing to bet you already have it. It ships as part of the standard Samples install. :)

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Hi,

Yep found it and deployed thanks.

So I can get the sample report and my report working OK now.

However, the plot thickens as I neglected to mention that I have Months nested on top of the Measure Columns which results in the incorrect percent results.

I have nested Sales Region level on the top of the measure columns in your sample report and replicated the issue.

Are you kindly able to modify the calc to account for this?

Cheers

[MFGF]

Hi,

Yep found it and deployed thanks.

So I can get the sample report and my report working OK now.

However, the plot thickens as I neglected to mention that I have Months nested on top of the Measure Columns which results in the incorrect percent results.

I have nested Sales Region level on the top of the measure columns in your sample report and replicated the issue.

Are you kindly able to modify the calc to account for this?

Cheers

Hi Rickby,

I just nested Sales Region above the measures in the Columns area of my crosstab, and it appears to work fine for me. Each percentage is now broken down by Sales Region - so as you add them across a row you get 100% for all regions. See below to see what I mean:



Do you need something different?

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Hi,

Yep I need:

Personal Accessories / Americas column and to add up to 100%
Personal Accessories / Asia Pacific column and to add up to 100%
Personal Accessories / Central Europe column and to add up to 100%
etc etc
Camping Equipment / Americas column and to add up to 100%
Camping Equipment / Asia Pacific column and to add up to 100%
Camping Equipment / Central Europe column and to add up to 100%
etc etc

At the moment it's adding up to 100% horizontally across, but I need it to be adding up to 100% vertically down.  If that makes sense.

My report is actually not replicating the sample as it is, but that might be due to other functions being used.  So probably best to get it working using the sample first...

Any time you have very much appreciated... but it's Friday now and I'm sure we both want the pub ;)

Cheers...

[MFGF]

Hi,

Yep I need:

Personal Accessories / Americas column and to add up to 100%
Personal Accessories / Asia Pacific column and to add up to 100%
Personal Accessories / Central Europe column and to add up to 100%
etc etc
Camping Equipment / Americas column and to add up to 100%
Camping Equipment / Asia Pacific column and to add up to 100%
Camping Equipment / Central Europe column and to add up to 100%
etc etc

At the moment it's adding up to 100% horizontally across, but I need it to be adding up to 100% vertically down.  If that makes sense.

My report is actually not replicating the sample as it is, but that might be due to other functions being used.  So probably best to get it working using the sample first...

Any time you have very much appreciated... but it's Friday now and I'm sure we both want the pub ;)

Cheers...

Notwithstanding the alluring call of the pub, is this what you mean?



If so, you simply need to bring the sales region members into the completeTuple() function, ie

[Quantity sold] / completetuple([Quantity sold], ancestor(currentMember([great_outdoors_sales_en].[Products].[Products]),[great_outdoors_sales_en].[Products].[Products].[Product line]),currentMember([great_outdoors_sales_en].[Sales regions].[Sales regions]))

I have attached the new report spec...

Happy Friday! Enjoy the pub!

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Hi,

Thanks a lot, that works!

Just trying to understand the logic... Why completeTuple instead of Tuple...?

Cheers,

Ricky

[MFGF]

Hi,

Thanks a lot, that works!

Just trying to understand the logic... Why completeTuple instead of Tuple...?

Cheers,

Ricky

Tuple acts within the context of the item's current position within the crosstab - so you're only ever going to get values within context of the Sales Region parent member. It uses the current member of the parent nested sets rather than the default member. CompleteTuple acts outside of the positional context, and uses the default member rather than the current member. The help in the expression dialog is pretty good here - worth a read:

Code Select Expand

completeTuple ( member { , member } )
Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. However, completeTuple () implicitly includes the default member from all dimensions not otherwise specified in the arguments, rather than the current member. CompleteTuple will use the default measure rather than the currentMeasure in the query if the measure is not defined in the completetuple function. This function appears in the Planned Headcount sample report in the GO Data Warehouse (analysis) package.
Example: completeTuple ( [Mountaineering Equipment] , [Fax] )
Result: The completeTuple does not pick up the currentMember by default as the tuple function does. The values in the first column are identical across each year because the default member of the Years dimension, the root member, is used rather than the current member. Likewise, the first column displays Revenue rather than Quantity Sold because the Revenue measure is the default from the Measures dimension.
CompleteTuple will use the default measure rather than the currentMeasure in the query if the measure is not defined in the completetuple function.
Example: completeTuple ( [Mountaineering Equipment] , [Fax] , [Quantity sold] , currentMember ( [great_outdoors_company].[Years].[Years] ) )
Result: The completeTuple function uses the currentMember of the Years dimension and the Quantity sold measure.

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Got it.

Many thanks for your help, much appreciated...

Think I have spent too long in SQL world ;)