One month limit - Reporting Service

Question

I am building a report in Micrososft SQL Reporting Services and, I have two date fields, startDate and endDate.

I need to limit the date range, at least in thirty days (or a month). For example, if startDate is 2012/01/01 and endDate is 2012/02/02, it should raise an error message, otherwise (being less than thirty days of difference), it can continue. How can I do that in reporting service?

Have you thought about using drop down list with dynamic values (E.G., 1 - 5 days, 6 - 10 days ... etc)? — Kane
You could write a VB program to call the report which checks the parameters first. — SarekOfVulcan

PaulF PaulF · Accepted Answer · 2012-07-02T21:50:14

You can use a data set to provide default values for your endDate parameter based on your startDate parameter.

Add a Dataset named DefaultStartDate. Set query text to:

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as [startDate];

Add a Parameter named startDate. Set type to Date/Time and get the default value from the DefaultStartDate query:

Default Values / Dataset=DefaultStartDate / Value=startDate

Add a Dataset named ValidEndDates. Set query text to something like the following, to generate a list of valid end dates based on the start date:

with A as
( select 1 as i UNION ALL select i+1 from A where i<30 )
select DATEADD(dd, i, @startDate) as [endDate] from A;

Add a Parameter named endDate. Set type to Date/Time and get the available values from the ValidEndDates Dataset:

Available Values / DataSet=ValidEndDates / Value=endDate / Label=endDate

One month limit - Reporting Service

2 Answers