Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Cognos 11.1.7 - Combine multiple rows into one

    Posted Mon January 23, 2023 09:43 AM
    Edited by Jenifer Broughton Mon January 23, 2023 09:46 AM
    I have done this in the past, but I just can't remember how exactly.

    I have 2 rows of data for one unique address.  I remember using a running-count based on the unique field and then creating new fields based on the running count.  I then used the min and max aggregation property for the fields depending on the running count.  Then I did a grouping.
    I must be missing something because it's not working as expected.

    I looks like it's working in the HTML version from what I've tested, but when I try dumping into either version of excel it still shows both rows.

    Any insight would be appreciated.

    Thanks,
    Jen

    ------------------------------
    Jenifer Broughton
    ------------------------------


  • 2.  RE: Cognos 11.1.7 - Combine multiple rows into one

    Posted Tue January 24, 2023 03:54 AM
    The min/max aggregation should work fine. Can you cut the report down to only the list with the unique field and the min/max address fields? Post the report XML, I'd like to see what's going on. What database are you using? If it's oracle you could use listagg. SQL Server is a little more difficult since you would probably need a UDF to do it properly (https://erikdarlingdata.com/udf-inlining-and-string-aggregations/). Have you seen this article? https://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/

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

    Original Message


  • 3.  RE: Cognos 11.1.7 - Combine multiple rows into one

    Posted Mon February 06, 2023 03:09 PM
    Edited by Logan Whitaker Mon February 06, 2023 03:13 PM

    I agree with previous feedback that using LISTAGG in SQL is way easier and consumes much less resources than trying to recreate it using Cognos (because Cognos doesn't have an easy way of doing the same unfortunately).  The way Cognos translates drag-and-drop functionality into SQL queries is rather cumbersome.   Below is one tried and true method using Cognos to get the desired effect on a report page.  Only LISTAGG will get it on your underlying queries, though (most use cases only need it on the report page).

    Cognos Recreation of LISTAGG

    1. On your report page, drag on the data point for your "data for one address" (I'm assuming it's a List object). 
    2. Unlock the report page and delete the value of "data for one address" leaving only the header of the column still showing but values now blank.
    3. Drag a Repeater from the Tools menu into where the values once were.
    4. Point the Repeater to the same query as your underlying List object.
    5. In the Properties pane for your Repeater set the Master Detail Relationship so the "data for one address" is grouped together however your data is segmented.
    6. Then drag the data item for your "data for one address" into the actual Repeater cell. 

    This is only going to concatenate the "data for one address" in no particular order and without any delimiting characters.  If you wanted to rank the values in your underlying query or something, you could then add that calculated rank field name to the Properties of the List object and then use that field in the Grouping & Sorting options for the Repeater.  If you need to separate them with commas, you can add commas to the end of your expression in the "data for one address" field.   Can also incorporate a max row count expression to identify the last row for each group where you don't want to add the comma.

    ------------------------------
    Logan Whitaker
    ------------------------------

    Original Message