Cognos 11 Prompt Page

[bswenson]

We would like to have a prompt page for our reports going forward.

It would have a value prompt drop down with reporting periods like Last Month, Current Month, Last Year, Last Fiscal year, etc.  We have a table with the correct dates for the selected timeframe for each day.  It would also contain Custom Range, which would then display Date Prompts so they could run the report for any timeframe they wanted.

We have the drop down working fine and custom range works fine.  The problem we cant figure out is how to put the correct dates from our Timeframe table into the date prompts for use in our filters and custom SQL.  It has been recomended we try javascript, but we are brand new to Cognos and dont know where to start.

We are using a relational DB2 LUW database and upgraded to Cognos 11.0.5.

Any help would be appreciated!  Thanks!

[Blue]

Hi. Have you looked at the Javascript API example reports that IBM provides?
CA11 uses external JavaScript files referenced by a Custom Control. The API allows you to manipulate the values displayed, selected, and defaulted in an prompt object.

[bswenson]

Thanks for the reply Robert!

Yes, we have looked over all the API examples supplied with Cognos 11.  I have also found a few Javascript examples that would do what we needed.  I tried to copy them but they didnt seem to work with the new version.  But i am not a Javascript guru by any means.

[dougp]

Why would you need the values from your table to populate a date prompt that exists so that the user can choose something other than what is in the table?

Did you try using conditional blocks to display either the value prompts OR the date prompts?  You can control it with a value prompt.

Cognos is a web application.  In the UI, it doesn't work with data types.  Everything is a string.  When you use a date prompt, the parameter receives a string like 2017-08-28.

In the filter, Cognos knows about the data type.  It will handle the parameter value differently based on the data type.  If the filter expects a date, it just doesn't put quotes around the value from the date prompt.

If you use a value prompt to do the same thing (output a date string in a format like 2017-08-28), Cognos may treat it like a string so you'll get [Query Subject].[Date] = '2017-08-28', which may not work.

Rather than using the simple parameter notation:

Code Select Expand

[Query Subject].[Date] = ?MyDateParam?
You can use a query macro:

Code Select Expand

[Query Subject].[Date] = #prompt('MyDateParam','date')#
That may implicitly cast the data type and work well.  If it doesn't, try:

Code Select Expand

[Query Subject].[Date] = #prompt('MyDateParam','token')#

[bswenson]

I wrote this in our early stages of thinking about this problem.  The main thing we would like is for a report to be schedulable and also be able to run with custom dates at any time.  We designed a prompt page with a bunch of drop down values like Today, Yesterday, Last week, last month, last fiscal year, etc.  We also have a custom range.  When they select custom range, a conditional block will appear with our 2 date parameters where the user can enter the desired dates and we can use those dates with the query macro in our Cognos querys.

What we are left trying to do is we really need is a way to populate the date parameters with the dates from our database table when they select one of the other choices like Previous Month.  This would also need to happen when the report is scheduled to run.  This would allow the Macro Query to work for all of the choices.

Thanks for your reply!

[CognosPaul]

How often is your dwh updated? If you have a daily schedule I'd recommend modifying the time dimension to include relative date flags.

add
todayFlag
yesterdayFlag
lastWeekFlag

and so on.

In the value prompt you could then set each of the options to be something like:

USE                                          |    Display
---------------------------------------------+-------------
[Namespace].[Date Dim].[Today Flag] = 1      |   Today
[Namespace].[Date Dim].[Yesterday Flag] = 1  |   Yesterday
[Namespace].[Date Dim].[Last Month Flag] = 1 |   Last Month
[Namespace].[Date Dim].[Last Year Flag] = 1  |   Last Year
custom                                       |   Custom Date Range



In the filter expression, your macro would look something like:

Code Select Expand

#
case prompt('dateRange','token')
when 'custom' then '[NS].[Date].[Date] between ' + prompt('beginDate','date',timestampMask($current_timestamp,'yyyy-mm-dd')) +' and ' + prompt('endDate','date',timestampMask($current_timestamp,'yyyy-mm-dd'))
else prompt('dateRange','token')
end
#

You could also use the case statement to calculate each option in the static prompt:

Code Select Expand

when 'yesterday' then '[NS].[Date].[Date] = ' + sq(timestampMask(_add_days($current_timestamp,-1),'yyyy-mm-dd'))

This solution will also allow you to easily schedule the reports. No JavaScript required at all.

[dougp]

Agreed.  As I get more experience using Cognos macros, my approach may change.  For now, for many of my reports that have user-selectable date filters and the requirement to schedule a report for "last month" or "last week", I have two reports:  one interactive and one scheduled (with the dates calculated, not prompted).  It means maintaining the same report twice, but since the reports rarely change once they are developed, it seemed like a simpler solution.