Solved

SSRS Null Value parameters

Posted on 2014-04-21
12
2,491 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now