urir10
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
itcouple
just to clarify second approach doesn't add 'None' if there are some items.
while creating the second parameter, in the report designer, please check the CHECK BOX for to accept 'NULL' values
Hi
You cannot allow null values for multi-select.... you get error 'A multi value parameter cannot include null values'
Regards
Emil
You cannot allow null values for multi-select.... you get error 'A multi value parameter cannot include null values'
Regards
Emil
ASKER
I found a different solution but that tip put on me on the right track, thanks
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
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
ASKER
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
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
Thanks!