Solved

SSRS Null Value parameters

Posted on 2014-04-21
12
2,584 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
[X]
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
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

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
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

737 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