Cognos Analytics

 View Only
Expand all | Collapse all

Wildcards do not allow to insert multiple values in a Prompt

  • 1.  Wildcards do not allow to insert multiple values in a Prompt

    Posted Thu November 03, 2022 07:20 AM
    Hello everyone,

    Could you please advice how I can change the below code in BACC/Cognos, in order to catch multiple values and not just one?

    For one of the reports, I have a detailed filter with syntax:
    [Business View].[T_DETAILS a1].[GBG_NAME a1] in ('%'+upper (?pGBG_Name?)+'%')

    It is working fine, but in the Prompt it allows me to insert just one value, and not multiple. I have changed the Prompt properties to be Multi select = "Yes" and Multi-line = "Yes"
    I need to keep the wildcards but at the same time to be able to insert multiple values in the prompt.
    If I change the code to [Business View].[T_DETAILS a1].[GBG_NAME a1] in (upper (?pGBG_Name?)) it works fine, it allows to me insert multiple values in the Prompt, but i need to keep the wildcards as well.

    ------------------------------
    Denitsa Pavlova
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Wildcards do not allow to insert multiple values in a Prompt

    Posted Fri November 04, 2022 12:05 PM
    Edited by System Fri January 20, 2023 04:30 PM
    Hi Denitsa.
    This is a tricky one. You could use the filter below. This should keep the prompt multi-select.
    You can also use this filter in combination with a text box prompt which is not multi-select.
    The user is able to type in text separated with a ';' and only change promptmany in prompt in the filter below.
    [Business View].[T_DETAILS a1].[GBG_NAME a1] contains 
    #
    join ( ' or [Business View].[T_DETAILS a1].[GBG_NAME a1] contains '; 
    	split ( ','; 
    		csv ( 
    			toupper (
    				split (';'; 
    					promptmany ( 'pGBG_Name'; 'token'; '' ) 
    				)
    			)
    		)
    	 )
    )
    #​


    ------------------------------
    Thomas van der Meer
    ------------------------------



  • 3.  RE: Wildcards do not allow to insert multiple values in a Prompt

    IBM Champion
    Posted Fri November 11, 2022 09:48 AM
    Hi, Did I get it right that you are looking for a combination of the 'like' operator with multiple values? I'm not sure but maybe the Cognos Analytics prompt macro offers a solution for this?

    ------------------------------
    Philipp Hornung
    ------------------------------