Last Function in Cognos 10.2 ?

[xplorerdev]

Hi,

I want to create a summary result for a column, Loan_Amount.

Let's say I have 6 months of data for Loan_Amount for year 2011 which is given below:

Month   Loan_Amount
Jan-11   5,000
Feb-11   15,000
Mar-11   25,000
Apr-11   45,000
May-11   55,000
Jun-11   60,000

Now, when I will cut the report on Year or Quarter, I sould get the following result:

Year   Loan_Amount
2011   60,000
   
Quarter   Loan_Amount
Q1 2011   25,000
Q2 2011   60,000


I have achieved the above in OBIEE11g by just applying LAST function to Loan_Amount
in the Admin Tool.

How do I implement this in Cognos 8/10.2 ?

Any suggestions/ideas will be highly appreciated.


Thanks n Regards
Dev

[blom0344]

LAST and FIRST are not available in a relational model , but perhaps you can improvise with seperate quarter / year queries like bringing in month 12 for year level  /  months 3,6,9,12 for quarter level.
Within one query it would come down to coding 3 seperate case statements setting values to zero for non-matching months and totalizing the rest

[xplorerdev]

Hi blom0344,

Thanks for your reply. I was actually able to achieve it in Framework Manager.

I selected the measure. Under Properties > Aggregate Rules: I set Dimension as Time and Aggregate Rule as Last. That's it. It worked.

I am now able to get exactly what I wanted.

**** CLOSING THIS THREAD ****

Thanks n Regards
Dev

[blom0344]

Good for you. Unless otherwise specified I always assume people are working with a relational model. But that's my state of mind  ;)

When we introduced DMR we also tested the use of the aggregate rule of measures against dimensional levels. In our case analysis slowed down to unacceptable levels with large datasets.  My advise would be to thoroughly test this with 'real' productionlike sets of data.

[xplorerdev]

Will keep your advice in mind. :)

Thanks n Regards
Dev