Parameters questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Parameters questions

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2010-03-29 : 07:49:26

I have a report that presently has 2 parameters used in my dataset. I am using sql text. The first is StartDate and the other is EndDate. I use a BETWEEN in my WHERE clause to select only rows that fall between theses values. This all works correctly.

But now I want to add some additional parameters. The parameters are Doctor, Drugs Used, and Surgery Type. I have created seperate datsets for each of these. I want to make these dropdown lists that the user can click on and select. But I want to be able to mix and match these parameters. For example, if the user only selects the Doctors dropdownlist, my report should display all rows between the dates and that match this doctor. In this case, it would ignore the Drugs Used and Surgery Type.

Presently, when I run the report it is forcing me to select an item from each dropdown list. How can I make this not mandatory? What is the best way to accomplish this?

Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 11:47:30

what you need to do is add a default value like <No Selection> to each of list for Doctor,DrugsUsed and Surgery Type parameters. then in parameter tab set this as default for all the parameters and write your where condition like


WHERE DateField BETWEEN @StartDate AND @EndDate
AND (Doctor=@Doctor OR @Doctor='<No Selection>')
AND ([Drugs Used]=@DrugsUsed OR @DrugsUsed='<No Selection>')
AND ([Surgery Type]=@SurgeryType OR @SurgeryType='<No Selection>')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bh0526
Yak Posting Veteran

71 Posts

Posted - 2010-03-29 : 13:25:26

Ok, I understand. But how do I add the <No Selection> row to each of my SQL lists? For example, I use this SQL statement for my doctors:

SELECT Distinct dr_name
FROM surgeries
ORDER BY dr_name

Thanks,

Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 13:33:23

quote:
Originally posted by bh0526

Ok, I understand. But how do I add the <No Selection> row to each of my SQL lists? For example, I use this SQL statement for my doctors:

SELECT <No Selection> AS value,0 AS OrderVal
FROM surgeries

UNION ALL

SELECT Distinct dr_name,1
FROM surgeries
ORDER BY OrderVal,value

Thanks,

Bob

something like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bh0526
Yak Posting Veteran

71 Posts

Posted - 2010-03-29 : 16:21:37

Your query didn't work since it returned thousands of identical rows Like: <No Selection> 0

I added a TOP(1) to your SELECT prior to the UNION ALL and now it works. Not sure if this is the most efficient way but it does work.

Thanks,

Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 14:18:04

oops sorry

you need only this


SELECT <No Selection> AS value,0 AS OrderVal
UNION ALL

SELECT Distinct dr_name,1
FROM surgeries
ORDER BY OrderVal,value

no need of any TOP 1 or table reference

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources