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.
My solution uses JavaScript and Prompt API to add to the date prompt functionality. Here is how it works:
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 (9389 downloads)
Nimrod (Rod) Avissar is a Cognos expert, with a penchant for specialized UX solutions. Feel free to drop me a line! (LinkedIn).
hi, nice solution. will definitely try but one query, since it uses javascript, will it degrade performance. also, if this code is use as default for all date prompt reports, will it in anyways impact performance. thanks
Hi, this will bear no implications on the query. Your filter remains something like [date] between ?fromdate? And ?todate?
This solution will simply allow automatic input of set relative dates to your date prompts.
Thanks for this timely post Nimrod,
Today was the day I needed to implement Prompt API to assign calendar based dates to prompts!
Your examples were very helpful, as I had all sorts of troubles pushing a date into the prompt; it still seems a bit strange that the prompt requires a ‘-‘ separated text string rather than an actual date, so thanks for pointing this out.
One change I made is I used two separate date prompts rather than a range. I find the built in range prompt very clunky, difficult to style, and we typically arrange our prompts horizontally so it just doesn’t fit.
Hi Andrew,
Thanks for your kind words. Glad I could help.
If you’ll look closely at my solution, you’ll see I’m using two separate date prompts. In fact, this is the reason I had to put in a “from date is prior to to date” verification mechanism. Date range prompts have that verification built in.
The main reason I’m not using a date range is because date ranges create worse sql than two separate date prompts.
Love this solution. Thanks! Question…I also need last quarter, but not sure how to get that. Can you assist?
Hi Dee, thanks!
As I wrote, I am not in a position to cater for every possible date range required – I do have a day job 🙂
However, just to show you and future readers how you can use the already scripted samples and build off them, I’ll start you off:
You need to find the first month of last quarter.You can see in the script provided that this quarter’s first month is found like this:
thisMonth<3?0:(thisMonth<6?3:(thisMonth<9?6:9));
to the first month of last quarter should be:
thisMonth<3?9:(thisMonth<6?0:(thisMonth<9?3:6));
You'll also need a variable for last quarter's year:
var lastQuarterYear=thisMonth<3?thisYear-1:thisYear;
Setting the dates should be something along these lines:
fromDate=new Date(lastQuarterYear,lastQuarterFirstMonth,1);
toDate=new Date(lastQuarterYear,lastQuarterFirstMonth+3,0);
Perfect! Thanks!
Its nice solution but it will not work if you make a schedule with the relative dates.
The system will still use the selected relative dates at the moment you run the report. For example if today is 3th of May and i schedule this report using MTD. It will run everyday with selection: 01/05 until 03/05.
Do you have any java solution about this?
Toni, this isn’t meant for dynamic date selection in scheduled reports – it’s a UX solution meant to make life easier for users of the report when executing it live. Dynamic dates in report scheduling are best achieved via the Event Studio. Javascript won’t help much there anyway.
Thanks Nimrod for the this solution. It is very helpful. I was wondering if I need to get the fiscal calendar dates instead of regular calendar. How can I achieve that. Any help would be appreciated.
Thanks in advance!
Gary,
The general idea is the same, but the calculations will vary. You’ll have to create if clauses to cater for year changes (For example, if your FY is July-June, and you pick “last month” on July, you’ll need to change the year) and of course quarter handling will be different, but other than that it’s all roughly the same.
Thanks Rod for your reply. My client’s FY starts with either the first Sunday of Feb or last Sunday of Jan, so the dates are not fixed. However, I have achieved this using the value and date prompt in Cognos.
Hi Rod, this is awesome, thank you for sharing with us!
I do have a question though. Is it possible to have it automatically reprompt with the new values once you click the link for the particular set of date values? Users currently will click the link and the new dates populate the from and to dates and then they have to click reprompt for the report data to be filtered with the new date values.
thanks so much
Disregard, we found what we needed: Just added SetPromptControl(‘reprompt’);
We put it in the following area of the main script:
——————————————————————
fromPrompt.addValues(vals);
SetPromptControl(‘reprompt’);
}
//set up the links
—————————————————————–
Hi Nimrod,
I have the same requirement but with value prompt static choices, how to integrate with fromdate and todate prompts on selection of radion button values.
Regards,
Inamul