crosstab - Calculation of fact cells in cognos

Question

enter image description hereCrosstab design

Good day I have the following scenario I am trying to achieve, I am trying to calculate total revenue which is a crosstab space (with fact cell), I am trying to add all the nodes highlighted in blue, from there do a calculation for the nodes highlighted in red. How may I achieve this?

question from:https://stackoverflow.com/questions/65917867/calculation-of-fact-cells-in-cognos

Answer 1

I am uncertain what you have there. At first glance I thought it was simply two crosstabs. But there's nothing simple about it. It looks like the Revenue crosstab has 10 measures in it and the Expenditures crosstab has 5 measures.

Now that I have clarified your question (and assuming my clarification is correct)...

To continue using that structure, you'll need to add up all of the measures you want into an additional measure and add it to the bottom of the crosstab. The data item expression may look like this:

[Property Rates]
 + [Service Charges - Electricity Revenue]
 + [Service Charges - Water Revenue]
 + [Service Charges - Sanitation Revenue]
 + [Service Charges - Refuse Revenue]
 + [Fines, Penalties and Forfeits]
 + [License and Permits]
 + [Agency Services]
 + [Transfers and Subsidies]
 + [Other Revenues]

Then you drag that below the other Revenue lines. Do something similar with Expenditures.

Typically, you would have one measure with one or more dimensions as row labels and one or more dimensions as column labels. That would be easy to sum using the Total tool on the Summarize menu.

So another way to do this would be to separate each measure into one query with a new data item (Revenue Category) defined with a string literal. For example, the first query would have a data item defined as [Property Rates]. Then you would union the queries together and use the result to create an actual crosstab that Cognos can handle automatically for you. Of course, if you are working with a star schema you won't need to do all this work because Revenue Category is already a dimension in the database.