• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2828
  • Last Modified:

SSRS Null Value parameters

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
donnie91910
Asked:
donnie91910
  • 5
  • 4
  • 3
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
DultonCommented:
Are you using "IN" rather than "=" because you're permitting multiple parameter input?
0
 
donnie91910Author Commented:
yes
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DultonCommented:
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
 
donnie91910Author Commented:
the OrderId is supplied by the report user, not by a dropdown list.
0
 
DultonCommented:
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
 
donnie91910Author Commented:
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
 
DultonCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>"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
 
donnie91910Author Commented:
unfortunately I am not able to create SP's on our DB server and have to create DataSet queries specific to the report.
0
 
donnie91910Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now