Parameterized URL passing current date or last 7 days

[cognosguru]

Hi guys and girls,

Has anyone done dynamic parameter value passing into a date prompt?  I know how to pass a hard-coded date but want to pass today's date or a rolling 7 days.  Any help would be appreciated.

[MDXpressor]

Are you actually looking update the fields of the prompt with values, or are you trying to have a default filter value applied with today's (or some other calculable date)?

If you're trying to update the actual values in the prompt, I can't really help without a .js solution.

If you're trying to implement a default value for a date filter, the best solution I've found is to use a macro.  There are a number of slick .js solutions as well, but I don't like being dependent on the Cognos API remaining consistent from version to version.

as an example you could create yesterday filter value with the following macro:

Code Select Expand

#prompt('Date Parameter Name', 'date', 'current_date')#

or default the filter to yesterday:

Code Select Expand

#prompt('Date Parameter Name', 'date', '_add_days(current_date,-1)')#

[cognosguru]

Thanks MDX, I was trying to update the default value with today's date.  I have the .js solution and was trying to see others.  I thought of having a hidden prompt with today's date and pass it in.  Haven't tried the macro, will do.

[AnalyticsWithJay]

I second MDXpressor. Always avoid JS if possible. The macro solution is the standard for this requirement.

[MDXpressor]

Let us know how it worked out, in the end. 

By the way, when working with something like a monthly data load into a datawarehouse, it can be helpful to have your ETL update a single-field, single-row table with the date that that any default filter should use (1st of month, last of month...).  That way your filters always default to the last-loaded data.  From there, you just call that field into your macro as the default value:

Code Select Expand

#prompt('Date Parameter Name', 'date', '[Namespace].[Query Subject].[Stored data refresh date]')#

[cognosguru]

Great idea!  I found a completely different way of dealing with my report issue which doesn't involve passing parms via url any more.  The issue was how to have 2 reports ran based on the date range selected.  If one range - one report from 1 package DQM (datasource A), if another date range selected - another report different package CQM (datasource B).  I was able to do that with 1 report and report reference pointing to other and dates as drivers.  So never got a chance to test out your suggestions.  I am really appreciative of your help though!