SSRS Parameter that does not go past todays date

Hello Experts Exchange
I want to have a Parameter in my SSRS report that the user can select, I want to validate the input and have the report not run if they select a future date, how do I do this in SSRS?

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
You could set up your dataset so that it only returns data when the entered data is considered valid.  Something like this:

IF @DateParameter < GETDATE()
    <your SELECT statement>
0
SQLSearcherAuthor Commented:
Hello ValentinoV
I am using Oracle as my datasource so the script you have given me is not going to work,  I have tried writing it for oracle but it want me to define the DateParameter, do you know how I can resolve?
0
Nico BontenbalCommented:
You could use this as the Visibility (Hidden) property of the Tablix or Chart that is on your report.
=Parameters!EndDate.Value>Today()

Open in new window

Then you could also add a text box to your report in red with for example the text 'Select an end date on or before today'. For this text box you use this expression for Visibility:
=Parameters!EndDate.Value<=Today()

Open in new window


If you have a tablix on the report you can also set a filter on the Dataset with:
expression: =Parameters!EndDate.Value<=Today()
type: Boolean
Operator: =
Value = True
That way the recordset will not have any data in it when the EndDate parameter is later than today. You can then use the NoRowsMessage property of the Tablix to display a specific message to the user.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ValentinoVBI ConsultantCommented:
The parameter in the code needs to get mapped to the parameter on the report.  This is done by the Parameters page of the Dataset properties.

Details: Associate a Query Parameter with a Report Parameter (Report Builder and SSRS)

BTW: what Nicobo mentioned will work as well.  Just take in mind that performance-wise it is a less favorable option.  The data will still be retrieved from the server, even with the "invalid" date value.  Unless my IF condition is implemented too :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nico BontenbalCommented:
You are absolutely right ValentinoV. When I was testing my solution I did think about this, but I forgot to mention it in my post. Thank you for reminding me. My solution will probably not have the best performance.

I can't test it but for Oracle ValentinoV's solution might work when you do something like this:
IF :DateParameter < CURRENT_DATE THEN
    <your SELECT statement>
END IF;

Open in new window

0
SQLSearcherAuthor Commented:
Hello Nicobo
I tried your code but it did not work;  it still wants me to define the parameter even tho there is a parameter with the same name in SSRS.

I did find this web page from someone with the same problem and there maybe a solution to it at the bottom of the page, but I don't know where to add the code that is detailed on the page, does it go on the code page under the report properties? or does it go under the dataset?

Here is the web link;
https://community.oracle.com/thread/2274597?tstart=0

What do you think I should try next?
0
ValentinoVBI ConsultantCommented:
What they say over there is to create a function that contains the IF logic.  That would work too, I've done similar implementations using stored procedures on SQL Server.  The function would need to be created on the Oracle database.  As I'm no Oracle expert I can't really help much with syntax on this, sorry.

You may also want to review the following: Oracle Connection Type (SSRS)
0
SQLSearcherAuthor Commented:
I wont be able to create a function in Oracle, can I use an expression to have two queries to the data in the dataset?

Regards

SQLSearcher
0
SQLSearcherAuthor Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.