SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Optional Parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sprotson
Yak Posting Veteran

70 Posts

Posted - 01/11/2013 :  22:08:01  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/12/2013 :  12:57:04  Show Profile  Reply with Quote
where is the part where you use level parameter for filtering in sql?

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

Go to Top of Page

sprotson
Yak Posting Veteran

70 Posts

Posted - 01/12/2013 :  14:19:06  Show Profile  Reply with Quote
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).

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 01/13/2013 :  10:33:29  Show Profile  Reply with Quote
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.
Go to Top of Page

sprotson
Yak Posting Veteran

70 Posts

Posted - 01/13/2013 :  13:01:35  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/13/2013 :  22:55:08  Show Profile  Reply with Quote
ok..glad that you got it sorted out

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000