Create a slicer within a data item in dimensional reporting

[deadsea]

Hello Folks:

I have a dimensional reporting issue that I cant wrap my head around.

In the report screen capture below, I am trying to figure out how to apply filter in my report such that the data item totalCurrent only provides me with measure totals of my set (containing Camping Equipment and Golf Equipment) which are sold in Sales Region set of Americas and Asia-Pacific.



I can do this by adding the slicer, however, I don't want this slicer to be applied to the whole query/report, instead I want it just apply to the data item: totalcurrent

Any ideas or suggestions?

Thanks.

[MFGF]

Hello Folks:

I have a dimensional reporting issue that I cant wrap my head around.

In the report screen capture below, I am trying to figure out how to apply filter in my report such that the data item totalCurrent only provides me with measure totals of my set (containing Camping Equipment and Golf Equipment) which are sold in Sales Region set of Americas and Asia-Pacific.



I can do this by adding the slicer, however, I don't want this slicer to be applied to the whole query/report, instead I want it just apply to the data item: totalcurrent

Any ideas or suggestions?

Thanks.

How about modifying the Totalcurrent item expression to:

total(currentMeasure within set [GoldandCamp], set([Americas],[Asia-Pacific]))

Cheers!

MF.

[deadsea]

Hello MF:

Thanks for your suggestion! After trying it out it looked promising but when I tried to reconcile my numbers by creating a seperate query/Xtab where I specified the filter manually, my measure totals don't match up. Any ideas why this would be?

Here is the screen cap showing the diff in the totals:



I have also attached the report XML (based on Powercube: Great Outdoors Sale.mdc).

Thank you for your help.

[deadsea]

Anyone? Thx.

[CognosPaul]

I can't recreate the Product cost on my own system, so I can't explain what's going on there.

The Unit Cost measure is an average. By using the total() summary, you are forcing Cognos to total the values, instead of recalculating them.


                                 | Product cost   | Unit cost
Camping Equipment | Americas     | 304,058,795.49 |    181.22
                  | Asia Pacific | 266,188,826.89 |    178.12
------------------+--------------+----------------+----------
Golf Equipment    | Americas     | 111,923,740.49 |    379.50
                  | Asia Pacific | 100,151,762.48 |    380.75


If you add the unit cost of each row there you'll get 1,119.59.

Instead, let the cube decide how to aggregate the measure by using the aggregate summary function:
aggregate(currentMeasure within set [GolfandCamp], set([Americas],[Asia-Pacific])