Hi All,
Others may find this helpful. I had a requirement to include some fields and summary values in a report that seemed to work best in a pivot table. But rather than a crosstab report it would need to be pivot sql for a few reasons. I couldn't see any suggestions on here other than it can be done and may have to use a pass-through sql query. So I have done a bit of a test using the GOSALESEDW sql server database and it's package in 10.2.2.
If you run this sql in your cognos report it will give you a feel for the data.
select [POSITION_CODE3],sum([MAX_SALARY]) as MaxSal
from [GOSALESDW].[gosalesdw].[EMP_POSITION_DIM]
group by position_code3
Now to change this to a Pivot of some of the values in the Position_Code3 field I constructed this SQL. I didn't need to change from Native to Pass-Through but other versions or setups may require this change.
Select 'mysal' as my_sal_calc, [300],[320],[330],[340]
FROM (
Select [POSITION_CODE3],[MAX_SALARY] from [GOSALESDW].[gosalesdw].[EMP_POSITION_DIM]) as srctbl
PIVOT ( MAX([MAX_SALARY]) for [POSITION_CODE3] in ([300],[320],[330],[340])) as pvt
When you run this in Cognos the data from the top piece of sql is pivoted
my_sal_calc 300 320 330 340
mysal 180,000 180,000 180,000 180,000
A very simple example I know that obviously has flaws if the data changes in the Code3 field, certainly can be built on but thought this may be useful for others.
Binway
Thanks for sharing that Binway...might come in handy at some point