Solved

MS Reporting Services Advanced Parameters

Posted on 2007-04-05
9
906 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
MS SQL 2016 from Database to Datawarehouse 6 37
SQL Server creating a temp table 7 40
sql calculate averages 18 32
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now