Solved

MS Reporting Services Advanced Parameters

Posted on 2007-04-05
9
909 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 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