Date in a Value Prompt Default to Most Recent Date

[tjohnson3050]

So I've always wanted to be able to have a date in a value prompt that defaults to the most recent date in the data.

Got this working today:

The prompt has the date field as the display value, and the number of days between the date field and the maximum date field as the use value.  I used four fields in one query that drives the value prompt and serves as a page query, then a master detail relationship from the list up to the page handles the date filtering.  The default prompt value is set to zero.  The data item that controls the filtering is adding zero days to the max date, and updates with any chosen value based on the day distance from the max date.

Query 1 (both prompt query and page query)

                Data Items
                1. The report date to be used as the prompt display value
                     [Report Date Field]

                2. Most recent report date
                      Maximum([Report Date Field] for Report)

                3. Number of days between the report date and the max report date date – prompt use value
                      _days_between([Max Date],[Report Date Field])

                4. Date Filter - subtracts the prompt use value (days) from the max date and drives the master detail link to the date field in a list report
                     _add_days([Max Date],-1 * ?DatePrompt?)


Thoughts?

[AnalyticsWithJay]

Is Javascript allowed?  :)

[tjohnson3050]

I used to say I avoided javascript because of the chance of reports breaking with a Cognos upgrade.

Really I just never took the time to master it.  That being said, from what I remember, in javascript you can work with date data relative to the current date, but not relative to dates in your data.  I could be wrong.

Either way, this solution is way to pre-populate a value prompt with the most recent date in your data without using javascript.

[AnalyticsWithJay]

I used to say I avoided javascript because of the chance of reports breaking with a Cognos upgrade.

Really I just never took the time to master it.  That being said, from what I remember, in javascript you can work with date data relative to the current date, but not relative to dates in your data.  I could be wrong.

Either way, this solution is way to pre-populate a value prompt with the most recent date in your data without using javascript.

I have an article about doing this exact requirement on my rarely updated blog. Let me know if this is of any help to you or if you need it modified:

http://www.cognoswithjay.com/selecting-a-dynamic-default-value-for-value-prompts-using-javascript/

You basically hide a singleton of your date, and javascript will pick up the hidden value, traverse your drop-down and select the matching value.

[tjohnson3050]

Very cool.  Thanks for sharing :)

[AnalyticsWithJay]

Very cool.  Thanks for sharing :)

Cheers :)