• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

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.
1 Solution
I guess what you are trying to do is a formula condition

you can do that through Expressions
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.

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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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


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!

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.
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

strauchaAuthor Commented:
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 '='
strauchaAuthor Commented:
Sorry my mistake.  I pasted your code in the graphical Desgin Query.
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now