Global Business Analytics

 View Only
Expand all | Collapse all

TIP: Ungroup a Crosstab Header for later filtering output in Excel // Repeat Crosstab Rows Content

  • 1.  TIP: Ungroup a Crosstab Header for later filtering output in Excel // Repeat Crosstab Rows Content

    IBM Champion
    Posted Wed May 10, 2023 01:49 PM

    Hi Cognos lovers,

    today I was approached by a user asking: "How do I repeat the row headers in a crosstab ... so ungrouping it. I need this for filtering in Excel"

    Good question!

    Looking everywhere in the product ... no options to ungroup anywhere.

    No answer in documentation, nor my preferred search-engine. ChatGPT invented options that are not in the product :-) ROTFL.

    With the help of IBM Champions @Jens Bäumler  and @Paul Mendelson I figured out how to "ungroup" the crosstab header rows.

    Add a running-count([query-item]) to your query.

    Add that item to your crosstab as first element in the rows.

    Then hide it.

    Hope this helps someone.

    regards,
    Ralf

    P.D. Still wondering why there is no button for this in the product. Would be a cool enhancement.



    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------


  • 2.  RE: TIP: Ungroup a Crosstab Header for later filtering output in Excel // Repeat Crosstab Rows Content

    IBM Champion
    Posted Thu May 11, 2023 03:06 AM

    Within the report properties there is an option called 'Group repeating cells - export to Excel'.

    This may achieve what you want without additional query items. 



    ------------------------------
    Marc Reed
    ------------------------------



  • 3.  RE: TIP: Ungroup a Crosstab Header for later filtering output in Excel // Repeat Crosstab Rows Content

    Posted Thu May 11, 2023 03:56 AM

    Hi Marc,

    that's working! Thanks a lot for sharing this!

    BG, Timm



    ------------------------------
    Timm Rosengarten
    Senior Consultant Analytics
    TIMETOACT Software & Consulting GmbH
    ------------------------------