Transpose Columns to Rows for easy Line Charting?

[Eric.Seitz]

Howdy!

I have a bit of a head-scratcher. I want to make a line chart for our quarterly data but it's currently laid out something like this:

Project      Category      Current      Q4      Q3
A              Building         $100        $80     $70
B              Const.           $300        $10     $50
C              Renovate       $400        $20     $30


I want it lay it out like this so I can make a line chart on DATE and BALANCE
Project      Category      Date      Balance
A              Building       Current    $100
A              Building       Q4           $80
A              Building       Q3           $70
B              Const.         Current    $300
B              Const.         Q4           $10
B              Const.         Q3           $50
C              Renovate     Current    $400
C              Renovate     Q4           $20
C              Renovate     Q3           $30

I can't figure out how to do it in Cognos, either through calculated columns or what. Any ideas? Thanks!

[MFGF]

Howdy!

I have a bit of a head-scratcher. I want to make a line chart for our quarterly data but it's currently laid out something like this:

Project      Category      Current      Q4      Q3
A              Building         $100        $80     $70
B              Const.           $300        $10     $50
C              Renovate       $400        $20     $30


I want it lay it out like this so I can make a line chart on DATE and BALANCE
Project      Category      Date      Balance
A              Building       Current    $100
A              Building       Q4           $80
A              Building       Q3           $70
B              Const.         Current    $300
B              Const.         Q4           $10
B              Const.         Q3           $50
C              Renovate     Current    $400
C              Renovate     Q4           $20
C              Renovate     Q3           $30

I can't figure out how to do it in Cognos, either through calculated columns or what. Any ideas? Thanks!

Hi,

This is an example of needing to pivot (transpose) the data from columns to rows. You can easily go the opposite way in Cognos (using a crosstab to pivot from rows to columns), but there is nothing in the Cognos toolset that lends itself to what you are looking for here. Cognos is designed to work with (mostly) normalized data, ie data that is already in the form you are looking to achieve here. Years ago, Cognos (and then IBM) had a product called Cognos Data Manager, and this would easily pivot data, but it was deprecated many years ago now. Effectively what you're looking to do here is to normalize the data (convert it from an array to a list), and that's usually done in an ETL tool or a data blending tool. If it was me, I'd look at something outside of Cognos to get the data into the form you need - Alteryx Designer or something similar.

Sorry - probably not the answer you were hoping for.

Cheers!

MF.

[MFGF]

Howdy!

I have a bit of a head-scratcher. I want to make a line chart for our quarterly data but it's currently laid out something like this:

Project      Category      Current      Q4      Q3
A              Building         $100        $80     $70
B              Const.           $300        $10     $50
C              Renovate       $400        $20     $30


I want it lay it out like this so I can make a line chart on DATE and BALANCE
Project      Category      Date      Balance
A              Building       Current    $100
A              Building       Q4           $80
A              Building       Q3           $70
B              Const.         Current    $300
B              Const.         Q4           $10
B              Const.         Q3           $50
C              Renovate     Current    $400
C              Renovate     Q4           $20
C              Renovate     Q3           $30

I can't figure out how to do it in Cognos, either through calculated columns or what. Any ideas? Thanks!

Following on from my previous post, if you really, really must do this in your report, you can pull together a messy solution that delivers the pivoted data.

The first big challenge is multiplying up the number of rows. You are starting with a single row per project/category, and you need to end up with 3 rows per project/category. You can code a SQL query to do this - drag an SQL object into the Query Explorer, and code a query like this:

select * from (
values (
cast('Current' as varchar(20)), cast('a' as varchar(1))),
('Q4', 'a'),
('Q3', 'a')
)      
PivotColumns ("Date", "Joiner")

This gives you a three-row result, with each row corresponding to each of the "dates" you require (Current, Q4 and Q3), plus an extra column containing a literal 'a'

Next, pull your original data into a second query, and add a Query Calculation, also containing a literal 'a' (this is what the two queries will join on)

Now bring in a Join, and join together these two queries based on the literal value - I called it Joiner in each.

In the query this join feeds into, add a query calculation that delivers the correct Balance:

case [Query3].[Date]
when 'Current' then [Query4].[Current]
when 'Q4' then [Query4].[Q4]
when 'Q3' then [Query4].[Q3]
else null
end

It's horrible. It's messy. However, you end up with the result you are looking for.

Cheers!

MF.

[dougp]

To handle this pre-pivoted data, I think you'll want three source queries UNIONed to unpivot the data, then use that as the query for the chart.  It's difficult to write text to represent how to do this in Cognos.  Here is SQL code (using SQL Server syntax) that may help explain:

Code Select Expand


WITH Project as (
SELECT [Project]
, [Category]
, 'Current' as 'Date'
, [Current] as 'Balance'
FROM [Table]
),
Q4 as (
SELECT [Project]
, [Category]
, 'Q4' as 'Date'
, [Q4] as 'Balance'
FROM [Table]
),
Q3 as (
SELECT [Project]
, [Category]
, 'Q3' as 'Date'
, [Q3] as 'Balance'
FROM [Table]
)

SELECT Project
UNION SELECT Q4
UNION SELECT Q3


If Q4 and Q3 are dynamic (the previous 2 quarters) you should be able to parameterize that with a prompt macro and automate it using value prompts fed from queries.

If you have a variable number of "Q" columns, you may be able to handle that by using filters on the source queries.

Shortcut:  If this is coming from a SQL database, you should be able to use the UNPIVOT command in raw SQL.