Solved

MS Reporting Services Advanced Parameters

Posted on 2007-04-05
9
913 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:straucha
[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 Comments
 
LVL 8

Expert Comment

by:MOA81
ID: 18857978
I guess what you are trying to do is a formula condition

you can do that through Expressions
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18857990
Hi Create this dynamic Query in your Procedure and execute Within your Procedure.
and Used this Procedure in your Reports.



0
 
LVL 1

Author Comment

by:straucha
ID: 18858071
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 14

Expert Comment

by:simon_kirk
ID: 18858615
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
 
LVL 18

Expert Comment

by:chrismc
ID: 18860382
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
 
LVL 1

Author Comment

by:straucha
ID: 18860945
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
 
LVL 18

Accepted Solution

by:
chrismc earned 500 total points
ID: 18860984
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
 
LVL 1

Author Comment

by:straucha
ID: 18861103
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
 
LVL 1

Author Comment

by:straucha
ID: 18861192
Sorry my mistake.  I pasted your code in the graphical Desgin Query.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

How to increase the row limit in Jasper Server.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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