Since version 10.2 of IBM Cognos BI Suite, IBM included an API to access and manipulate prompt objects. Since prompt objects are the main instrument we use to allow users to communicate with a report (Interactivity or user selection), being able to manipulate them however we see fit can change user experience dramatically for the better. There are countless examples of how the prompt API can be used to achieve this. For example, dynamic defaults: Suppose you have two prompts, for region and for products. You want the default product selected to be the best selling product in the region selected. With prompt API, this can be achieved easily.
In this post I’d like to showcase one of the first solutions I ever wrote using Prompt API, because it was one of the things I wanted to solve for a long time.
Every so often we add “from date” and “to date” prompts to a report, to use for filtering the report output to show only data from the date range selected. The problem is, most users and most use cases don’t require the sort of flexibility a date range offers: most users will not run their sales-per-branch report between April 23rd and May 2nd, for instance, because it’s an arbitrary chunk of dates. Instead, users are likely to filter dates for last month, this MTD, QTD, YTD, last week and so on. So, basically, set, standard, comparable time frames. And sometimes the date range prompt can be replaced with a drop down list of such pre-set ranges, but other times, users ask to still have the flexibility of choosing to and from date, but nonetheless, still mostly use the set, comparable ranges.
Now, in order to select last month’s dates with two date prompts, your average user will need 6 clicks: One to open from date calendar, one to page back to last month, one to click on “1”, and the same process with the “To date” prompt. For YTD, they might need more. That’s a lot of clicks. Also, developers often have to write scripts to get the default value right, and because these date prompts are never done in a centralised, reusable manner, they end up writing a script for each report. I have long fought the war on developers wasting time by doing things more than once, and this case is no different. Even if reports require different default times, the solution can still be generalised, and therefore made reusable.
I’m using two date prompts, and adding 7 pre-defined links, which, when clicked, fill in their respective dates. So, for example, clicking on MTD will set the from date prompt to the 1st of the current month, and the to date prompt to today’s date. There’s also a verification mechanism in place to ensure that from date is always earlier than to date, or equal to it.
But how do I make this solution generalised? Let’s take a look at the report studio report:
The bit in blue is the error message the user will get if they choose an end date that’s prior to the start date. The bit in blue is a text that should be replaced with another text – containing just one number between 1 and 7, corresponding with a dynamic ate range. “1” is YTD, 4 is WTD and so on.
Now, if you drag in a layout reference object to this interface, here’s what you’ll get:
You can override&replace the warning message and the default text. So, if the default for a certain report is “last month”, you’ll override “Defaults”
Drag in a text item and insert “5”
When you run the report, the default would be last month:
This way you can set a different default value for each report in a reusable manner.
I’m attaching the XML, of course, but pay attention to these caveats:
1. The script has seven preconfigured date ranges. You can change them or add to them as you require, and use the general example in the code, but it requires some knowledge of scripting. Unfortunately, I will not be able to provide support for such customisations.
2. If you’re relying on my script to manipulate weeks, pay attention that my script assumes Monday is the first day of the week. Israelis especially, this means you’ll have to change this logic (Weeks in Israel begin on Sundays).
3.This is 10.2.2 – You can downgrade it to 10.2.x by changing the version number at the top.
daterange.txt (1993 downloads)
Nimrod (Rod) Avissar is a Cognos expert, with a penchant for specialized UX solutions. Feel free to drop me a line! (LinkedIn).