Cannot sum the metrics after rounding

[sthabinash]

I have created a measure in Framework manager with expression round(sum(x),2).

When I run the report in the Report Studio, I get the error "Data source adapter error: ERROR: Aggregate function calls may not be nested".

What am I doing wrong?  :'(

[blom0344]

It is not best practice to code aggregates (takes some getting used to if you are accustomed to other tools) in definitions.  The aggregate is applied dynamically through the properties of the dataitem. This way you can override the standard setting from the front-end.
Within the report it is fairly easy to use a certain format to show any precision for a given measure; you do not need to code anything.  Pretty sophisticated don't you think?   ;)

[sthabinash]

I know that defining data formats for the metrics at the report studio is a good practice. But there are cases in the report I am currently working on where I have to use the same metrics at different pages and at different lists and crosstabs. So, if the requirement changes I have to go to different places and makes changes to same metrics which makes it a tedious task. But if I am able to do it through the Framework manager, it makes it a whole lot easy for me.

And I guess you are right about the aggregates. But out of curiosity, if I have to apply the sum within the metrics itself in framework manager, is it possible.

[blom0344]

You set the format within the model - as well -  WITHOUT having to resort to applying functions. Setting hardcoded aggregates is not the way to go

[sthabinash]

I get what you are saying. But, the problem is if i round up first in the framework manager, the output is like sum(round(x)), which gives a distorted sum as well due to the fact the rounding is done first and it makes a huge difference in the final result (the data we are considering are in the range of millions of data).

But if I follow the method of round(sum(x)), the result are not much distorted and are very much similar to the results we are looking for. This is achieved through the data formatting in the Report Studio, but as i have mentioned in my previous post, I have to do this at different places in the same report. So, I am trying to find a solution through the Framework manager itself (if possible).

[blom0344]

No, rounding is not the answer. Applying a certain format means that internally the highest precision is still maintained for calculations. And I never suggested rounding before aggregating. You are stuck in the wrong track , possibly due previous experience with other BI tools.
Once again, formatting can be done at a global level (model), will therefore be reusable and  it will not affect internal precision, therefore not causing inaccuracy in results.
I rest my case  ;D

[Lynn]

I agree with blom. Set the aggregate property to total and the desired default format. You shouldn't need to round at all. Just let Cognos aggregate the data at whatever precision is available from the source. It will display according to whatever data format you specify in the model. You won't need to change any formatting in Report Studio unless you want something different from what the default is.

[MFGF]

I agree with Lynn who agrees with Blom. Oh... I think you get the idea by now. :)

MF.

[sthabinash]

Thanks for the info. It helped a lot. :)