COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: RudiHendrix on 09 Dec 2009 04:38:24 AM

Title: Aggregation order
Post by: RudiHendrix on 09 Dec 2009 04:38:24 AM
In FM I have a calculated measure (Price). This is calculated as invoice_amount / invoice_volume.

Now the data is as follows.
Invoice   Invoice_item    Invoice_Amount       Invoice_Volume         Invoice_Price
10000     A                   1000                     4000                        0,25
10000     B                   1000
10000     C                   50

If I pull up a report displaying this data everything is being calculated correctly. However if I pull up a report displaying only the Invoice data it displays the following:
Invoice   Invoice_Amount       Invoice_Volume         Invoice_Price
10000     2050                     4000                        0,25
Obviously the price should be 0,5125.

How and where can I set the aggregation properties to have this value be calculated correctly?
Title: Re: Aggregation order
Post by: blom0344 on 09 Dec 2009 05:01:30 AM
If you want to perform divisions with aggregates, then you need both components as dataitems:

A and B

Invoice price is than stored as dataitem C  (as A/B ) with aggregation set to 'calculated'

This way the division is performed AFTER aggregation..
Title: Re: Aggregation order
Post by: RudiHendrix on 09 Dec 2009 06:37:02 AM
Perhaps I'm not understanding the solution correctly or I'm not making clear what the issue is.

If I display a report as such:
Invoice   Invoice_item    Invoice_Amount       Invoice_Volume         Invoice_Price
10000     A                   1000                     4000                        0,25
10000     B                   1000
10000     C                   50

It is correct. Because that IS the Invoice_price for that specific Invoice_item for that specific Invoice.

However if I display a report like following:
Invoice   Invoice_Amount       Invoice_Volume         Invoice_Price
10000     2050                     4000                        ?

The price should be 0,5125 and not 0,25. Currently still 0,25 is displayed. But 0,25 is not the price for Invoice "10000".

I've tried setting the regular aggregate to "Calculated", but still I see the same result :(
Title: Re: Aggregation order
Post by: RudiHendrix on 11 Dec 2009 10:26:16 AM
I've found some info here:
http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_fm.8.4.0.doc/ug_fm_id7982usage_aggregation_properties.html

In this example:
For example, to divide debt by credit for each row, the SQL looks like this:

Select
customer, debt, credit, debt/credit as
percent_debt from x
To aggregate for all customers, the SQL looks like this:

Select sum(debt), sum(credit), sum(debt)/sum(credit)
as percent_debt from (Select customer, debt, credit from x)

The latter one is exactly what I want! However... a little bit before that passage in the documentation I can read:
"The calculated aggregation type is not supported for calculations that are embedded within query subjects. "

I can't believe that really  is the case! There must be some way to do these calculations before it ends up with the QS user! Is there no way?

Edit:
I should read a little better:
It also says the following:
The calculated aggregation type is supported only for the following:
-stand-alone calculations
-calculations that are embedded within measure dimensions and are based on measures from the same measure dimension

I think this is exactly what I'm doing here! So, I'm really wondering why it still isn't working!
Title: Re: Aggregation order
Post by: blom0344 on 15 Dec 2009 03:02:21 PM
Do not dispair. I have at least 1 report containing tens of calculations the way you describe them. The trick is to define  A as counter with aggregate as total and B as denominator with aggregate as total and then define C as  A/B with aggregate as calculated.

Obviously, do not fetch Invoice_item to allow for the proper aggregates!!
Title: Re: Aggregation order
Post by: RudiHendrix on 16 Dec 2009 01:57:42 AM
If I create the calculation in QS or RS it does work. But of course I would like to do the calculation in FM and present it as an item that the user can select and drag to the QS working area. If I do that it doesn't work.

I agree, dat I shouldn't fetch Invoice_item. Because then it will definately not aggregate (and why should it? :))

I have made some screenshots that I would like to add. How can I attach files to a thread?
Title: Re: Aggregation order
Post by: blom0344 on 16 Dec 2009 05:53:39 AM
In FM you can enforce it by defining the aggregates in an inline view. That way you are enforcing Cognos to aggregate first and perform the division later. Unfortunately this is not flexible as the aggregate is a static expression. It will also force you to store the definition as an SQL query subject instead of a model query subject.
Title: Re: Aggregation order
Post by: RudiHendrix on 16 Dec 2009 11:47:02 AM
That sounds very interesting!

Could you please elaborate a little bit on this solution?

How can I define the aggregates in an inline view in FM?

As I understand I should create a SQL Query subject. But that way the query subject will be a separate item that is not connected to dimensions.
Because a separate SQL query subject would then be something like:
select (sum(amount)/sum(volume)) as price from t_f_billing

And this:
select id_pk, invoice_dimension_id, (sum(amount)/sum(volume)) as price from t_f_billing
will not work.

So that separate entity is just there...or am I misunderstanding this one again?
Title: Re: Aggregation order
Post by: RudiHendrix on 17 Dec 2009 05:13:52 AM
In an E-mail (as response on the screen shots that I've sent) blom0344 has pointed me in the right direction!

Let me share the final solution with you.

The Query Subject Invoice_price in the end should not be calculated as follows:
[Business view].[Factureringfeiten].[Gefactureerd Bedrag in Euro's]  / [Business view].[Factureringfeiten].[Gefactureerd Volume MJ]

But as follows:
total([Business view].[Factureringfeiten].[Gefactureerd Bedrag in Euro's])   / total([Business view].[Factureringfeiten].[Gefactureerd Volume MJ])

This needs to be done, because in standard methematics divisions are done before aggregations. Still, leave the Regular Aggregate of the Query Subject to "Calculated" and it works out fine!

As always: in the end the solution is fairly simple! :)

Many thanks go out to blom0344!