Topic
• 5 replies
• Latest Post - ‏2013-04-03T17:22:57Z by DanielWagemann
15496 Posts

# Pinned topic Calculated Percent Change in Nested Crosstab

‏2013-04-02T14:34:27Z |
Cognos gurus-

I am looking for a bit of help here. I have a nested cross tab with the following setup

Rows:Region
Columns: Year
Nested Columns underneath (Total Charges, Total Costs)
Measures TotalChargeAmt, TotalCostAmt

The cross tab looks like this:

2011 2012
Charges Costs Charges Costs
Midwest \$400 | \$200 || \$500 | \$100
South \$700 | \$300 || \$600 | \$100
North \$800 | \$300 || \$500 | \$100

Ok, what I am trying to do is get the percent change from 2011 to 2011 and I am trying to use the right function but does not seem to work as it is ignoring the 2011 and 2012. This is relational DB I do not believe this is cube backend.
So I want the table to do the follwoing calculation
(2012 Charges - 2011 Charges)/(2012 charges) = %changeincharges
(2012 Costs- 2011 Costs)/(2012 Costs) = %changeincosts

I'd like this as a calculated column if possible.

Final result would be

2011 2012
Charges Costs Charges Costs %ChangeCharge %ChangeCOsts
Midwest \$400 | \$200 || \$500 | \$100 20% -100%
South \$700 | \$300 || \$600 | \$100 -16% -200%
North \$800 | \$300 || \$500 | \$100 -60% -200%
I hope this is clear..is this possible to even do? I also don't mind where the columns go they can go anywhere they do not have to be at the end. I can also reorder it as well if it is easier

Sorry the table may have not come out well..there is an attachement

#### Attachments

Updated on 2013-04-03T17:22:57Z at 2013-04-03T17:22:57Z by DanielWagemann
• DanielWagemann
411 Posts

#### Re: Calculated Percent Change in Nested Crosstab

‏2013-04-03T14:18:30Z
Can you confirm what type of package this is please. It is important as the approaches will be different.
15496 Posts

#### Re: Calculated Percent Change in Nested Crosstab

‏2013-04-03T14:28:18Z
Can you confirm what type of package this is please. It is important as the approaches will be different.
Sorry I am new to Cognos. I come from the Microsoft Stack (SSRS/AS/IS/MS).

I believe you are asking me what is the datasource? I am using a reporting package (blue folder) via report studio. I am assuming it is a relational connection via cognos's framework. The package contains four namespaces and I am workinbg withing just one. It already has metrics and dimensions pre-built for the user to click and drag. I unfortunately cannot confirm if this is a cube or relational source 100%. My suspicsiouns is it is data mart that is reltional

Does this help?
• DanielWagemann
411 Posts

#### Re: Calculated Percent Change in Nested Crosstab

‏2013-04-03T15:58:45Z
Sorry I am new to Cognos. I come from the Microsoft Stack (SSRS/AS/IS/MS).

I believe you are asking me what is the datasource? I am using a reporting package (blue folder) via report studio. I am assuming it is a relational connection via cognos's framework. The package contains four namespaces and I am workinbg withing just one. It already has metrics and dimensions pre-built for the user to click and drag. I unfortunately cannot confirm if this is a cube or relational source 100%. My suspicsiouns is it is data mart that is reltional

Does this help?
In Report Studio, within the left hand metadata tree, do you see query subjects and query items... or do you see hierachies, dimensions and levels?
15496 Posts

#### Re: Calculated Percent Change in Nested Crosstab

‏2013-04-03T16:19:27Z
In Report Studio, within the left hand metadata tree, do you see query subjects and query items... or do you see hierachies, dimensions and levels?
I see query subjects and query items.
• DanielWagemann
411 Posts

#### Re: Calculated Percent Change in Nested Crosstab

‏2013-04-03T17:22:57Z
I see query subjects and query items.
The type of reporting you are trying to do is better suited to a DMR/OLAP package. You can get what you need over relational but it is going to take some work to get the buckets to compare.

Attached is a 10.2 example against the Go Sales(query) relational package. It uses an if then else approach to bucket the Revenue per Year. It then uses the zero suppression on the columns to wipe out the zero columns.