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

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
Updated on 2013-04-03T17:22:57Z at 2013-04-03T17:22:57Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: Calculated Percent Change in Nested Crosstab

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

      Re: Calculated Percent Change in Nested Crosstab

      ‏2013-04-03T14:28:18Z  in response to DanielWagemann
      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
        DanielWagemann
        411 Posts
        ACCEPTED ANSWER

        Re: Calculated Percent Change in Nested Crosstab

        ‏2013-04-03T15:58:45Z  in response to SystemAdmin
        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?
        • SystemAdmin
          SystemAdmin
          15496 Posts
          ACCEPTED ANSWER

          Re: Calculated Percent Change in Nested Crosstab

          ‏2013-04-03T16:19:27Z  in response to DanielWagemann
          I see query subjects and query items.
          • DanielWagemann
            DanielWagemann
            411 Posts
            ACCEPTED ANSWER

            Re: Calculated Percent Change in Nested Crosstab

            ‏2013-04-03T17:22:57Z  in response to SystemAdmin
            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.