I am using Cognos 10.2.
I have a table that I want to pivot to make it available to report authors.
I have the SQL to do it and could use it to create a View.
But, I am thinking that adding this table as a Query Subject in Framework Manager might be more flexible.
The SQL is listed below. What is the best way to turn this into a Query Subject?
select ticketid,
max( decode(assetattrid,'CRSTITLE',alnvalue) )as Title,
max( decode(assetattrid,'STRTDATE',alnvalue) )as StartDate,
max( decode(assetattrid,'ENDDATE',alnvalue) )as EndDate,
max( decode(assetattrid,'SUPSCORE',numvalue) )as SupScore,
max( decode(assetattrid,'ASSETSRC',alnvalue) )as ASSETSRC,
max( decode(assetattrid,'BKSFILES',alnvalue) )as BKSFILES ,
max( decode(assetattrid,'BOOKNOTE',alnvalue) )as BOOKNOTE,
max( decode(assetattrid,'CLRMDETS',alnvalue) )as CLRMDETS ,
max( decode(assetattrid,'CLSRMCNT',tablevalue) )as CLSRMCNT ,
max( decode(assetattrid,'COMMNOTE',alnvalue) )as COMMNOTE ,
max( decode(assetattrid,'COMMPKGS',alnvalue) )as COMMPKGS ,
max( decode(assetattrid,'CRSCODE',alnvalue) )as CRSCODE,
max( decode(assetattrid,'CSRCLOC',alnvalue) )as CSRCLOC ,
max( decode(assetattrid,'DELEVTMG',tablevalue) )as DELEVTMG ,
max( decode(assetattrid,'DELINFO',alnvalue) )as DELINFO ,
max( decode(assetattrid,'DELSETCT',tablevalue) )as DELSETCT ,
max( decode(assetattrid,'ELMSREQ',numvalue) )as ELMSREQ ,
max( decode(assetattrid,'EVTREQSR',tablevalue) )as EVTREQSR ,
max( decode(assetattrid,'EXTINST',tablevalue) )as EXTINST,
max( decode(assetattrid,'EVTREQTY',alnvalue) )as EVTREQTY ,
max( decode(assetattrid,'IMAGELOC',alnvalue) )as IMAGELOC ,
max( decode(assetattrid,'IMG_OS',alnvalue) )as IMG_OS ,
max( decode(assetattrid,'IMG_SW',alnvalue) )as IMG_SW ,
max( decode(assetattrid,'INTINSTR',alnvalue) )as INTINSTR ,
max( decode(assetattrid,'IRASCPU',numvalue) )as IRASCPU ,
max( decode(assetattrid,'IRASHD',numvalue) )as IRASHD ,
max( decode(assetattrid,'IRASRAM',numvalue) )as IRASRAM ,
max( decode(assetattrid,'SHPMATCT',tablevalue) )as SHPMATCT ,
max( decode(assetattrid,'STUDENTS',alnvalue) )as STUDENTS ,
max( decode(assetattrid,'TIMEZONE',alnvalue) )as TIMEZONE ,
max( decode(assetattrid,'VIVRECID ',numvalue) )as VIVRECID ,
max( decode(assetattrid,'VPNIDS',alnvalue) )as VPNIDS ,
max( decode(assetattrid,'VPNREQ',alnvalue) )as VPNREQ
from maximo.ticketspec where classstructureid='1048' group by ticketid;