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 Null Value parameters

Posted on 2014-04-21
12
2,552 Views
Last Modified: 2014-06-10
In my SSRS report I have 4 parameters that the user can choose from:  CD, Condition, OrderID and Begining and End Date (ProjectDate).  The user will always choose CD and Condition, but only sometimes will they choose OrderID and Beginning and End Date as part of the parameter criteria.  Is there a way to make my DataSet query recognize that the user has not put anything in for either one of the parameters of OrderID or Beginning and End Date?


Here is the query in my dataset:

SELECT CD,CONDITION, OrderID, ProjectDate
FROM ORDER
Where ((Order.CD       IN (:CD)  
AND Order.CONDITION IN (:CC))
OR (Order.ORDERID         IN (:WOID)
OR (ORDER.ProjectDate             >= to_date(:BEGDATE, 'MM/DD/YYYY'))
AND ORDER.ProjectDate             <= to_date(:ENDDATE, 'MM/DD/YYYY')))
0
Comment
Question by:donnie91910
  • 5
  • 4
  • 3
12 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40013596
<wild guess>

{the above query}
AND (ORDER.ProjectDate >= @begdate OR @begdate IS NULL)
AND (ORDER.ProjectDate <= @enddate OR @enddate IS NULL)

This way, T-SQL will not process the date expression if the parameter value is NULL.
0
 
LVL 6

Expert Comment

by:Dulton
ID: 40014668
Are you using "IN" rather than "=" because you're permitting multiple parameter input?
0
 

Author Comment

by:donnie91910
ID: 40014934
yes
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Expert Comment

by:Dulton
ID: 40014964
That makes it trickier for your orderId..... Out of the box, SSRS won't permit you to allow multiple values and null value on the same parameter.

Usually these "multi-select" parameters are fed by they're own dataset, as a drop-down box which makes it somewhat easy to trick SSRS into lettings us do what we want anyways. If not, then you have to coordinate with your end users what placeholder value to input when they want the results of "Null".

Is the OrderId parameter supplied by the report user, or are they in a dropdown list fed by its own dataset?
0
 

Author Comment

by:donnie91910
ID: 40015368
the OrderId is supplied by the report user, not by a dropdown list.
0
 
LVL 6

Expert Comment

by:Dulton
ID: 40015403
Ok, realizing SSRS won't permit null on a multi-value parameter, then you need to alter your user prompt to be something like this:

"Order Id: (Enter * for all)"

This should prompt users to enter an asterisk (or 'ALL' or whatever non-null value/string you want to user)

Then back on the SQL side, you need to consider this value.

For this, I will use the asterisk as the all wildcard passed in by the user.

SELECT CD,CONDITION, OrderID, ProjectDate
FROM ORDER
Where Order.CD  IN (@CD)  
AND Order.CONDITION IN (@CC)
AND (Order.ORDERID   IN (@WOID) OR '*' IN (@WOID)  
/*   
       For '*' occurring on any line, with any other parameters.... but to Only show all when
       '*' is the only parameter input, replace with @WOID = '*' 
*/
AND (ORDER.ProjectDate  >= @BEGDATE OR @BEGDATE IS NULL)
AND (ORDER.ProjectDate <= @ENDDATE OR @ENDDATE IS NULL)

Open in new window

0
 

Author Comment

by:donnie91910
ID: 40015467
For the OrderID, the user will have the option to leave the parameter field blank (NULL) or the user will have the option to enter in one or multiple OrderID's typed in by hand (11553;33445;88791).  

So in this case the line for the @WOID would be:

AND (Order.ORDERID   IN (@WOID) OR IN (@WOID) IS NULL)

Please correct if this is wrong.
0
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 250 total points
ID: 40015512
It is wrong.

SSRS won't permit you to have a multi-input nullable field. That's why I proposed an alternate solution using an asterisk or some other dummy value. I would think that if you've tried to set both of these properties in the report parameter properties, you would have already seen the message that pops up. The easiest way around it is what I've proposed.    

Here is Microsoft's explanation of Multiple Values and null:
http://msdn.microsoft.com/en-us/library/dd220464.aspx

Scroll down to the "Report Parameters" section, and look for the Allow Multiple Values description.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40015537
>"Order Id: (Enter * for all)"
>That's why I proposed an alternate solution using an asterisk or some other dummy value.
Building on Dulton's comments, I've been in this situation before, and pulled it off by...

(1)  Build a SP that will be the list for this combo box, with an < ALL > row up top, that goes something like this:

SELECT name, id
FROM (
   SELECT name, id, 2 as sort_order
   FROM Your_table
   UNION ALL
   SELECT '<ALL>', -1 as id, 1 as sort_order) a
ORDER BY sort_order, id

Open in new window

(2)  Attached the above list as an SSRS dataset, then attach the data set to the parameter

(3)  Modify your main SP to handle having a -1 passed (a dummy value I just made up.  Change to 2999-12-31, 'banana', whatever) to mean 'All', so that it behaves as if you wanted 'all'.   Something like..
{the above query}
AND (ORDER.ProjectDate >= @begdate OR @begdate = -1)
AND (ORDER.ProjectDate <= @enddate OR @enddate = -1) 

Open in new window

0
 

Author Comment

by:donnie91910
ID: 40015875
unfortunately I am not able to create SP's on our DB server and have to create DataSet queries specific to the report.
0
 

Author Comment

by:donnie91910
ID: 40020830
Since I am not able to create a stored procedure on the DB server I think that I will need to Declare variables in the dataset.  Please let me know if this looks correct.

DECLARE :CD NUMBER(10)
DECLARE :CC NVARCHAR2(50)
DECLARE :WOID NVARCHAR2(60 CHAR) NULL
DECLARE :BEGDATE DATETIME 'MM/DD/YYYY' NULL
DECLARE :ENDDATE DATETIME 'MM/DD/YYYY' NULL


SELECT CD,CONDITION, OrderID, ProjectDate
FROM ORDER

Where ((Order.CD       IN (:CD)  
AND Order.CONDITION IN (:CC))
OR (Order.ORDERID         IN (:WOID)
OR (ORDER.ProjectDate             >= to_date(:BEGDATE, 'MM/DD/YYYY'))
AND ORDER.ProjectDate             <= to_date(:ENDDATE, 'MM/DD/YYYY')))
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40124552
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 36
Stored Proc - Rewrite 42 59
Requesting help with creating an SQL query with 2 tables 6 27
SQL - Simple Pivot query 8 15
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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