Value Prompt with Dynamically Chosen Default Values in Cognos RS - Perficient Blogs
Blog
  • Topics
  • Industries
  • Partners

Explore

Topics

Industries

Partners

Value Prompt with Dynamically Chosen Default Values in Cognos RS

Implementing Value prompts with dynamically chosen default value in Cognos Report Studio

Introduction :  This blog describes the steps to set Value Prompts in Cognos Report Studio with default value selections that are dynamically rendered.  A sample Problem statement taken up in this blog illustrates the idea which can then be extrapolated to other such similar scenarios.

Problem Statement  :  The prompt page of a report should  contain a Value prompt for ‘Year’ ; the Default selections for which should be the current Year . For example : If the report is executed on  5-Feb-2017  , the year prompt should display all possible years in the DB with default value being 2017.

Solution : One possible solution to the above problem statement ( the steps for which are detailed in this blog ) is to have a Prompt Query for the Year prompt with 2 data items – one that displays the year as it is and the other that sets a static value for the current year alone ( and rest values as it is) . This Query can be used by a value prompt .The display value for the prompt can be set to the former data item and Use value to the latter data item. Following this , the Default Selection property can be set to the aforementioned static value. Now the prompt page would display the year prompt with the respective current value.

In the backend query for the report , Detail filters have to be applied such that the result set is filtered for the corresponding ‘ Use value ‘ selections ( Keeping in mind the Static value assigned to current year ) in order to get the desired result.

STEPS:

Requirement :

Value prompts – Year and Month with the default selections of Current year and Current month

Value Prompt

Prompt Query : Year Prompt Query should have 2 data items

  • Year Number – year as such from the DB
  • Current/ Selected Year – Current year alone to be replaced with static value of 1 ( expression described further below )

Data Item Expression :

Data Item Expression

Prompt Page Setting :

Value prompt with properties as shown below

Value Prompt with Properties 

Default selction :

Default selection to be set to 1 – Static value mentioned in the prompt query

Default Selections

Resultant prompt page :

Prompt page displays Year value prompt with default selection – 2017

Prompt Page

Detail filter expression for Query of the expected list

The Detail filter should be implemented as shown in the image.

Either of the following two cases would execute in the Detail filter

  • Year prompt selected has 1 as its value (equivalent to current year ) thus filtering Records having Year of current date.
  • Year prompt selected does not have 1 as its value (equivalent to any year other than the current year ) thus filtering for that particular year

Expression Definition

Report sample output

Report Sample Output

Limitation : The above solution has a limitation that it doesn’t apply for Multi – select scenarios . This is because a case statement cannot be applied in the detail filter for a situation when the user selects 2 years say the current year 2017 ( static value 1) and another year  say 2016.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up

Categories