Calculating Columns in Cognos Report Studio with Multiple Ledgers/Year

[SeanCognos]

I'd like to be able to perform calculations between columns of a crosstab report in Report Studio that combine different years and ledger types. For instance, is there a way to create variance columns between the columns in a crosstab that is set up as shown:

Measure:
Year to date amount
Column ACTUAL LEDGER BUDGET LEDGER
2007 2008
Rows:
Revenues
Expenses
etc.


I have no trouble calculating columns when just needing one year or one ledger, but when the setup is as above, I have no luck getting a meaningful variance column. Any suggestions would be appreciated!

 

[almeids]

You can do this with a union (tabular set) which merges the actual/budget tabular model currently in your report with another one that calculates the difference. If volume/performance is an issue there is probably a creative way to do it with tabular references so you only hit the database once. Post again if you need more specifics.
Steve

 

[yphilogene]

Hello,

Your business need clearly shows that you should consider cube or DMR modeling. With such models, you will be able to cross dimensions and perform calculations very easily, with no performance issue.

With a relational model, as soon as calculations between two elements of one column (here years 2007 and 2008) are needed, you're only solution will be UNIONs of queries, which is quite difficult to maintain afterwards.