donnie91910
asked on
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')))
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')))
Are you using "IN" rather than "=" because you're permitting multiple parameter input?
ASKER
yes
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?
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?
ASKER
the OrderId is supplied by the report user, not by a dropdown list.
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.
"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)ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
unfortunately I am not able to create SP's on our DB server and have to create DataSet queries specific to the report.
ASKER
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')))
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')))
Thanks for the split. Good luck with your project. -Jim
{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.