If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

how to conver this access query to the report studio cognos 8 report!!!

Started by katdbc, 29 Sep 2010 05:10:37 PM

Previous topic - Next topic

katdbc

TRANSFORM Count(MORIS_CONTRACT_MONTHLY.ACCOUNT_NUM) AS [The Value]
SELECT MORIS_CONTRACT_MONTHLY.REMAINING_TERM, Count(MORIS_CONTRACT_MONTHLY.ACCOUNT_NUM) AS [Total Of ACCOUNT_NUM]
FROM MORIS_CONTRACT_MONTHLY INNER JOIN Program ON MORIS_CONTRACT_MONTHLY.FINANCE_PGM_CD = Program.PROGRAMCD
WHERE (((MORIS_CONTRACT_MONTHLY.ACTIVE_CONTRACT_FLG)="Y") AND ((MORIS_CONTRACT_MONTHLY.CONTRACT_DIV_CD)="FASTRUCK"))
GROUP BY MORIS_CONTRACT_MONTHLY.REMAINING_TERM
PIVOT Mid([Program]![Program],1,3);


that is the cross tab query!!!!  can anyone suggest on how to go about it!!!

nmcdermaid

Do you want to do this properly (use framework manager etc.) or do you just want to try and convert this to pass through SQL?

Will your datasource be MS Access?

An important thing to note is that in Cognos, you would perform the crosstab in the presentation part. Your query would return a standard table with Mid([Program]![Program],1,3) as a SINGLE column with multiple values.

Then you would drop the query on to a crosstab object in query studio.

katdbc