COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: sc_solucient on 08 Feb 2018 02:37:11 PM

Title: Aggregating Conditional Statements in Report Studio Crosstab
Post by: sc_solucient on 08 Feb 2018 02:37:11 PM
I have a conditional statement in a crosstab that's over riding the budget numbers with %s or $s.   The statement is working fine, but when I try to aggregate the crosstab, it treats the Total (aggregate), as the default.   For example, using only 1 parameter, Let's say the budget is $100.  I override that budget by adding $50.  The default (else portion of the condition), is $100*1.25%.   

The report should show:

Param1   $150
Total       $150

The results I'm getting are:

Param1    $150
Total        $125

How do I get this to aggregate correctly?   I've changed the solve order, and it still is incorrect.
Title: Re: Aggregating Conditional Statements in Report Studio Crosstab
Post by: AnalyticsWithJay on 16 Feb 2018 07:04:06 AM
Try setting the aggregate and rollup aggregate to 'Calculated'. If it doesn't solve it, please post the expressions for Param1 and Total.
Title: Re: Aggregating Conditional Statements in Report Studio Crosstab
Post by: sc_solucient on 17 Feb 2018 06:13:48 PM
Thank you for responding.   Setting it to 'calculated' in aggregate and rollup aggregate did not change the results.

Let me explain the process.

The user enters in a Budget Variable in the prompt page to adjust the previous year's budget.   For example, the user wants to increase the budget by 25%.  He would enter 25.   This overall budget is broken out by up to 90 detail lines,
each associated with a code.  The user is then taken to another prompt page to have the option to override the previously enter budget of 50% at a line code level.   This can be a percentage (?p_1A?), or actual dollars (?p_1A$?).   
The actual dollars are expressed as a percentage in adding to the budget.   All 90 parameters are defaulted to zero.  If nothing is entered into the parameter the default of 25% increase in budget for that line code
is used ([Budget Proj All]).   The override works at the line code level, so last year's budget is $100, and I increase it by 50%, it correctly shows $150 at that line code.  In this example, using only 1 line code, the aggregate
would be $125, ignoring the override at the line code level, and using the default of 25% increase of $100.  The total should be $150.
 
The code used is as follows:

Conditional Statement or Param1:

if (caption(currentMember([Advantage].[CMS64 Line Category Code].[CMS64 Line Category Code])) contains '1A' and ((?p_1A?/1)<>0))
then
([Total Cost of Coverage CMS64 Budget]*(1+(?p_1A?/100)))
else
if (caption(currentMember([Advantage].[CMS64 Line Category Code].[CMS64 Line Category Code])) contains '1A' and ((?p_1A$?/1)<>0))
then
([Total Cost of Coverage CMS64 Budget]*(1+(?p_1A$?/[Total Cost of Coverage CMS64 Budget])))
else
([Budget Proj All])


Total:

aggregate(currentMeasure within set [Advantage].[CMS64 Line Category].[CMS64 Line Category].[(All)]->:[XO].[CMS64 Line Category].[All CMS64 Line Categorys])

Thanks again.
Title: Re: Aggregating Conditional Statements in Report Studio Crosstab
Post by: AnalyticsWithJay on 19 Feb 2018 08:11:32 AM
I shouldn't have assumed it was relational. Could you try setting a Solve Order on the metrics in the query? Ensure the 'Total' metric has the highest solve order.

Ex: Set 'Solve Order' on the measure to 100, and set the Total solve order to 1000. This will fix it if the issue is related to the order of computations.

Title: Re: Aggregating Conditional Statements in Report Studio Crosstab
Post by: sc_solucient on 19 Feb 2018 10:10:07 AM
That did not work.  I had tried previously using a solve order of 1 and 2, respectively.   It appears that the Total line is displaying the default of the conditional statement, instead of aggregating the column.