Cognos Analytics

 View Only
  • 1.  Move Group By Subtotal Above Details Rows in Report - List Table

    Posted Wed May 10, 2023 11:04 PM

    Hi All,


    We are using IBM Cognos Analytics with Watson (11.2.0).


    I have design this one list table where we group the list table by internal_facility_name and get the total balance for each facility. Is there a way to move the group by subtotal above the data rows?

    Any help is deeply appreciated.



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------


  • 2.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    Posted Thu May 11, 2023 01:31 AM

    Hi Mohamad,

    1. select the list and go to more options ...



    2.  Insert list headers using this option
    3. select the overall list header that was added
    4. again go to the same more options ...

    5. Insert desired list rows cell above as required(you can delete the list header).
    6. unlock the report and add those totals to the newly added rows.

    Thanks!!



    ------------------------------
    P S
    ------------------------------



  • 3.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    IBM Champion
    Posted Thu May 11, 2023 01:39 AM

    The first thing I like to do is change the visual aids to uncheck Show Repeating:


    It only affects the development environment, but I find it makes it significantly easier to see.

    With the list selected click on the three dot menu->Headers & footers->List headers & footers:



    Set it to look like this:


    It will look like this:

    Click on the subtotal row and then the Split Cells button


    You'll get this:

    ctrl click on internal_facility_name and sub_product_name and then Merge Cells


    Next you'll want to drag the measures into the sub-total cells, but in order to do that you'll need to unlock the report:

    Now you can ctrl-drag the Total(previous_balance) from the overall total row or drag them in from the query.



    This should give you exactly the result you're looking for.



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



  • 4.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    IBM Champion
    Posted Thu May 11, 2023 10:53 AM
    Paul Mendelson nailing another one great instructions! :)


    Logan Whitaker

    PMO, HEB Supply Chain Planning & Operations

    o: 210.938.6043 m: 210.429.3181

    e: whitaker.logan@heb.com







  • 5.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    Posted Sun May 14, 2023 11:23 PM

    Hi Paul,

    Thank you for the detail instructions. Your instructions make it look so easy :)



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------



  • 6.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    Posted Thu May 25, 2023 11:16 AM
    Edited by Kristen Park Fri May 26, 2023 09:20 AM
    This is how I solved the same problem, but for a crosstab.
    (1) From toolbox move "crosstab cells (with fact cells)" to the top of a crosstab.
    (2) Do the same thing but place it at position shown on the image 2. Do this several times if necessary.
    (3) This would be the result.
    (4) Place the column for which your want subtotals (column "Year") into black cell on the image 4.
    (5) Now you have subtotals.
    (6) Delete original subtotals and apply green color to new subtotals.
    ( I will post my image tomorrow. Sorry. I have some technical problems now.  )



    ------------------------------
    Milan Milovanovic
    ------------------------------



  • 7.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    Posted Fri May 26, 2023 09:11 AM

    This is how to solve the same problem, but for a crosstabs:
    1) We start with a crosstab on image.
    2) Then, from a toolbox, we add "Crosstab space (with fact cells)" on the top of a crosstab.
    3) Next, we place column that defines our subtotals (column "year") on the right side of that "Crosstab space".
    4) On this image, we can see our result. Now we have subtotals.
    5) If we select column "Year", we can click on Σ button in a toolbar, to add grand total bellow subtotals.
    6) Final step is to delete original subtotals and grand total, because we don't need them any more.

    Image shows how to display subtotals on the top of a crosstab.


    ------------------------------
    Milan Milovanovic
    ------------------------------



  • 8.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    Posted Wed July 12, 2023 02:15 PM

    Thank you so much for the instructions.  Is it possible to hide this empty area (the data detail)?  When I run the report (2nd screenshot), I got those blank lines.  All I wanted to remain on the report (and when exporting) is the green highlight one.  Thanks.  I'm a newbie to Cognos.



    ------------------------------
    Martie Lysas
    ------------------------------



  • 9.  RE: Move Group By Subtotal Above Details Rows in Report - List Table

    IBM Champion
    Posted Thu July 13, 2023 01:57 AM

    Hi Martie, Without knowing the report details I assume you could move the inner cell content to the white lines (unlock first), adjust the grouping of the list and remove the green lines. But if you need it like that for some reason you could set the inner borders in the cells' properties to 0 and in HTML output they are gone. 



    ------------------------------
    Philipp Hornung
    Business Intelligence Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------