Dynamic parameters - 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)

Dynamic parameters

Author

Topic

homeguard
Starting Member

32 Posts

Posted - 2008-05-22 : 12:34:09

I have a report that i want to beable to do some dynamic paramaters on it. I have a start and end date of when they want to run the report but i also have a mulivalue list box for users filtering for users. I want to add a supervisor filter on form also. But I have a problem.

I want to allow the users to select a supervisor and filter for all employees under that, or allow them to select a set of users and allow them to filter for that also. Also i would like when they select no supervisor or user it just filters for the dates and picks all up all the users.

I thought i could do something like in access like this

Like UserName & "*"

but it doesnt work.

Here is my dataset code i am using currently:

SELECT     U.Name, A.row_date, A.split, A.SumOfti_stafftime, A.AHT, A.AvgACW, A.AvgACD, A.AvailTime, A.SplitSkill_Incalls, A.SplitSkill_Outcalls, A.SumOfacdtime, 
                      A.SumOfti_othertime, A.SumOfacwtime, A.SumOfti_auxtime0, A.SumOfti_auxtime1, A.SumOfti_auxtime2, A.SumOfti_auxtime3, A.SumOfti_auxtime4, 
                      A.SumOfti_auxtime5, A.SumOfti_auxtime6, A.SumOfti_auxtime7, A.SumOfti_auxtime8, A.SumOfti_auxtime9, U.Sup
FROM         tblAvayaDaily AS A RIGHT OUTER JOIN
                      tblUsers AS U ON A.logid = U.[Avaya ID]
WHERE     (A.split = 1651) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR
                      (A.split = 1655) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR
                      (A.split = 1653) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 12:39:42

may be this


SELECT     U.Name, A.row_date, A.split, A.SumOfti_stafftime, A.AHT, A.AvgACW, A.AvgACD, A.AvailTime, A.SplitSkill_Incalls, A.SplitSkill_Outcalls, A.SumOfacdtime, 
                      A.SumOfti_othertime, A.SumOfacwtime, A.SumOfti_auxtime0, A.SumOfti_auxtime1, A.SumOfti_auxtime2, A.SumOfti_auxtime3, A.SumOfti_auxtime4, 
                      A.SumOfti_auxtime5, A.SumOfti_auxtime6, A.SumOfti_auxtime7, A.SumOfti_auxtime8, A.SumOfti_auxtime9, U.Sup
FROM         tblAvayaDaily AS A RIGHT OUTER JOIN
                      tblUsers AS U ON A.logid = U.[Avaya ID]
WHERE     A.split IN (1651,1653,1655) 
AND  (',' + @UserName + ',' LIKE  '%,'+ U.Name + ',%' OR @UserName IS NULL)  
AND (A.row_date BETWEEN @rDateStart AND @rDateEnd)

Pass NULL as default value of non mandatory parameters.

homeguard
Starting Member

32 Posts

Posted - 2008-05-22 : 13:46:36

it says "Paramater Error: Select a value for parameter 'User Name'"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 13:47:36

quote:
Originally posted by homeguard

it says "Paramater Error: Select a value for parameter 'User Name'"

Thats because you've not set a default value for UaerName. Set default value to null.

homeguard
Starting Member

32 Posts

Posted - 2008-05-22 : 15:19:51

i had it so i could put muli-values on but i turned it off and put it to allow null values, allow blank value and confirmd that default value is null. it is still prompting me for information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 15:30:02

quote:
Originally posted by homeguard

i had it so i could put muli-values on but i turned it off and put it to allow null values, allow blank value and confirmd that default value is null. it is still prompting me for information.

It wont if you've set default value as null and also checked option Allow Null Value

homeguard
Starting Member

32 Posts

Posted - 2008-05-22 : 15:38:16

pm me your email, I'll send you a screen shot, i have everything how it should be i think.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-25 : 01:53:32