Print Page - Transform the source data (1 dataset to 12 datasets)

Title: Transform the source data (1 dataset to 12 datasets)
Post by: Rico on 26 Sep 2006 09:10:18 AM

Hi out there,

we have a source table which looks like:


store | year | month1 | month2 | month3 | ...
-----------------------------------------------------------
a     | 2005 | 34,56  | 32,23  | 43,30  | ....
...

To be able to dynamically select the month values it should be transformed in the framework manager to something like:

Code Select


store | year | month | value
---------------------------------------------
a     | 2005 |     1 | 34,56
a     | 2005 |     2 | 32,23
a     | 2005 |     3 | 43,30
...

I know how to do it the other way around but have no idea how to make several rows out of one as in this example. Any ideas?

Best regards,
- Rico

Title: Re: Transform the source data (1 dataset to 12 datasets)
Post by: MFGF on 26 Sep 2006 10:21:34 AM

Hi,

Framework Manager is not designed to Transform data so anything of this nature would probably be done in a stored procedure, a view or a sql statement in a data source query subject.

Perhaps you could code a data source query subject using a UNION:
e.g.:

select Store, "Year", '1' as "Month", month1 as "Value" from Table

UNION ALL

select Store, "Year", '2' as "Month", month2 as "Value" from Table

UNION ALL

select Store, "Year", '3' as "Month", month3 as "Value" from Table

etc etc

Regards,

MF.

Title: Re: Transform the source data (1 dataset to 12 datasets)
Post by: Rico on 28 Sep 2006 01:45:23 AM

Thanks very much. That works exactly as I wanted it to be. Blame me for not having that idea myself ;D

Best regards,
- Rico

COGNOiSe.com - The IBM Cognos Community

Legacy Business Intelligence => COGNOS ReportNet => Framework Manager => Topic started by: Rico on 26 Sep 2006 09:10:18 AM