setting parameter drop down to the max date in the list

Question

An SSRS report has multiple parameters, each using it's own stored procedure and dataset. The user first selects a school district from a drop down list. From there, a second drop down populates with the schools in that district.

Once the user picks a school, a third drop down populates with a list of certification dates from which the user can select one. All the queries and drop downs populate correctly.

How to display the most recent date instead of the <Select a Value>?

As an example, for school A, there are three dates from which the user can select one, the most recent being 07/30/2015. The query sorts the dates in Desc order so I want the first one be the default, not <Select a Value>. Rather than go into all the things I've tried, how can this be done?

Looks like you're missing a couple pieces of information from your question. The last sentence in your first paragraph -- "instead of the..." what? And in the second paragraph, "I want the first one be the default, not..." what? Do you have a default set on your parameter? If so, what's it set to? — molleyc

Hannover Fist Hannover Fist · Accepted Answer · 2015-08-04T22:31:53

I usually have a separate dataset for the parameter query and add a MAX column with a subquery to find the latest date.

SELECT DISTINCT CONVERT(VARCHAR(10), checkprintdate, 101) AS CHECK_DATE,
        (SELECT CONVERT(VARCHAR(10), MAX(checkprintdate), 101) AS X1
            FROM paycheck AS P2) AS MAX_CHECK_DATE
FROM paycheck
ORDER BY CHECK_DATE DESC

I use the CheckDate as the Value and the MaxCheckDate as the Default.

setting parameter drop down to the max date in the list

2 Answers