MS Reporting Services Advanced Parameters

Hello Experts,

I am using MS Reporting Services 2000 and creating my MS Reporting Services Reports in Visual Studio 2003.  When setting my SQL query string I can use Select Statements like the following no problem:
- Select  RegHrs, OTHrs FROM  JOBCLASS WHERE (PayDate >= @StartDate)  
 * StartDate is user defined parameter that I send from a web page.

What I would like to have the ability to do is a Select Statement Like the Following:
- Select  RegHrs, OTHrs FROM  JOBCLASS  @wherestr
* wherestr would be a user defined parameter representing the where clause part of the previous example

Am I able to accomplish this with MS Reporting Services 2000 ?  Is so, how.  Any help will be greatly appreciated.
LVL 1
strauchaAsked:
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.

MOA81Commented:
I guess what you are trying to do is a formula condition

you can do that through Expressions
0
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
Hi Create this dynamic Query in your Procedure and execute Within your Procedure.
and Used this Procedure in your Reports.



0
strauchaAuthor Commented:
Can you show me through code how to use a formual condition. I am using the Generic Query Designer in the Visual Studio development environment for Reporting Services to create my SQL Statement.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

simon_kirkCommented:
Probably easier to use a dynamic SQL statement or construct it in a Stored Procedure like:

declare @whereclause varchar(100)
declare @statement varchar(100)

SET @whereclause = 'WHERE (PayDate >= @StartDate)'
SELECT @statement = 'Select  RegHrs, OTHrs FROM  JOBCLASS  ' + @whereclause

EXEC(@statement)


0
chrismcCommented:
What you have asked for is known as 'Dynamic SQL' or a 'Dynamic Query'.
This is generally frowned upon because it is innefficient. The query can't be compiled until run-time, each time it's run.
Obviously there are times when you have no choice.

To do this in Reporting services, stay in the Generic Query Builder. You can use VB expressions here and that's how you build the query dynamically like so;
="Select  RegHrs, OTHrs FROM  JOBCLASS WHERE " + @WhereStr

In this particular scenario, you have two further issues:
1) The contents of @WhereStr must be syntactically correct for SQL otherwise it could crash the query. This will be a major issue if you are allowing end users to enter this in a parameter.  
2) Again, users are entering the contents of @WhereStr, you are making yourself open to 'SQL Injection' attacks. Imagine the user entered;
     RegHrs = 10; Delete From JobClass;

If the permissions aren't tight enough you will have an empty table!

Cheers
Chris
0
strauchaAuthor Commented:
Chrismc, you seem to be very close to the solution that I am after.  However, when I have the below code in my Generic Query Designer I am still getting an error.  
   ="Select  RegHrs, OTHrs FROM  JOBCLASS WHERE " + @WhereStr            

The error I am getting is "The Expression for the query 'JobClass' conatins an error: [BC30203] Identifier Expected".  Also, I have never used VB expression in the Generic Query Designer and was wondering if the Generic Query Designer just lets you put the above code you recommended into the SQL Pane or do you have to set it somewhere else.  I used expressions in the Layout Mode but not yet under the data mode.
0
chrismcCommented:
My fault!
The @WhereStr is a SQL variable name, but because we've started with the equals sign we are actually in VB so the variable name is invalid.
To refer to parameters in VB you use the Parameters collection and each parameter has a Value and Label property.

In short you query becomes;
    ="Select  RegHrs, OTHrs FROM  JOBCLASS WHERE " + Parameters!WhereStr.Value

Cheers
Chris            
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
strauchaAuthor Commented:
chrismc,
I took the above code:
 ="Select  RegHrs, OTHrs FROM  JOBCLASS WHERE " + Parameters!WhereStr.Value  
and I pasted it right into the SQL Pane under Generic Query Designer and I get the following error:
Incorrect syntax near '='
0
strauchaAuthor Commented:
Sorry my mistake.  I pasted your code in the graphical Desgin Query.
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
DB Reporting Tools

From novice to tech pro — start learning today.