CODEWITHSUNDEEP
reporting-servicesssrs-2008
fabioleardini
fabioleardini

Reputation: 75

One month limit - Reporting Service

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?

Upvotes: 1

Views: 5078

Answers (2)

Tarzan
Tarzan

Reputation: 4538

This is an old question, but there is an answer. A high-level explanation of the solution is to write code in the Report Properties, add a hidden parameter that contains code to validate the parameters, and a conditional expression in the DataSet. Please see the following link for a detailed explanation: https://gugiaji.wordpress.com/2012/03/26/easy-step-by-step-ssrs-parameter-validation-using-code

Upvotes: 1

PaulF
PaulF

Reputation: 1183

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

Upvotes: 2

Related Questions