If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Define calculation in a crosstab intersection

Started by bonniehsueh, 28 Jul 2008 11:33:08 PM

Previous topic - Next topic

bonniehsueh

I need to define a calculation in a crosstab intersection. For example, Actual, Budget, % of Budget are the 3 columns with Product Type and Camping Equipment nested underneath.
                             Actual      Budget        % of Budget
Tents                      10             10                1
Sleeping Bag              5              10                .5
Camping Equipment    15             20                1.5 Should be 15/20 = .75

I do not want the intersection of % of Budget and Camping Equipment to aggregate. Instead I want to calculate (Intersection of Actual, Camping Equipment)/(Intersection of Budget, Camping Equipment).

value(tuple([Budget], [Camping equipment]))/ value(tuple([Budget], [Camping equipment]))

Using the calculation above as another column in the crosstab gets the correct value, but when I place the calculation in the intersection, nothing shows. I also set the properties for the calculation as 'Calculated' for the regular aggrgation and 'None' in the Rollup Aggregation.

What am I missing?


praveennb

#1
Set the solve order property for the % of Budget to a value 2

Let us know if this works


bonniehsueh

#2
I figured it out. I set the properties in the row edge to everything except the calculation itself :).

So on the Row edge (Camping Equipment) I already associated Actual and Budget in the Data properties. Once I set % at intersection (the calculation) the calculation appeared.

I also tried changing the sort order to 2 and changing around the aggregate function and rollup properties to check if that would change it and suprisingly not even the solve order made a difference for this situation.

Thanks for the thoughts!