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.
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:
#prompt('Date Parameter Name', 'date', 'current_date')#
or default the filter to yesterday:
#prompt('Date Parameter Name', 'date', '_add_days(current_date,-1)')#
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.
I second MDXpressor. Always avoid JS if possible. The macro solution is the standard for this requirement.
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:
#prompt('Date Parameter Name', 'date', '[Namespace].[Query Subject].[Stored data refresh date]')#
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!