Cognos Value Prompt Dynamic Defaults Without JavaScript

Cognos value prompt defaults can easily be set to static values. However, if you wanted to dynamically set their default values you had to employ JavaScript code on the prompt page. In fact, I have a tutorial that explains how to use JavaScript to set date prompts dynamically here. You could use a similar technique with value prompts. However, there are some developers that feel uneasy about using JavaScript in their reports and the potential maintenance headaches that entails.

I’ve discovered a novel technique that allows you to set value prompt defaults dynamically without using any JavaScript. This approach has multiple benefits:

1. No JavaScript to have to write and maintain
2. All logic is contained in standard data items in the prompt query for easy modification by existing Cognos BI developers

Note that this technique will only work with value prompts.

Technique

The technique has three main steps:

1. Change the prompt query key value of the desired record dynamically to a known, specific value
2. Configure the specific value as the prompt default
3. Configure the filter expression to convert the default back to a usable key if selected

First, we use logic constructs to identify the value we want to be the default. We then change its key value to a specific value (referred to as the dummy key) that isn’t in use by actual values. Next, we set this dummy key as the default value of the prompt. Finally, we modify the filter to identify the passed in dummy key and adapt the filter logic to accommodate it.

Tutorial

For this tutorial we are going to create a value prompt that will allow the choice of a single calendar month using the Radio button group UI. We want the current calendar month to be the default value. We want this value to change depending on the month we run the report. If the report is run in December then the prompt will default to December. If run in July it will default to July. The report will use this month parameter in a filter, displaying only records where the month matches the passed in value. This might be used in a report to compare month over month sales.

We are going to use the GO Sales (query) package in the Cognos sample packages. If you don’t already have the samples installed you can install them by following the instructions found in this IBM document. Having the sample data and packages available will make following along with the tutorial much easier.

If you want to skip the configuration, you can download a sample report here. Note that the sample report has just the query and prompt page configured. The filter logic described below is not included for simplicity.

Steps

Create Report

1. Create a new blank Report Studio report using the GO Sales (query) sample package.

Create Query

1. Add a new query to generate the values for our prompt

Configure Query

1. Open the new query
2. Add the Month data item to the query. It’s located in Go Sales (query)>Sales (query)>Time
3. Add a new Data Item to the query
4. Provide the following expression in the expression window that appears:
   
   CASE WHEN [Sales (query)].[Time].[Month (numeric)] = extract(month,current_date) THEN 0 ELSE [Sales (query)].[Time].[Month (numeric)] END

   1 2 3 4

   CASE WHEN [Sales (query)].[Time].[Month (numeric)] = extract(month,current_date) THEN 0 ELSE [Sales (query)].[Time].[Month (numeric)] END

5. Name the data item Month Key
6. Add the Month (numeric) data item to the query. It’s located in Go Sales (query)>Sales (query)>Time

Create Prompt Page

1. Add a prompt page

Create Value Prompt

1. Open the prompt page
2. Drag over a Value Prompt object from the toolbox

Configure Value Prompt

1. Click Finish on the wizard that appears to accept the defaults
2. Select the new prompt
3. Set the following data properties:
   Query: Query1
   Use Value: Month Key
   Display Value: Month
4. Select Radio button group for the Select UI property
5. Double-click on the Sorting property to bring up the sorting dialog
6. Drag the data item Month (numeric) into the Sort List on the right
7. Click OK to confirm the sorting changes
8. Double-click on the Default Selections property to bring up the default selections dialog
9. Click on the Add button on the toolbar at the bottom of the dialog. It looks like a page with a yellow sun in the corner.
10. Enter 0 in the dialog and click OK
11. Click OK on the default selections dialog to accept the change

Test

1. Run the report and confirm that the current month is selected in the value prompt

Modify Filter

Since we changed one of the returned value’s keys to 0, this value will be passed into the report if the user selects the default value. The last thing we have to do is modify the filter we are using to accommodate this special value.

Since we don’t have a report page and query we don’t currently have a filter. However, our filter before modification might look like this:

[Sales (query)].[Time].[Month (numeric)] = ?Parameter1?

We need to change this to detect when the default value has been chosen. This could be done in several ways. I’m going to show you a couple:

If..Then

Boolean

In either case, we are simply reversing the logic we used to identify the default value in the data item. Since we know that a passed in value of 0 corresponds to the current month we can use the current month in our filter when that value is passed in. Otherwise, we use the value passed in directly to filter the report like we would normally do.

Result

When we run the report we should see a set of 12 radio buttons, one for each month, with the current month selected.

Conclusion

With a bit of configuration you can easily set the default value of a Cognos value prompt dynamically using the standard Report Studio capabilities: No JavaScript needed.

Updates

2015.12.8 – I neglected to mention that if you were to use drill-throughs with a report using this technique you have to take some precautions. Any report using a passed in parameter that uses this method will have to use similar modified filter logic. Thanks Rod Avissar for pointing this out.