Hi,
Say you have two parameters StartDate and EndDate, the simplest solution is to define a default value to both using min and max functions of the dates available on your dataset.
By this, create an extra dataset using SELECT MIN(yourdatefield) as sdate, MAX(yourdatefield) as edate from yourtable group by whateverfield.
Then use this dataset as default value for the your two parameters, using sdate for your StartDate and edate for your EndDate.
When your report loads, it will automatically fill in these dates to display that spans all your data.
An alternative solution is to use an expression in your main dataset, depending on whether you have other criteria in your where clause other than dates, this might become more complicated. But assuming you only have the date as your criteria, it will be
something like this:
="SELECT * FROM YourTable " & IIF(IsNothing(Parameters!StartDate.Value) and IsNothing(Parameters!EndDate.Value), "", " WHERE yourDateField BETWEEN " & Parameters!StartDate.Value & " AND " & Parameters!EndDate.Value)
So the idea is to check if the two parameters have values. If not, don't add a where clause which will return all your dates. If there are values, then add the where clause applying the date range.
The sql expression above is just an example to give you an idea. The syntax maybe wrong depending on your database. You will have to build the proper sql string to process the dates and use functions to convert them from string to dates for evaluation.
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t z
