Optional 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 2008 Forums

Analysis Server and Reporting Services (2008)

Optional Parameters

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2013-01-11 : 22:08:01

I am nuilding a report that needs to have optional parameters (20 of them).

Skill 1, Skill 2 to Skill 10
Level 1, Level 2 to Level 10

These parameters are passed to 10 parameters in a stored procedure

so @SkillExp1 will equal Skill 1 & "=" & Level 1

In the stored procedure the parameters are set with a default value as follows

@skillexp1 varchar (2000) = '',

The where clause in the stored procedure contains case statements such as

case when @skillexp1 = '' then 1 else .............

The stored procesure runs correctly and produces results outwith the report and will run even when the parameters are blank.

In the report the Skill parameters have available values from a dataset as follows:

SELECT DISTINCT s.name as Skill
FROM SkillLevel AS sl
UNION ALL
SELECT '' AS Skill

So the user can select a skill or use the default of blank.

The level parameters are free text, so have no available values, but have a default value of blank (ie a space).

All parameters allow blank values.

I can get the report to run, but it does not return any values, any thoughts on what I might be doing wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 12:57:04

where is the part where you use level parameter for filtering in sql?

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

sprotson
Yak Posting Veteran

75 Posts

Posted - 2013-01-12 : 14:19:06

Sorry, not sure what you mean.

The level parameter is only in SSRS and is free text and is combined with the skill parameter in the parameter tab of the dataset.

so @SkillExp1 will equal SkillParameter & "=" & LevelParameter

The @SkillExp1 parameter is in the stored procedure.

I know the stored procedure works, for some reason when the report parameters are combined they do not return any results from the stored procedure.

ie if I enter Skill1=1 (ie Skill 1 + Level 1) direct in the stored procedure I get results, but if the Skill parameter is Skill 1 and the level parameter is 1 (combined as above to Skill1=1 ), then no results are returned.

The only alternative I can think of is the default values of teh parameters not being used are different from the default value in the stored procedure of ''.

In the report the Skill parameters have available values from a dataset as follows:

SELECT DISTINCT s.name as Skill
FROM SkillLevel AS sl
UNION ALL
SELECT '' AS Skill

So the user can select a skill or use the default of blank.

The level parameters are free text, so have no available values, but have a default value of blank (ie a space).

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-13 : 10:33:29

As you probably guessed, I suspect that the parameter(s) being sent to the server does not match your expectation of what it should be. I didn't follow the logic you described above; however one possibility is to run SQL profiler on the server and examine the query that it receives from Reporting Services. That may give you a clue as to how your parameters are being interpreted.

sprotson
Yak Posting Veteran

75 Posts

Posted - 2013-01-13 : 13:01:35

I think I have worked it out, such a stupid thing to make a mistake on.

The formula used in the report to generate the result passed to stored procedure parameter was:

=Parameters!Skill1.Value & "=" & Parameters!Level1.Value)

So if no parameters were entered it would return "=" to be passed back to the stored procedure.

I have changed the formula to:

=iif(Parameters!Skill1.Value ="",iif(Parameters!Level1.Value ="","",Parameters!Skill1.Value & "=" & Parameters!Level1.Value))

In that way if either of the report parameters are "", then "" will be passed to the stored procedure. whihc is the parameter default.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-13 : 22:55:08

ok..glad that you got it sorted out

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources