Cognos Analytics

 View Only
Expand all | Collapse all

Adding expressions together

  • 1.  Adding expressions together

    Posted Tue November 15, 2022 09:20 PM

    Greetings,

     

    I am trying to extract the values of multiple expressions. I figured out some but I really am super green when it comes to this. Here is what I started with:

    [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]

     

     

     

    If there are values for more than one of the expressions it returns all the values (i.e. test1, test2, etc.) but if there is only one value it returns nothing. Is there a way to return all the values regardless if there is one or four?

     

    Thanks,

    Chad


    #CognosAnalyticswithWatson


  • 2.  RE: Adding expressions together

    IBM Champion
    Posted Wed November 16, 2022 05:39 AM
    Edited by System Fri January 20, 2023 04:42 PM
    Hi Chad, If I got you right then your problem is that the concatenated string is empty if one of the attributes is empty (even though I'd rather expect || or + instead of & operator). In this case you can use the function coalesce to solve this. E.g. instead of the first attribute you need to use COALESCE ( [Inspection].[CEPO Special Inspection1].[CEPO Special Inspection1 Code] , '' ) and same for the other attributes.

    To only add the needed ', ' you could try something like this (untested):
    SUBSTRING ( 
    COALESCE ( ', ' + [Inspection].[CEPO Special Inspection1].[CEPO Special Inspection1 Code] , '' ) + 
    COALESCE ( ', ' + [Inspection].[CEPO Special Inspection2].[CEPO Special Inspection2 Code] , '' ) + 
    COALESCE ( ', ' + [Inspection].[CEPO Special Inspection3].[CEPO Special Inspection3 Code] , '' ) + 
    COALESCE ( ', ' + [Inspection].[CEPO Special Inspection4].[CEPO Special Inspection4 Code] , '' )
    , 3 )


    ------------------------------
    Philipp Hornung
    ------------------------------
    #CognosAnalyticswithWatson