Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SSRS Reporting Services Problem with Parameters

Posted on 2010-08-18
8
613 Views
Last Modified: 2012-08-13
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?

0
Comment
Question by:urir10
  • 5
  • 2
8 Comments
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 33469217
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
 
LVL 10

Expert Comment

by:itcouple
ID: 33469238
just to clarify second approach doesn't add 'None' if there are some items.
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 33473070
while creating the second parameter, in the report designer, please check the CHECK BOX for to accept 'NULL' values
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 10

Expert Comment

by:itcouple
ID: 33474416
Hi

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

Regards
Emil
0
 
LVL 7

Author Closing Comment

by:urir10
ID: 33501377
I found a different solution but that tip put on me on the right track, thanks
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33501499
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
 
LVL 7

Author Comment

by:urir10
ID: 33501548
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
 
LVL 10

Expert Comment

by:itcouple
ID: 33501613
Thanks!
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 26
Acces SQL Insert Problem 6 31
SQL Availablity Groups List 2 7
shrink table after huge delete 2 13
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question