IBM Business Analytics

 View Only

  • 1.  Multiple Data Items Combined into One Cell/Column

    Posted Wed December 14, 2022 01:36 PM
    Greetings, 

    I am wondering if there is a way to combine multiple data items into 1 cell. Currently I have 6 separate data items:
    • [Inspection].[CEPO Special Inspection1].[CEPO Special Inspection1 Code]
    • [Inspection].[CEPO Special Inspection2].[CEPO Special Inspection2 Code]
    • [Inspection].[CEPO Special Inspection3].[CEPO Special Inspection3 Code]
    • [Inspection].[CEPO Special Inspection4].[CEPO Special Inspection4 Code]
    • [Inspection].[CEPO Special Inspection5].[CEPO Special Inspection5 Code]
    • [Inspection].[CEPO Special Inspection6].[CEPO Special Inspection6 Code]

    I would like to combine them into one cell/column but am not sure how to do it. In simplified terms it would look something like this with each separate data item being separated by a comma:
    [Inspection].[CEPO Special Inspection1].[CEPO Special Inspection1 Code], [Inspection].[CEPO Special Inspection2].[CEPO Special Inspection2 Code], [Inspection].[CEPO Special Inspection3].[CEPO Special Inspection3 Code], [Inspection].[CEPO Special Inspection4].[CEPO Special Inspection4 Code], [Inspection].[CEPO Special Inspection5].[CEPO Special Inspection5 Code], [Inspection].[CEPO Special Inspection6].[CEPO Special Inspection6 Code]

    Any assistance would be greatly appreciated. 

    Thanks, 
    Chad

    ------------------------------
    Chad King
    ------------------------------


  • 2.  RE: Multiple Data Items Combined into One Cell/Column

    Posted Wed December 14, 2022 02:12 PM
    There are two ways to accomplish this that I'm aware of.  One is if you want the underlying query to already have the values aggregated like this (which would require writing the query using SQL as opposed to your data model).  The other is if you only need the values aggregated on a report page such as in a list object.  Do either of this meet your needs?  If so, let me know and I'll tailor an answer.

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

    Original Message


  • 3.  RE: Multiple Data Items Combined into One Cell/Column

    Posted Wed December 14, 2022 05:35 PM
    Hi Logan, 

    Thanks for your response. I don't believe I have SQL as an option but was hoping to do the work in the Data Item Expression area if possible. I have attached a screenshot of what I am talking about. 

    Thanks,
    Chad

    (love your profile pic...one of my favorite shows as a kid!!!)


    ------------------------------
    Chad King
    ------------------------------

    Original Message


  • 4.  RE: Multiple Data Items Combined into One Cell/Column

    Posted Wed December 14, 2022 06:26 PM
    Edited by Logan Whitaker Wed December 14, 2022 06:26 PM

    Hi Chad.  Yes, ALF was the BEST!  I'm really amazed there hasn't been a remake of that show.  

    As for your challenge, I do not believe Cognos has a native syntax to do what you are aiming for within an expression window.  Like I mentioned before, there ARE two other ways it can be done.  One is using the Repeater Cell function in a Report Page, the other is using SQL within the query itself.  I read your contributions and saw you were, at least recently, new to Cognos.  The solution you may need isn't straightforward to describe or walk one through unless you are intimately familiar with the lingo and functionality.  If you are available to discuss it (I'm not a sales person or vendor of anything BTW), please shoot me an email (it's listed on my profile).



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

    Original Message


  • 5.  RE: Multiple Data Items Combined into One Cell/Column

    Posted Mon February 06, 2023 01:49 PM
    Edited by Logan Whitaker Mon February 06, 2023 01:52 PM

    Never saw a reply from you, but sharing some methods here for the group.  First method is via SQL as probably the easiest.  Second is a mixture of both a calculated field in your query plus the Repeater Cell on a report page.

    SQL Method

    If you have permissions and/knowledge to write SQL for the query, you can use the LISTAGG function.  You can Google how this works for your particular DB version (Oracle, mySQL, etc.)  This will get you exactly what you are looking for displayed within the results of the query.

    Repeater Cell Method

    This method is recreating the LISTAGG function using nuts and bolts of Cognos functionality.  First, in the query that has the fields you are using for your report page, you need to put a calculation in there to put a comma after each value unless it is the last value for your group.  This is because the LISTAGG function does this for you automatically, but recreating it in Cognos does not do this so you have to tell it when to stop using commas.  If you don't care about a hanging comma at the end you can just put a calculation like this:  [Your Field Here] || ', '

    Then you go into the report page that uses your query and drag a Repeater Cell into that area of the report to display the calculated field you created (the one with the value + conditional comma at the end).  You may need to unlock the page in order to drag a repeater cell onto it.  You'll have to create a Master Detail Relationship for the Repeater Cell to work and probably need to define the sorting sequence for which values come first vs last.  But that's essentially it.  

    Of course, both these methods can be confusing to someone new to Cognos or SQL so that's why I offered a deeper dive so I could help with your particular situation.  But for those reading this that are intermediate-level, you should pick up on it rather easily.  

    Good luck!



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

    Original Message


Related Content