Incorrect crosstab totals for member percentage calculation

[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 A, Measure B

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.

Measure B is calculation: percentage ( Measure B within set Row A, Row C )

Measure B is correct for individual crosstab rows but incorrect totals for Row A and overall crosstab totals.

If I remove Row C and change calculation to percentage ( Measure B within set Row A ) everything works fine.

If I leave all nested rows and change the calculation to: percentage ( Measure B within set Row A, Row B, Row C ) I get the following error:

OP-ERR-0260

Paramters following the WITHIN SET clause of an aggregate must be either of the same hierarchy or different hierarchy. dataItem="% Gross Revenue" : expression="percentage( [Gross Revenue] within set [Agency],members([Advertiser0@Advertiser$]),[BARTER] )"

Any suggestion on what I'm doing wrong 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 A, Measure B

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.

Measure B is calculation: percentage ( Measure B within set Row A, Row C )

Measure B is correct for individual crosstab rows but incorrect totals for Row A and overall crosstab totals.

If I remove Row C and change calculation to percentage ( Measure B within set Row A ) everything works fine.

If I leave all nested rows and change the calculation to: percentage ( Measure B within set Row A, Row B, Row C ) I get the following error:

OP-ERR-0260

Paramters following the WITHIN SET clause of an aggregate must be either of the same hierarchy or different hierarchy. dataItem="% Gross Revenue" : expression="percentage( [Gross Revenue] within set [Agency],members([Advertiser0@Advertiser$]),[BARTER] )"

Any suggestion on what I'm doing wrong appreciated.

Cheers,

Ricky (COYS)

Hi To... er, I mean Ricky!

I'm assuming that the "rows" are levels from your cube? If so, your life is easy. You don't need to add totals per se. The "Total" for Row B within a Row A parent is the Row A level member, if you think about it. Just stack another instance of Row A below Row B (nested within Row A) for the Row A totals. For the overall total, stack the top level from the Row A/Row B hierarchy below the outer Row A in your crosstab.

I just mocked this up using the great_outdoors_sales_en sample powercube. Here's the stacking / nesting I used:



And here's the end of the report showing the totals:



The Perc measure is defined as percentage( [Quantity sold] within set [Product line],[Year])

Cheers!

MF.

[Lynn]

Or....do you even need the "within set" qualifier for the calculated measure? Try using "percentage ( [Measure A] )" for the Measure B expression and then set the solve order to something higher than 1.

[Ricardo Julio "Ricky" Villa]

Thanks guys.

I'm almost there with Mike's solution.

The sub total at row A level is now fixed, but not the report overall total.

I'm guessing that Products is the root member? (I don't have the great outdoors cube to hand)

My definition for row A is actually this:
filter ( [Saxon Sales Performance Cube].[All Agencies].[All Agencies].[Agency], roleValue ( '_memberDescription', [Saxon Sales Performance Cube].[All Agencies].[All Agencies].[Agency] ) = 'Y' )

Do you have a suggestion how I can get the summary member for this and add it to the crosstab?

@Lynn - unfortunately the report hangs and does not return data when I try that expression.

Cheers,

Ricky (COYS)

[MFGF]

Thanks guys.

I'm almost there with Mike's solution.

The sub total at row A level is now fixed, but not the report overall total.

I'm guessing that Products is the root member? (I don't have the great outdoors cube to hand)

My definition for row A is actually this:
filter ( [Saxon Sales Performance Cube].[All Agencies].[All Agencies].[Agency], roleValue ( '_memberDescription', [Saxon Sales Performance Cube].[All Agencies].[All Agencies].[Agency] ) = 'Y' )

Do you have a suggestion how I can get the summary member for this and add it to the crosstab?

@Lynn - unfortunately the report hangs and does not return data when I try that expression.

Cheers,

Ricky (COYS)

Mike? Ha ha ha :) Perhaps I should call you Tony? ;)

Ok - so the highest level nested rows are a filter expression returning a subset of level members, not the entire level? In that case, your grand total row could perhaps be

aggregate( currentMeasure within set [your set of Row A members delivered by the expression you posted])

You'd then need to set the solve order on your Percent column calculation to be 2 - so it was performed after the calculation above.

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Sorry Mark, too much multi-tasking ;)

Yes that actually works, cheers!