Solved

Ssrs date parameters

Posted on 2016-09-19
12
50 Views
Last Modified: 2016-09-28
Is there a way if you have two date parameters, one is start date and the other is end date, to make sure that the end date is after the start date ?
0
Comment
Question by:basile
  • 9
  • 2
12 Comments
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805252
You don't have direct access to parameter values while they are being entered. You can specify an available values list, and then only those values will be allowed. The list can come from a query that depends upon an earlier parameter.

As I have noted elsewhere, recently, the things you can do with the built-in parameter UI in ReportViewer are limited, and you frequently have to make compromises. For this issue, my usual compromise is to just make sure that no results are returned if the dates go the wrong way. This can trigger a NoRows message from a data region. I don't remember off the top of my head if that property (of the data region) can be dynamic, to display a different message if the date parameters are in the wrong order. I don't think so, but I'll check (never tried it).

 It would also be possible, using visibility flags, to display a textbox in place of the normal report output if the date parameters are reversed. Another compromise would be to swap the values in the code that references them, if the first date is larger, but that could be quite confusing to the user.

My general answer to this type of question is that if you write your own parameter UI, customized for your application, and do it well, you won't have to deal with the limitations of the built-in one and all the user complaints that follow.
0
 
LVL 42

Expert Comment

by:zephyr_hex
ID: 41805259
You can add the following validation:

function ValidParams(byval startdate as datetime, byval enddate as datetime) as boolean
if enddate < startdate then
return FALSE
else
return TRUE
end if
end function

Open in new window


Based on: https://gugiaji.wordpress.com/2012/03/26/easy-step-by-step-ssrs-parameter-validation-using-code/

Add that code to the Report Properties Code tab.
To implement it, add a new hidden parameter with the following default value:
=code.ValidParams(Parameters!startdate.Value, Parameters!enddate.Value)

Open in new window

Then change your original query so that it only runs when the validation passes:

=IIF(Parameters!IsValidParam.Value, "Your SQL Query")

Open in new window


You may want to then add a message to your report that displays when Parameters!IsValidParam.Value is false, so the user knows why they aren't getting any results.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805285
I usually just code the SQL-level validation check into the SQL query using query parameters (as opposed to report parameters). But that should work. I don't much care for dynamic SQL in dataset queries, unless it can't be done another way. (such as when reporting off a star schema using a parameter-driven code generator).

You might have given me an idea, though, for another approach to intercepting and validating parameter values while the UI is active. I am not terribly optimistic that it will work, but I need to go take a walk and think about it...
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805320
The idea I had might actually work. When I fired up VS 2015 to try it, however, it crashed for the umpteenth time. I am now installing an update that came out last Friday and that might help with the crashes. It looks like there is a typo in the filename (VS14 instead of VS15), but oh well. I'll try my parameter validation idea when all the updating is finished. It may be a while.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805464
It appears to be possible to have the NoRows message for a tablix change according to parameter values, although I haven't actually gotten around to trying that, yet. If you have the main dataset return no rows when the date range is invalid, the NoRows expression can also check for an invalid date range and display a message to that effect instead of displaying a "no rows" message, which would be misleading.

My idea worked -- I was able to prevent the report from running if the date range was invalid by defining an internal parameter that depends on a dataset query that takes the two date range parameter values and compares them, returning a different value depending on whether or not the validation test fails. The value from the dataset determines the default value of the internal parameter, and if that value is not valid for the parameter then report execution is blocked.

I have not, however, been able to return any sort of intelligible error message to the user. One idea was to return blank or an error message string from the validation dataset, and to allow blank as the only valid value, thinking that RS might actually show the 'bad' value. Nope. It just says the value is "missing," which is not even true. But such is the built-in parameter UI for ReportViewer. It has a lot of issues.

I have one more idea to try, that doesn't depend on the available values list or any other built-in parameter validation feature.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805526
I can cause an exception message of my choosing to be displayed by the local report viewer (used in Visual Studio), without allowing the report to run. It's not very pretty but it more or less gets the message across (see screen shot). I don't know if the HTML renderer will display the message or not. RS has a knack for hiding exception messages, and the behavior of different renderers can vary. But it might work.

I will post a sample RDL file later on that demonstrates both methods for handing a parameter range error (exception and NoRows). I still have a thing or two to try to improve the appearance of the exception message.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805533
Oops, I guess I should attach the screen shot before sending.
RangeException.png
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805573
Here is a sample RDL containing both methods. I improved the exception message somewhat (see new screen shot).

The exception message method runs by default. To see the NoRows method, delete the ValidateDateRangeParams internal parameter. When it doesn't throw an exception, the NoRows error message takes over instead. The latter method does cause the report to run, however, while the exception stops execution.

I still don't know if the HTML renderer shows or hides exception messages, and it's not something I can easily test today.

The exception itself is thrown in the IsValid function in the report Code property. The message can be changed there. The ValidateDateRangeParams parameter calls the function to obtain its default value. It seems to reliably do this after the Start and End parameter values have been set. It would be possible to force the execution order, if necessary, by creating another dataset and having it call the validation function, perhaps from a query parameter expression that is returned by the dataset. The internal parameter would then obtain its value from the dataset. That is more complexity than I wanted to introduce to this example, however, and it does not appear to be necessary.

The NoRows expression is a property of the tablix. Select Tablix1 in the Properties pane in Report Designer and you should find the property in the list, well down.

The tablix itself is just silly stuff. I needed some rows and didn't want to have to actually query tables from a database. You do have to connect the data source to a valid database, however. the database isn't actually used; it's just there to keep RS happy. I suppose I could have used an XML source or something.

This exercise certainly satisfied my curiosity about a few things I had never worked out. I hope it may be useful to others as well.
BetterRangeException.png
ValidateDateRangeParams.rdl
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41805602
I inadvertently posted the SSRS 2016 version of the RDL. Here is the 2008 version, which should be compatible with later versions.
ValidateDateRangeParams.rdl
0
 
LVL 1

Author Comment

by:basile
ID: 41806097
Wow. Thanks for all the effort, I will try to implement this today.
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41806535
You're welcome. It became a personal cause. :-)

I think I prefer using the NoRows message to report the error, rather than the exception message. No matter what I try to do with the exception, it is ugly and somewhat confusing (but I thought it was a neat trick).

I don't think it matters much that the report has to execute in order to display the NoRows message, because when the parameters are bad the dataset is short circuited to return zero rows, and it should execute quickly.

To summarize using NoRows:
  1. Code the main dataset to check for an invalid date range (and/or other parameter value issues), and have it return zero rows if anything is not right. Complex queries may need additional tweaking to ensure that subqueries don't execute anyway and cause errors. In my example, I simply added "WHERE @End >= @Start" to the query.
  2. Define a NoRows message for the outermost tablix of the report (since any inner tablixes will not show if there are no rows at the outermost level). If there is more than one tablix at the top level, or if there are charts, you will have to decide what to do.
  3. Make the NoRows message conditional depending on whether or not the report parameters are valid, so as to distinguish between a no-rows condition where there simply is no data and one caused by invalid parameters as determined in step 1, above. The 'normal' message can be an empty string, but  it is a good practice, generally, to include a message rather than show a blank page. In my example RDL file, the NoRows message expression is:
=IIf(Parameters!End.Value >= Parameters!Start.Value, "No data rows were found", "Start date parameter is greater than End date parameter")

You can style the NoRows message using the other properties of the NoRows section of the tablix property sheet.
0
 
LVL 1

Author Closing Comment

by:basile
ID: 41820980
Thanks so much! i finally got a chance to implement.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now