Cognos Audit report that shows reports that are currently running from Report,Query,Powerplay studios

As you might have noticed that on the Cognos audit package the report run details are saved on different tables depending on the studio and there is no simple way to see all the report run details at one place i created this report in Report Studio(8.4) that Unions and gives you report run details from studios and total number of reports run in past 24hrs,reports run by the report executor,number of people running reports. The naming convension on the Data items are different than the default audit package. You can also implement this as a one of solution in the model.

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-us">
 <modelPath>/content/package[@name='BI MetaData']/model[@name='model']</modelPath>
 <drillBehavior modelBasedDrillThru="true"/>
 <queries>
  <query name="Adhoc Reports">
   <source>
    <model/>
   </source>
   <selection><dataItem name="UID" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[UserID]</expression></dataItem><dataItem name="Run Time Stamp" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[Adhoc Report Run Time Stamp]</expression></dataItem><dataItem name="Report Run Server" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[Adhoc Report Run Server]</expression></dataItem><dataItem name="Report Name" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[Adhoc Report Run Name]</expression></dataItem><dataItem name="Report Target type" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[Adhoc Report Run Target type]</expression></dataItem><dataItem name="Report Run Status" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[Adhoc Report Run Status]</expression></dataItem><dataItem name="Report Error Details" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Adhoc Run Reports].[Adhoc Report Run Error Details]</expression></dataItem></selection>
   <detailFilters><detailFilter><filterExpression>[Run Time Stamp] between {sysdate}-1day and {sysdate}</filterExpression></detailFilter><detailFilter><filterExpression>[Run Time Stamp] is not missing</filterExpression></detailFilter></detailFilters></query>
  <query name="RS Reports">
   <source>
    <model/>
   </source>
   <selection><dataItem name="UID" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[User name]</expression></dataItem><dataItem name="Run Time Stamp" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[Run Report Time Stamp]</expression></dataItem><dataItem name="Report Run Server" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[Run Report Server Name]</expression></dataItem><dataItem name="Report Name" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[Run Report Name]</expression></dataItem><dataItem name="Report Target type" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[Run Report Type]</expression></dataItem><dataItem name="Report Run Status" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[Run Report Status]</expression></dataItem><dataItem name="Report Error Details" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[Run Reports].[Run Report Error Details]</expression></dataItem></selection>
   <detailFilters><detailFilter><filterExpression>[Run Time Stamp] between {sysdate}-1day and {sysdate}</filterExpression></detailFilter><detailFilter><filterExpression>[Run Time Stamp] is not missing</filterExpression></detailFilter></detailFilters></query><query name="PP Reports">
   <source>
    <model/>
   </source>
   <selection><dataItem name="UID" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[PowerPlay Summary].[User Name]</expression></dataItem><dataItem name="Run Time Stamp" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[PowerPlay Summary].[Request Time Stamp]</expression></dataItem><dataItem name="Report Run Server" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[PowerPlay Summary].[PP Request Server Name]</expression></dataItem><dataItem name="Report Name" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[PowerPlay Summary].[Powerplay Report Name]</expression></dataItem><dataItem name="Report Target type" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[PowerPlay Summary].[Powerplay Report Type]</expression></dataItem><dataItem name="Report Run Status" aggregate="none" rollupAggregate="none"><expression>[Audit Presentation].[PowerPlay Summary].[Powerplay Report Status]</expression></dataItem><dataItem name="Report Error Details"><expression>' '</expression></dataItem></selection>
   <detailFilters><detailFilter><filterExpression>[Run Time Stamp] between {sysdate}-1day and {sysdate}</filterExpression></detailFilter><detailFilter><filterExpression>[Run Time Stamp] is not missing</filterExpression></detailFilter></detailFilters></query><query name="QS-AS-RS">
   <source>
    <queryOperation name="Union1" setOperation="UNION">
     <queryRefs><queryRef refQuery="Adhoc Reports"/><queryRef refQuery="RS Reports"/></queryRefs>
     <projectionList autoGenerated="true"><queryItem name="UID"/><queryItem name="Run Time Stamp"/><queryItem name="Report Run Server"/><queryItem name="Report Name"/><queryItem name="Report Target type"/><queryItem name="Report Run Status"/><queryItem name="Report Error Details"/></projectionList>
    </queryOperation></source>
   <selection><dataItem name="UID"><expression>[Union1].[UID]</expression></dataItem><dataItem name="Run Time Stamp"><expression>[Union1].[Run Time Stamp]</expression></dataItem><dataItem name="Report Run Server"><expression>[Union1].[Report Run Server]</expression></dataItem><dataItem name="Report Name"><expression>[Union1].[Report Name]</expression></dataItem><dataItem name="Report Target type"><expression>[Union1].[Report Target type]</expression></dataItem><dataItem name="Report Run Status"><expression>[Union1].[Report Run Status]</expression></dataItem><dataItem name="Report Error Details"><expression>[Union1].[Report Error Details]</expression></dataItem></selection>
  </query><query name="QS-AS-RS-PS">
   <source>
    <queryOperation name="Union2" setOperation="UNION">
     <queryRefs><queryRef refQuery="QS-AS-RS"/><queryRef refQuery="PP Reports"/></queryRefs>
     <projectionList autoGenerated="true"><queryItem name="UID"/><queryItem name="Run Time Stamp"/><queryItem name="Report Run Server"/><queryItem name="Report Name"/><queryItem name="Report Target type"/><queryItem name="Report Run Status"/><queryItem name="Report Error Details"/></projectionList>
    </queryOperation></source>
   <selection><dataItem name="UID"><expression>[Union2].[UID]</expression></dataItem><dataItem name="Run Time Stamp"><expression>[Union2].[Run Time Stamp]</expression></dataItem><dataItem name="Report Run Server"><expression>[Union2].[Report Run Server]</expression></dataItem><dataItem name="Report Name"><expression>[Union2].[Report Name]</expression></dataItem><dataItem name="Report Target type"><expression>[Union2].[Report Target type]</expression></dataItem><dataItem name="Report Run Status"><expression>UPPER([Union2].[Report Run Status])</expression></dataItem><dataItem name="Report Error Details"><expression>[Union2].[Report Error Details]</expression></dataItem><dataItem name="Report Count" aggregate="count"><expression>[Union2].[Report Name]</expression></dataItem><dataItem name="UID Count" aggregate="countDistinct"><expression>[Union2].[UID]</expression></dataItem><dataItem name="My UID" aggregate="total"><expression>IF(upper([Union2].[UID])=upper ( #sq($account.personalInfo.userName)# ))
THEN
(1)
ELSE
(0)</expression></dataItem></selection>
  </query></queries>
 <layouts>
  <layout>
   <reportPages>
    <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
     <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
      <contents>
       <table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Total # of Reports Executed in Past 24 HRS :  </staticValue></dataSource><style><CSS value="text-decoration:none;font-style:italic;font-weight:bold"/></style></textItem><singleton name="Singleton1" refQuery="QS-AS-RS-PS">
                          <contents><textItem><dataSource><dataItemValue refDataItem="Report Count"/></dataSource><style><CSS value="font-weight:bold;color:blue"/></style></textItem></contents>
                         </singleton></contents><style><CSS value="text-align:left"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Total # of Users Executing Reports in Past 24 HRS :  </staticValue></dataSource><style><CSS value="text-decoration:none;font-style:italic;font-weight:bold"/></style></textItem><singleton name="Singleton2" refQuery="QS-AS-RS-PS">
                          <contents><textItem><dataSource><dataItemValue refDataItem="UID Count"/></dataSource><style><CSS value="font-weight:bold;color:blue"/></style></textItem></contents>
                         </singleton><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Total # of Reports executed by you in past 24 HRS :   </staticValue></dataSource><style><CSS value="text-decoration:none;font-style:italic;font-weight:bold"/></style></textItem><singleton name="Singleton3" refQuery="QS-AS-RS-PS">
                                <contents><textItem><dataSource><dataItemValue refDataItem="My UID"/></dataSource><style><CSS value="font-weight:bold;color:blue"/></style></textItem></contents>
                               </singleton></contents></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="text-align:left"/></style></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="text-align:left"/></style></tableCell><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue/></dataSource></textItem></contents><style><CSS value="width:0.2in;height:0.2in;background-color:green"/></style></tableCell><tableCell><contents><textItem><dataSource><staticValue>Report Currently Running</staticValue></dataSource></textItem></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue/></dataSource></textItem></contents><style><CSS value="width:0.2in;height:0.2in;background-color:red"/></style></tableCell><tableCell><contents><textItem><dataSource><staticValue>Report Failed</staticValue></dataSource></textItem></contents></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="text-align:left"/></style></tableCell></tableCells></tableRow></tableRows></table></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><list horizontalPagination="true" name="List1" refQuery="QS-AS-RS-PS">
              <style>
               <defaultStyles>
                <defaultStyle refStyle="ls"/>
               </defaultStyles>
               <CSS value="border-collapse:collapse"/>
              </style>
              <listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="UID"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:center"/></style><contents><textItem><dataSource><dataItemValue refDataItem="UID"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Run Time Stamp"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:center"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Run Time Stamp"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Report Run Server"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:center"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Report Run Server"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Report Name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:left"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Report Name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Report Target type"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:center"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Report Target type"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Report Run Status"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:center"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Report Run Status"/></dataSource></textItem></contents><conditionalStyleRefs><conditionalStyleRef refConditionalStyle="Conditional Style 1"/></conditionalStyleRefs></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-weight:bold;color:white;background-color:navy"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Report Error Details"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:center"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Report Error Details"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><sortList><sortItem refDataItem="Run Time Stamp" sortOrder="descending"/></sortList></list></contents><style><CSS value="padding-top:10px;padding-bottom:10px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents>
     </pageBody>
     <pageHeader>
      <contents>
       <block>
        <contents>
         <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
          <contents>
           <table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><block>
                  <contents><image>
                    <dataSource>
                     <staticValue>../samples/images/fple-logo.jpg</staticValue>
                    </dataSource>
                    <style><CSS value="width:125px"/></style></image></contents>
                 </block></contents><style><CSS value="width:50px"/></style></tableCell><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><block>
                        <contents><textItem><dataSource><staticValue>Live Report Run Logs (AS/QS/PS/RS)</staticValue></dataSource></textItem></contents>
                        <style><CSS value="height:50px;font-weight:bold;font-size:14pt;text-align:center"/></style></block></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><block>
                        <contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents>
                        <style><CSS value="text-align:center"/></style></block></contents></tableCell></tableCells></tableRow></tableRows></table></contents></tableCell><tableCell><contents><date>
                  <style>
                   <dataFormat>
                    <dateFormat/>
                   </dataFormat>
                  </style>
                 </date><time>
                  <style>
                   <dataFormat>
                    <timeFormat/>
                   </dataFormat>
                  </style>
                 </time></contents><style><CSS value="text-align:center"/></style></tableCell></tableCells></tableRow></tableRows></table>
          </contents>
         </block></contents>
        <style>
         <defaultStyles>
          <defaultStyle refStyle="ta"/>
         </defaultStyles>
        </style>
       </block></contents>
      <style>
       <defaultStyles>
        <defaultStyle refStyle="ph"/>
       </defaultStyles>
       <CSS value="padding-bottom:10px"/>
      </style>
     </pageHeader>
     <pageFooter>
      <contents>
       <table>
        <tableRows>
         <tableRow>
          <tableCells>
           <tableCell>
            <contents>
            </contents>
            <style>
             <CSS value="vertical-align:top;text-align:left;width:25%"/>
            </style>
           </tableCell>
           <tableCell>
            <contents>
             <pageNumber/>
            </contents>
            <style>
             <CSS value="vertical-align:top;text-align:center;width:50%"/>
            </style>
           </tableCell>
           <tableCell>
            <contents>
             <textItem><dataSource><staticValue>IL1CMR0003</staticValue></dataSource></textItem>
            </contents>
            <style>
             <CSS value="vertical-align:top;text-align:right;width:25%"/>
            </style>
           </tableCell>
          </tableCells>
         </tableRow>
        </tableRows>
        <style>
         <defaultStyles>
          <defaultStyle refStyle="tb"/>
         </defaultStyles>
         <CSS value="border-collapse:collapse;width:100%"/>
        </style>
       </table>
      </contents>
      <style>
       <defaultStyles>
        <defaultStyle refStyle="pf"/>
       </defaultStyles>
       <CSS value="padding-top:10px"/>
      </style>
     </pageFooter>
    </page>
   </reportPages>
  </layout>
 </layouts>
 <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><namedConditionalStyles><advancedConditionalStyle name="Conditional Style 1"><styleCases><styleCase><style><CSS value="background-color:red"/></style><reportCondition>[QS-AS-RS-PS].[Report Run Status] ='FAILURE'</reportCondition></styleCase><styleCase><style><CSS value="background-color:green"/></style><reportCondition>[QS-AS-RS-PS].[Report Run Status] is missing</reportCondition></styleCase></styleCases><styleDefault/></advancedConditionalStyle></namedConditionalStyles></report>

Comments

  1. A-TLVFebruary 23, 2011 at 10:42 AM

    It sounds like a very good idea, is there a way you could share the FM model/Package? so we can use the report. If possible you just need to post the model.xml code, we can take it and publish the 'BI MetaData' package.
    thanks,

    ReplyDelete
  2. Santosh KanthetyFebruary 23, 2011 at 1:41 PM

    I modified the package as it had lot of other systems audit data from various apps. You can edit the package name on the report xml to audit and use this model.xml. It's too long to post it as a comment,you can email or leave your email address on here and i can send it to you.

    ReplyDelete
    Replies
    1. UnknownAugust 15, 2012 at 8:05 AM

      Hi Santosh,
      could you pls. send me that FM-Model?
      Your solution sounds really interesting...
      Carsten

      Delete
    2. Santosh KanthetyAugust 16, 2012 at 6:45 PM

      What is your email address?

      Delete
  3. A-TLVFebruary 23, 2011 at 2:24 PM

    Please send it arturo2k@gmail.com
    Thanks much!

    ReplyDelete
  4. AnonymousMarch 2, 2011 at 3:12 PM

    Hi Santosh - trying to get your model (xml) here as well. Sound like real helpful idea you have over there. Can you please email it to me to afay@san.rr.com? Thanks so much!

    ReplyDelete
  5. AnonymousMarch 13, 2011 at 8:44 AM

    And to me - stella_ssbg@abv.bg

    Thank you so much! This is really great idea

    ReplyDelete
  6. AnonymousMarch 15, 2011 at 10:10 AM

    hi, i would appreciate if you could send me the model(xml).sounds very interesting and practical.

    Barry
    pad23au@yahoo.com.au

    ReplyDelete
  7. AnonymousMay 18, 2011 at 7:29 PM

    hi, it would be much helping to Cognos administrators please send me the model(xml).it is very interesting.
    mail id: smurugan2@gmail.com

    Thanks in advance
    Murugan

    ReplyDelete
  8. Crystal Casey RhoadesJuly 9, 2013 at 10:18 AM

    Cognos administrators please send me the model(xml).it is very interesting.
    clrhoades@gmail.com

    ReplyDelete

Post a Comment

Popular posts from this blog

Query Cognos 8/10 Content Store

I came across these queries that can give you good insight on the data and maintenance of Content Store in Cognos 8/10. You can also leverage the CMTools utility that comes with cognos install. ..\Cognos\bin\utilities\cm\CMTools\CMTools.exe select count(*) as "total number of objects" from CMOBJECTS; select c.NAME as "class name", count(*) as "number of objects" from CMOBJECTS o, CMCLASSES cgroup by o.CLASSID, c.CLASSID, C.NAME having c.CLASSID=o.CLASSID order by c.NAME ; select c.NAME as "class name", count(*) as "number of secured objects" from CMOBJECTS o, CMCLASSES c, CMPOLICIES pwhere o.CMID=p.CMID and p.POLICIES is not null group by o.CLASSID, c.CLASSID, C.NAME having c.CLASSID=o.CLASSID order by c.NAME ; select count(*) as "Total number of outputs", SUM(cast(coalesce(DATALENGTH(DATAPROP),0) as bigint)) as "Total size of outputs" from CMDATA where DATAPROP is not NULL; select
Read more

Cognos 8/10 CAMID-User ID/LDAP ID report

I came across this slick KB article on how to get the list of active sessions/users currently on any cognos environment and implemented it. https://www-304.ibm.com/support/docview.wss?uid=swg21385278 After I got this to work the servlet that runs only retrieves the CAMID and the session start time. I started looking around in the content store tables and was able to successfully  retrieve the CAMID and the associated  LDAP ID/User ID. I made this a query subject in the Audit model and joined it to the security groups table and created a report that will give me User ID, CAMID, AD Group, Name , Email. Query : SELECT  distinct ldap_id,     last_name,     first_name,     email,     objid as CAMID     FROM            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,                   c1.surname last_name, c1.givenname first_name,                   c1.email email, c1.objid              FROM cmobjprops1 c1 LEFT OUTER JOIN cmobjprops33 c33                   ON c33.cmid = c1.cmid          
Read more

Cognos Cross-tab Report Studio Report Ungroup Items to display as list on every line

I recently had a requirement where the user wanted data from an existing Crosstab report to repeat every line so he can use the output in excel for comparison/analysis. It must be an old trick for most of us as I see so many posts around but none of them are straight forward and are hard to follow so i thought why not log it here, besides its only been 10 months since I shared anything. Sorry guys I'm in support right now so no time to explore new tips and tricks or maybe i'm being lazy ;) By default Crosstab groups columns nested based on the position on the cross tab, the trick here is to identify your lowest and highest level of data granularity. Lets pick the below crosstab as an example with PRODUCT, DESCRIPTION, LOCATION as nested dimension columns and default measure of SALES $ across MONTHS.                   Measure: Sales $      MONTH 1 | MONTH 2 .................................... Product | Description | Location |       $10             $20 Cognos groups PRO
Read more