?
Solved

Ssrs date parameters

Posted on 2016-09-19
12
Medium Priority
?
315 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:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 2
12 Comments
 
LVL 16

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 44

Expert Comment

by:zephyr_hex (Megan)
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 16

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

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 16

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 16

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
 
LVL 16

Expert Comment

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

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 16

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:Auerelio Vasquez
ID: 41806097
Wow. Thanks for all the effort, I will try to implement this today.
0
 
LVL 16

Accepted Solution

by:
Megan Brooks earned 2000 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:Auerelio Vasquez
ID: 41820980
Thanks so much! i finally got a chance to implement.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 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