SSRS Reporting Services Problem with Parameters

Hi All

I have an issue that i cannot solve with a report im building.
The report needs to show the number of tickets for each Form and request type, the problem is some Forms have multiple request types some have none. My ask is to create the report so that the user can pick the Form and based on that selection pick a request type, if the form has no request type than it could be left blank and it will show total tickets for that form. Because i need to enable the user select multiple value for each parameter i cannot set the second parameter to accept NULL but when the Form has no Request type than it is NULL and the report wont run.

How can i hide the second parameter when there is nothing to select there?

LVL 7
urir10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

itcoupleCommented:
Hi

You could add artifical item 'None' using UNION

select 0 as value, 'None' as Label, 0 as order
UNION
my proper select statemement (add 1 as order in select and order by ) so None appear always at top.

You might also want to count rows and store value in hidden parameter then when you do union you could do

select 0 as value, 'None' as Label, 0 as order
where 1*@MyParameter <> 0
UNION
your sql

Regards
Emil
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itcoupleCommented:
just to clarify second approach doesn't add 'None' if there are some items.
0
sureshbabukrishCommented:
while creating the second parameter, in the report designer, please check the CHECK BOX for to accept 'NULL' values
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

itcoupleCommented:
Hi

You cannot allow null values for multi-select.... you get error 'A multi value parameter cannot include null values'

Regards
Emil
0
urir10Author Commented:
I found a different solution but that tip put on me on the right track, thanks
0
itcoupleCommented:
Hi

I'm glad you found the solution. If you don't mind please share it with us :) it might be usefull for me to know other ways of doing that (or variations)....

Regards
Emil
0
urir10Author Commented:
sure.

Its abit tricky but ill give it a shot.
First in SSRS for the second parameter i wrote that code to get the possible values for the Drop Down:

SELECT  Distinct  CASE WHEN  ReqType IS NULL THEN 'N/A'
ELSE ReqType END as ReqType
 
 FROM [vw_TFAM_Reporting]
Where FormName IN (@TicketType)

So that give me a N/A option when a ticket type without requests is selected.
I then pass those value to a Stored procedure where i check to see if the selected value was 'N/A' then i dont look for a matching Request or Else if its not N/A then i use the IN operator.


Hope that helps
0
itcoupleCommented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.