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

[RS]aggregate(currentMeasure within detail [CATEGORY]) means what?

Started by dxwallace, 12 Jul 2007 11:30:48 AM

Previous topic - Next topic

dxwallace

I am trying to find out why a report works when I use the
aggregate(currentMeasure within detail [CATEGORY]) statement.

The original problem was that I had a calculation that I was totaling, but when I attempted to move that total of the calculation to another cell of the totals line I got erroneous results. For example in my list report:

        22  <---result of ((a-b)/c) * 100)
        21          "               "
        14          "               "

        57  <---total of above calculations,
but when I use the above field as part of a
calculation in another cell in totals line, the field value is --------->    61


I know this is some kind of rollup issue, but I screwed around with Rollup aggregation and Aggregate it still comes up this way.

Continuing on, I couldn't figure this out until I noticed a data item the first query, from another programmer which was

aggregate(currentMeasure within detail [CATEGORY])  ,

CATEGORY is one of the data items from a table in my query, along with CHANNELS and GRADE.

However, I am using three separate queries for three sections of the report, each section does not have the other two columns within it on the report,  meaning that user will see in the three report sections of the report:

Page1
    CATEGORY -  CALC from above , 

Page2
    CHANNELS - CALC  from above, 

Page 3
    GRADE       -  CALC from above


So I put the statement

aggregate(currentMeasure within detail [CATEGORY])

in the second and third queries, now they all show the following CORRECT results:

        22  <---result of ((a-b)/c) * 100)
        21          "               "
        14          "               "

        57  <---total of above calculations,
I can now use this value as part of a calc in another cell on totals line
since the total  is now --------->    57


The thing is that although CATEGORY is a data item from a table in queries 2 and 3, those pages don't display CATEGORY as a column, so I can't see why the statement makes them work also.

I am trying to learn why this statement works because If I hadn't noticed it in the long list of fields from the first query from another programmer, I don't think I would have figured this out. The data is from SQL server relational data. I thought if I just grouped on columns, I would get the correct numbers, but that didn't work either.  I', trying to get better at learning how Cognos is dealing with aggregation, Grouping an such, this I really wanna know.

Let me know if more detail is needed for this issue or if anyone has had a similar problem.

I would also welcome any other ways to accomplish the task.