Cognos Analytics

Cognos Analytics

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

 View Only
Back to discussions
Expand all | Collapse all

default selection in value prompt is first value

  • 1.  default selection in value prompt is first value

    Posted Mon June 12, 2023 09:28 AM
      |   view attached

    Hi Team,

    Can you please help me how to get default selection is first value in value prompt in Cognos analytics.I have attached screen shot in period value prompt need

    default value is 2022 march



    ------------------------------
    Murali
    ------------------------------


  • 2.  RE: default selection in value prompt is first value

    Posted Mon June 12, 2023 11:02 AM
      |   view attached

    Hey,

    I like to use surrogate keys for this. I rewrite my keys of the prompt query with a value that will never exist in the data (e.g. -1). Here's an example with IBMs data:

    The calculation is 

    IF ([Prompt Retailer].[Retailer Code] = [best retailer code])
    THEN (-1)
    ELSE ([Prompt Retailer].[Retailer Code])

    The prompt object on the report page is then set to this -1 as default value:

    Last step is to reflect this cheat in the filter condition of your main data query. Example of the same report:

    (#prompt('Retailer';'Integer')#=-1 and  [Retailer Code] =[best retailer code])
    OR
    ([Retailer Code] =#prompt('Retailer';'Integer')#)

    Depending on your data it might be sufficient to just do a rank on the Period column. If (rank(Period)=1) then (-1) else (Period).

    There are also other ways like writing a javascript control which picks the first element from the drop down list. I'm a friend of using this data-driven approach.

    You'll find my sample report spec attached here. Hope this helps.



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Attachment(s)

    txt
    dynamic default selection.txt   32 KB 1 version
    Original Message


  • 3.  RE: default selection in value prompt is first value

    Posted Tue June 13, 2023 03:11 AM

    You could use this problem as an excuse to upgrade to Cognos 12!

    https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=reporting-defining-default-parameter-values



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 4.  RE: default selection in value prompt is first value

    Posted Tue June 13, 2023 11:39 AM

    As long as you are below CA 12 you can create a data element with a prompt macro having a default value in form of a relative date (e.g. # prompt ( 'P_YearQuarter' ; 'integer' ; '_year ( current_date ) * 100 + _month ( current_date ) ' ) #) and use this data element (only) instead of the parameter itself subsequentially. In the prompt property "text header" you can give the empty/default value there a generic description like 'this month'. If you are using the parameter also in layout calculations, you have to replace the empty value/description there as well by the default value/generic description (e.g. using case-when). 



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

    Original Message


  • 5.  RE: default selection in value prompt is first value

    Posted Wed June 14, 2023 08:23 AM

    Thanks for your help



    ------------------------------
    Murali
    ------------------------------

    Original Message


  • 6.  RE: default selection in value prompt is first value

    Posted Thu June 15, 2023 12:53 AM

    One of the biggest advances in Cognos 12 is you can now dynamically set the default value based on a query, macro, or layout expression. Forget the AI, dashboard, dataset, and other advances - the new parameter page is absolutely the best reason to upgrade to the latest version.



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

    Original Message


  • 7.  RE: default selection in value prompt is first value

    Posted Thu June 15, 2023 03:30 AM

    Hi Paul,  I haven't had a chance to play with the new c12 'dynamic' prompts. 

    Do they only work when a user run's a report. If we schedule a report will we still have to use the old techniques.



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 8.  RE: default selection in value prompt is first value

    Posted Mon June 19, 2023 03:42 AM

    That's a really good question.

    I'm testing like this.

    I have a crosstab that returns years and months in the columns with an optional value on Year.

    The prompt is based on the year as well, no default defined in the prompt object. Autosubmit is set to true.

    In the parameters pane I have one parameter, Year, defined:

    The default value is a simple report expression:

    When I run the report I get the expected output without any generated prompts:

    Setting the Year prompt to no-value also works as expected:

    The problem with JavaScript or default values here is that you can't easily unset a parameter. Here this just works great.

    While I've been writing this, I also scheduled the report to run in the background and again, it works exactly as I expected:

    This tells me that this does work with schedules. 

    I am absolutely LOVING this new feature.



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

    Original Message


  • 9.  RE: default selection in value prompt is first value

    Posted Mon June 19, 2023 04:12 AM

    Great for blank prompts then. In the sales demos IBM showed a calendar prompt defaulting to a day.
    If that's scheduled, would the schedule be stored with the prompt answer or the expression.
    (Calendar prompts have always been dodgy - the only prompt that has a default even when you don't set one.)



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 10.  RE: default selection in value prompt is first value

    Posted Mon June 19, 2023 06:38 AM

    Yeah, I've never liked Calendar prompts. Using the Prompt API on them has also been a bit buggy. In this picture I have a query calculating the min and max dates in the data, and I'm using that to control the prompt.

    On the first run the parameter is automatically set based on the max date:

    The table containing the date prompt and text items are sitting inside a singleton

    And the First Date Source and Last Date Source are both set to Report expression and the data item from the singleton. They don't support query sources, but this works great.

    Dates before the first date and after the last date will be grayed out

    It won't even let the user type in 2009:

    I'm very happy with this. Since it is setting the parameter, you can still report expressions based on the parameter value. Previously you would have to add something like:

    case when paramValue('Param') is null then 'Default Value Calculation' else paramValue('Param') end



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

    Original Message


  • 11.  RE: default selection in value prompt is first value

    Posted Mon June 19, 2023 06:46 AM

    Always another question - i need access to a C12 test system...

    In your example, Max Date is 20 Jul 2013. This is what the prompt is defaulting to. 

    If I schedule the report then is the schedule for this prompt set to 20 Jul 2013 or max day.

    In a weeks time my max day would be 27 Jul 2013. 

    What would the schedule report use for the prompt.



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 12.  RE: default selection in value prompt is first value

    Posted Mon June 19, 2023 07:55 AM

    It's calculating the date when the schedule runs, so the date prompt would update to 27 Jul 2013. But we can confirm this by using a date time prompt.

    I changed the report to add the current time to the max date. This is what it looks like when I saved it:

    I've scheduled the report to run every 3 minutes for the next hour. 

    Interesting, I'm getting a report server error. Trying to figure this out.

    It should be working, not sure what's going on there. Going to keep digging on it. Looks like it might be a problem with using dates or date times in parameter.



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

    Original Message