Cognos Analytics

 View Only
Expand all | Collapse all

Expand/Collapse in CA Report Crosstab

  • 1.  Expand/Collapse in CA Report Crosstab

    IBM Champion
    Posted Wed November 11, 2020 12:55 AM
    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
    ------------------------------

    #CognosAnalyticswithWatson


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

    Posted Mon November 16, 2020 04:35 AM
    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
    ------------------------------

    Original Message


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

    IBM Champion
    Posted Mon November 16, 2020 05:05 AM
    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
    ------------------------------

    Original Message


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

    Posted Mon November 16, 2020 06:15 AM
    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
    ------------------------------

    Original Message


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

    Posted Mon November 16, 2020 06:34 AM
    and here is a short video....



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

    Original Message


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

    Posted Tue November 17, 2020 01:43 PM
    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
    ------------------------------

    Original Message


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

    IBM Champion
    Posted Tue November 17, 2020 04:46 PM
    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
    ------------------------------

    Original Message


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

    Posted Wed November 18, 2020 02:56 AM
    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
    ------------------------------

    Original Message


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

    Posted Mon April 04, 2022 01:55 AM

    Dear Torben,

    I am unable to get the latest Java script for expand/collapse functionality, 
    I tried below link but its not working
    https://pmsquare.com/analytics-blog/2019/1/7/custom-javascript-in-cognos-expand-and-collapse.

    Please share it.



    ------------------------------
    BHUPENDRA BERDIA
    ------------------------------

    Original Message


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

    IBM Champion
    Posted Mon April 04, 2022 10:23 AM
    Hi Bhupendra,

    It looks like the linked had an extra dot at the end. Try https://pmsquare.com/analytics-blog/2019/1/7/custom-javascript-in-cognos-expand-and-collapse 

    Let me know if you have any trouble getting it to work, I know the guy that wrote it.

    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


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

    IBM Champion
    Posted Sat November 21, 2020 06:07 PM
    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
    ------------------------------

    Original Message


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

    IBM Champion
    Posted Mon November 23, 2020 01:19 AM
    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
    ------------------------------

    Original Message