I am looking for a bit of help here. I have a nested cross tab with the following setup
Nested Columns underneath (Total Charges, Total Costs)
Measures TotalChargeAmt, TotalCostAmt
The cross tab looks like this:
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
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
This topic has been locked.
5 replies Latest Post - 2013-04-03T17:22:57Z by DanielWagemann
Pinned topic Calculated Percent Change in Nested Crosstab
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-04-03T17:22:57Z at 2013-04-03T17:22:57Z by DanielWagemann
Re: Calculated Percent Change in Nested Crosstab2013-04-03T14:28:18Z in response to DanielWagemannSorry 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?
Re: Calculated Percent Change in Nested Crosstab2013-04-03T17:22:57Z in response to SystemAdminThe 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.