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

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 :

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

Prompt Page Setting :

Value prompt with properties as shown below

Default selction :

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

Resultant prompt page :

Prompt page displays Year value prompt with default selection – 2017

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

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.