Mixing
How can I write a WHERE clause to cater for optional paramerers on an SSRS report?
I am trying to get a basic SSRS report to be filterable by any optional parameters.
The 2 parameters to filter should be either between 2 dates or by a SalesID. I can get each parameter to filter individually but when I add them together in a statement and try to run the report it says "parameter cannot be blank".The advices I read online says me to check the "allow blank values" in the parameter properties but this is not available for Date/Time.
I read them online and it asks me to check the "allow blank values" in the parameter properties but this is not available for Date/Time. I've also tried adding numerous variations of the WHERE clause using "OR IS NULL" but that doesn't work either.I also read that I might need to add an =IIF clause somewhere but I have never done this before.
Select SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE (SalesDate BETWEEN @StartDate AND @EndDate AND SalesID IS NULL) OR
(SalesID = @SalesID AND @StartDate IS NULL and @EndDate IS NULL)
The above code runs if I select a Start Date and End Date but it only pulls through the SalesDate data and the other fields are blank.
I want the report to be filterable by either a Start and End Date or SalesID, not both. At present the above returns an error that-StartDate cannot be blank. As I mentioned above, I cannot check the "allow blank values". Thanks in advance.
sql-server datetime parameters where-clause ssrs-2016 edited Jan 2 at 17:09
Rob
37.6k21103130
asked Jan 2 at 8:35
JimboJimbo
328
add a comment |
I am trying to get a basic SSRS report to be filterable by any optional parameters.
The 2 parameters to filter should be either between 2 dates or by a SalesID. I can get each parameter to filter individually but when I add them together in a statement and try to run the report it says "parameter cannot be blank".The advices I read online says me to check the "allow blank values" in the parameter properties but this is not available for Date/Time.
I read them online and it asks me to check the "allow blank values" in the parameter properties but this is not available for Date/Time. I've also tried adding numerous variations of the WHERE clause using "OR IS NULL" but that doesn't work either.I also read that I might need to add an =IIF clause somewhere but I have never done this before.
Select SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE (SalesDate BETWEEN @StartDate AND @EndDate AND SalesID IS NULL) OR
(SalesID = @SalesID AND @StartDate IS NULL and @EndDate IS NULL)
The above code runs if I select a Start Date and End Date but it only pulls through the SalesDate data and the other fields are blank.
I want the report to be filterable by either a Start and End Date or SalesID, not both. At present the above returns an error that-StartDate cannot be blank. As I mentioned above, I cannot check the "allow blank values". Thanks in advance.
sql-server datetime parameters where-clause ssrs-2016 edited Jan 2 at 17:09
Rob
37.6k21103130
asked Jan 2 at 8:35
JimboJimbo
328
You can change the datatype to string and add a validation (or default value) to check when empty. This has side effects though. Please check this thread social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– EzLo
Jan 2 at 8:56
add a comment |
I am trying to get a basic SSRS report to be filterable by any optional parameters.
The 2 parameters to filter should be either between 2 dates or by a SalesID. I can get each parameter to filter individually but when I add them together in a statement and try to run the report it says "parameter cannot be blank".The advices I read online says me to check the "allow blank values" in the parameter properties but this is not available for Date/Time.
I read them online and it asks me to check the "allow blank values" in the parameter properties but this is not available for Date/Time. I've also tried adding numerous variations of the WHERE clause using "OR IS NULL" but that doesn't work either.I also read that I might need to add an =IIF clause somewhere but I have never done this before.
Select SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE (SalesDate BETWEEN @StartDate AND @EndDate AND SalesID IS NULL) OR
(SalesID = @SalesID AND @StartDate IS NULL and @EndDate IS NULL)
The above code runs if I select a Start Date and End Date but it only pulls through the SalesDate data and the other fields are blank.
I want the report to be filterable by either a Start and End Date or SalesID, not both. At present the above returns an error that-StartDate cannot be blank. As I mentioned above, I cannot check the "allow blank values". Thanks in advance.
sql-server datetime parameters where-clause ssrs-2016 edited Jan 2 at 17:09
Rob
37.6k21103130
asked Jan 2 at 8:35
JimboJimbo
328
I am trying to get a basic SSRS report to be filterable by any optional parameters.
The 2 parameters to filter should be either between 2 dates or by a SalesID. I can get each parameter to filter individually but when I add them together in a statement and try to run the report it says "parameter cannot be blank".The advices I read online says me to check the "allow blank values" in the parameter properties but this is not available for Date/Time.
I read them online and it asks me to check the "allow blank values" in the parameter properties but this is not available for Date/Time. I've also tried adding numerous variations of the WHERE clause using "OR IS NULL" but that doesn't work either.I also read that I might need to add an =IIF clause somewhere but I have never done this before.
Select SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE (SalesDate BETWEEN @StartDate AND @EndDate AND SalesID IS NULL) OR
(SalesID = @SalesID AND @StartDate IS NULL and @EndDate IS NULL)
The above code runs if I select a Start Date and End Date but it only pulls through the SalesDate data and the other fields are blank.
I want the report to be filterable by either a Start and End Date or SalesID, not both. At present the above returns an error that-StartDate cannot be blank. As I mentioned above, I cannot check the "allow blank values". Thanks in advance.
sql-server datetime parameters where-clause ssrs-2016 sql-server datetime parameters where-clause ssrs-2016 edited Jan 2 at 17:09
Rob
37.6k21103130
asked Jan 2 at 8:35
JimboJimbo
328
edited Jan 2 at 17:09
Rob
37.6k21103130
asked Jan 2 at 8:35
JimboJimbo
328
edited Jan 2 at 17:09
Rob
37.6k21103130
edited Jan 2 at 17:09
Rob
37.6k21103130
edited Jan 2 at 17:09
Rob
37.6k21103130
37.6k21103130
asked Jan 2 at 8:35
JimboJimbo
328
asked Jan 2 at 8:35
JimboJimbo
328
asked Jan 2 at 8:35
JimboJimbo
328
328
You can change the datatype to string and add a validation (or default value) to check when empty. This has side effects though. Please check this thread social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– EzLo
Jan 2 at 8:56
add a comment |
You can change the datatype to string and add a validation (or default value) to check when empty. This has side effects though. Please check this thread social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– EzLo
Jan 2 at 8:56
You can change the datatype to string and add a validation (or default value) to check when empty. This has side effects though. Please check this thread social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– EzLo
Jan 2 at 8:56
You can change the datatype to string and add a validation (or default value) to check when empty. This has side effects though. Please check this thread social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– EzLo
Jan 2 at 8:56
add a comment |
1 Answer
1
active
oldest
votes
Constraining the Report Builder parameter selection to enforce the user specifying either the SalesID or the StartDate and EndDate pair isn't something I'm familiar with in Report Builder / SSRS, however I can point you towards a solution for the SQL query that should cater to your needs.
Start by checking Allow null value for the parameters (this is available for Date/time parameters, unlike Allow blank value):
Once you've done that you can use ISNULL against the parameters to filter the data to the desired subset:
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
Using the SalesId = ISNULL(@SalesId, SalesId) predicate as an example, this tells SQL Server to return rows where the SalesId for the row is either:
- Equal to the @SalesId parameter if it isn't null, or
- Equal to the rows SalesId value if @SalesId IS null
This means that when @SalesId is null, all rows get returned ready for the restrictions (if any) specified by @StartDate and @EndDate to be applied.
Here's the test harness I used, which may be useful to you or others.
CREATE TABLE SalesDb
(
SalesID INT NOT NULL,
SalesDate DATETIME NOT NULL,
SaleValue DECIMAL(10, 2),
)
GO
INSERT
INTO SalesDb
(
SalesID, SalesDate, SaleValue
)
VALUES (15, '2019-01-01', 12),
(16, '2019-01-02', 34),
(16, '2019-01-03', 56),
(16, '2019-01-04', 78)
DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesID INT = NULL
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
answered Jan 2 at 10:23
RobRob
37.6k21103130
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
1
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54003334%2fhow-can-i-write-a-where-clause-to-cater-for-optional-paramerers-on-an-ssrs-repor%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Constraining the Report Builder parameter selection to enforce the user specifying either the SalesID or the StartDate and EndDate pair isn't something I'm familiar with in Report Builder / SSRS, however I can point you towards a solution for the SQL query that should cater to your needs.
Start by checking Allow null value for the parameters (this is available for Date/time parameters, unlike Allow blank value):
Once you've done that you can use ISNULL against the parameters to filter the data to the desired subset:
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
Using the SalesId = ISNULL(@SalesId, SalesId) predicate as an example, this tells SQL Server to return rows where the SalesId for the row is either:
- Equal to the @SalesId parameter if it isn't null, or
- Equal to the rows SalesId value if @SalesId IS null
This means that when @SalesId is null, all rows get returned ready for the restrictions (if any) specified by @StartDate and @EndDate to be applied.
Here's the test harness I used, which may be useful to you or others.
CREATE TABLE SalesDb
(
SalesID INT NOT NULL,
SalesDate DATETIME NOT NULL,
SaleValue DECIMAL(10, 2),
)
GO
INSERT
INTO SalesDb
(
SalesID, SalesDate, SaleValue
)
VALUES (15, '2019-01-01', 12),
(16, '2019-01-02', 34),
(16, '2019-01-03', 56),
(16, '2019-01-04', 78)
DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesID INT = NULL
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
answered Jan 2 at 10:23
RobRob
37.6k21103130
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
1
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
add a comment |
Constraining the Report Builder parameter selection to enforce the user specifying either the SalesID or the StartDate and EndDate pair isn't something I'm familiar with in Report Builder / SSRS, however I can point you towards a solution for the SQL query that should cater to your needs.
Start by checking Allow null value for the parameters (this is available for Date/time parameters, unlike Allow blank value):
Once you've done that you can use ISNULL against the parameters to filter the data to the desired subset:
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
Using the SalesId = ISNULL(@SalesId, SalesId) predicate as an example, this tells SQL Server to return rows where the SalesId for the row is either:
- Equal to the @SalesId parameter if it isn't null, or
- Equal to the rows SalesId value if @SalesId IS null
This means that when @SalesId is null, all rows get returned ready for the restrictions (if any) specified by @StartDate and @EndDate to be applied.
Here's the test harness I used, which may be useful to you or others.
CREATE TABLE SalesDb
(
SalesID INT NOT NULL,
SalesDate DATETIME NOT NULL,
SaleValue DECIMAL(10, 2),
)
GO
INSERT
INTO SalesDb
(
SalesID, SalesDate, SaleValue
)
VALUES (15, '2019-01-01', 12),
(16, '2019-01-02', 34),
(16, '2019-01-03', 56),
(16, '2019-01-04', 78)
DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesID INT = NULL
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
answered Jan 2 at 10:23
RobRob
37.6k21103130
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
1
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
add a comment |
Constraining the Report Builder parameter selection to enforce the user specifying either the SalesID or the StartDate and EndDate pair isn't something I'm familiar with in Report Builder / SSRS, however I can point you towards a solution for the SQL query that should cater to your needs.
Start by checking Allow null value for the parameters (this is available for Date/time parameters, unlike Allow blank value):
Once you've done that you can use ISNULL against the parameters to filter the data to the desired subset:
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
Using the SalesId = ISNULL(@SalesId, SalesId) predicate as an example, this tells SQL Server to return rows where the SalesId for the row is either:
- Equal to the @SalesId parameter if it isn't null, or
- Equal to the rows SalesId value if @SalesId IS null
This means that when @SalesId is null, all rows get returned ready for the restrictions (if any) specified by @StartDate and @EndDate to be applied.
Here's the test harness I used, which may be useful to you or others.
CREATE TABLE SalesDb
(
SalesID INT NOT NULL,
SalesDate DATETIME NOT NULL,
SaleValue DECIMAL(10, 2),
)
GO
INSERT
INTO SalesDb
(
SalesID, SalesDate, SaleValue
)
VALUES (15, '2019-01-01', 12),
(16, '2019-01-02', 34),
(16, '2019-01-03', 56),
(16, '2019-01-04', 78)
DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesID INT = NULL
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
answered Jan 2 at 10:23
RobRob
37.6k21103130
Constraining the Report Builder parameter selection to enforce the user specifying either the SalesID or the StartDate and EndDate pair isn't something I'm familiar with in Report Builder / SSRS, however I can point you towards a solution for the SQL query that should cater to your needs.
Start by checking Allow null value for the parameters (this is available for Date/time parameters, unlike Allow blank value):
Once you've done that you can use ISNULL against the parameters to filter the data to the desired subset:
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
Using the SalesId = ISNULL(@SalesId, SalesId) predicate as an example, this tells SQL Server to return rows where the SalesId for the row is either:
- Equal to the @SalesId parameter if it isn't null, or
- Equal to the rows SalesId value if @SalesId IS null
This means that when @SalesId is null, all rows get returned ready for the restrictions (if any) specified by @StartDate and @EndDate to be applied.
Here's the test harness I used, which may be useful to you or others.
CREATE TABLE SalesDb
(
SalesID INT NOT NULL,
SalesDate DATETIME NOT NULL,
SaleValue DECIMAL(10, 2),
)
GO
INSERT
INTO SalesDb
(
SalesID, SalesDate, SaleValue
)
VALUES (15, '2019-01-01', 12),
(16, '2019-01-02', 34),
(16, '2019-01-03', 56),
(16, '2019-01-04', 78)
DECLARE @StartDate DATETIME = '2019-01-03', @EndDate DATETIME = '2019-01-04', @SalesID INT = NULL
SELECT SalesID, SalesDate, SaleValue
FROM SalesDb
WHERE SalesId = ISNULL(@SalesId, SalesId)
AND SalesDate BETWEEN ISNULL(@StartDate, SalesDate) AND ISNULL(@EndDate, SalesDate)
answered Jan 2 at 10:23
RobRob
37.6k21103130
answered Jan 2 at 10:23
RobRob
37.6k21103130
answered Jan 2 at 10:23
RobRob
37.6k21103130
answered Jan 2 at 10:23
RobRob
37.6k21103130
37.6k21103130
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
1
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
add a comment |
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
1
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
I will give this a try and feedback soon, thanks guys.
– Jimbo
Jan 2 at 10:40
1
1
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
This worked perfectly, thanks so much.
– Jimbo
Jan 3 at 11:47
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54003334%2fhow-can-i-write-a-where-clause-to-cater-for-optional-paramerers-on-an-ssrs-repor%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You can change the datatype to string and add a validation (or default value) to check when empty. This has side effects though. Please check this thread social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– EzLo
Jan 2 at 8:56