Cognos Analytics

Expand all | Collapse all

Expand/Collapse in CA Report Crosstab

  • 1.  Expand/Collapse in CA Report Crosstab

    Posted 15 days ago
    Hello folks,

    I am trying to expand and collapse levels in a crosstab over a TM1 cube in a CA report.  I have seen bunch of things saying that it is not possible unless I use a javascript file.

    I have three questions?
    1. Is it still true (as at CA 11.1.7) that we cannot expand and contract levels in a crosstab in CA?
    2. Does anyone have a javascript that can be used to achieve this?
    3. Will this be rectified in a soon to be released version of CA, given that it is apparently now available in dashboards?
    Thanks

    JV

    ------------------------------
    John Vaughan
    www.ExploringTM1.com
    john.vaughan@exploringtm1.com
    ------------------------------


  • 2.  RE: Expand/Collapse in CA Report Crosstab

    Posted 10 days ago
    Hi John

    Have you tried the new Data Table in Reporting - it supports expand/collapse:

    Torben

    ------------------------------
    Torben Noer
    Offering Manager, Cognos Analytics and Planning Analytics
    IBM
    ------------------------------



  • 3.  RE: Expand/Collapse in CA Report Crosstab

    Posted 10 days ago
    Yeah, I did give that a crack, Torben, but I could not make it work.

    Do you have any Youtube's on how to do it as just eluded me!

    Thanks mate,

    John

    ------------------------------
    John Vaughan
    www.ExploringTM1.com
    john.vaughan@exploringtm1.com
    ------------------------------



  • 4.  RE: Expand/Collapse in CA Report Crosstab

    Posted 10 days ago
    There is a detailed example in the documentation: https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_cr_rptstd.doc/c_rptstd_data_table.html

    Torben

    ------------------------------
    Torben Noer
    Offering Manager, Cognos Analytics and Planning Analytics
    IBM
    ------------------------------



  • 5.  RE: Expand/Collapse in CA Report Crosstab

    Posted 10 days ago
    and here is a short video....



    ------------------------------
    Torben Noer
    Offering Manager, Cognos Analytics and Planning Analytics
    IBM
    ------------------------------



  • 6.  RE: Expand/Collapse in CA Report Crosstab

    Posted 9 days ago
    Hi Torben,
    This is a great feature which makes a report more interactive to the end users, however am seeing a limitation on exporting the output to other formats, Its not rendering in excel or csv. I am also wondering if this won't support any other formats then why it does shows the options in cognos viewer as well as in report editor which is confusing.

    ------------------------------
    Noor Ansari
    ------------------------------



  • 7.  RE: Expand/Collapse in CA Report Crosstab

    Posted 9 days ago
    Thanks for this Torben. Your video is great and explains it well. I agree that this is really useful for allowing a user to have multiple levels form one dimension and expand down and up.

    Unfortunately it doesn't do what we need though. We have a GL cube with Account, Cost Centre, Version and Time dimensions amongst others, and a separate Measures dimension. We have then created a hierarchy in the Account dim that rolls up to a summary P&L. In CA, this Summary P&L set is placed on rows and we have intersections of time, version and variance calculations as columns - so a crosstab. We need to be able to expand "in place", so that, for example, Labour Costs expand down to Salaries and Wages, Taxes, Pension etc. Then Salaries and Wages can expand down to Salaries as one row and Wages as another. Finally, it would then collapse up when one fo the subtotals is double clicked. There is a .js available from CognosPaul that does this, but I was a really hoping that it would be built in (again) to Cognos (I seem to remember being able to do this with PowerPlay).

    Cheers,

    John

    ------------------------------
    John Vaughan
    www.ExploringTM1.com
    john.vaughan@exploringtm1.com
    ------------------------------



  • 8.  RE: Expand/Collapse in CA Report Crosstab

    Posted 8 days ago
    Correct - CognosPaul created the original expand/collapse functionality in Reporting - I believe the latest is here: https://pmsquare.com/analytics-blog/2019/1/7/custom-javascript-in-cognos-expand-and-collapse.

    We have focused on bringing the expand/collapse functionality into the crosstab in Dashboards - its' supported today and we have more advanced functionality coming for the crosstab in Dashboards.

    Looking at the requirements for using specially PA cubes in a crosstab, it's not a simple thing and I see people using several crosstabs to stich together the P&L crosstab - this is also on the roadmap, to supply some templates to help with the P&L reporting - we will also include the expand/collapse functionality.

    I will ping you directly to get some more details and examples.

    Torben

    ------------------------------
    Torben Noer
    Offering Manager, Cognos Analytics and Planning Analytics
    IBM
    ------------------------------



  • 9.  RE: Expand/Collapse in CA Report Crosstab

    Posted 5 days ago
    Hi John.
    If you're using the interactive mode it is possible to expand and collaps single members:

    If you want to expand one or more complete level, one way is to create a report using the descendants funktion in combination with a prompt.

    Jens



    ------------------------------
    Jens Bäumler
    Senior Consultant and Trainer
    Apparo Group
    ------------------------------



  • 10.  RE: Expand/Collapse in CA Report Crosstab

    Posted 3 days ago
    Thanks for this @Jens Bäumler. For some reason this is not working when I have a TM1 dimension as the source, using a single measure for the crosstab and having intersections of time and version for columns.​​

    ------------------------------
    John Vaughan
    www.ExploringTM1.com
    john.vaughan@exploringtm1.com
    ------------------------------