SQL Query result and SSRS result dont match
I have created a SQL that can pass in multiple parameters and more than one condition.
Example
SELECT *
FROM movement
WHERE Arrivalid IN (@ArrivalID) OR DepartureID IN (@DepartureID)
With this script, SELECT * FROM movement WHERE Arrivalid IN ('A_1234') OR DepartureID IN (''), i am able to pull out record from SSMS.
However, when I run it in SSRS, SSRS return me empty record.
Btw, the query is created as Dataset in SSRS.
Is it a must to pass in 2 parameters? But I have already set "allow blank value ("")".
Report Parameter Properties1: https://i.stack.imgur.com/9AJwO.png
There is no filter created on this simple table format.
SSRS Report
Does anyone have any idea what has gone wrong with my SSRS setup?
To add on:
In Dataset properties, i have added "Spilt" for multiple parameter.
Expression for parameter
Setting for Default Values
Parameters - Default Value
sql
sql-server ssrs-2008 asked Nov 27 '18 at 7:36
VYEVYE
11
add a comment |
I have created a SQL that can pass in multiple parameters and more than one condition.
Example
SELECT *
FROM movement
WHERE Arrivalid IN (@ArrivalID) OR DepartureID IN (@DepartureID)
With this script, SELECT * FROM movement WHERE Arrivalid IN ('A_1234') OR DepartureID IN (''), i am able to pull out record from SSMS.
However, when I run it in SSRS, SSRS return me empty record.
Btw, the query is created as Dataset in SSRS.
Is it a must to pass in 2 parameters? But I have already set "allow blank value ("")".
Report Parameter Properties1: https://i.stack.imgur.com/9AJwO.png
There is no filter created on this simple table format.
SSRS Report
Does anyone have any idea what has gone wrong with my SSRS setup?
To add on:
In Dataset properties, i have added "Spilt" for multiple parameter.
Expression for parameter
Setting for Default Values
Parameters - Default Value
sql
sql-server ssrs-2008 asked Nov 27 '18 at 7:36
VYEVYE
11
add a comment |
I have created a SQL that can pass in multiple parameters and more than one condition.
Example
SELECT *
FROM movement
WHERE Arrivalid IN (@ArrivalID) OR DepartureID IN (@DepartureID)
With this script, SELECT * FROM movement WHERE Arrivalid IN ('A_1234') OR DepartureID IN (''), i am able to pull out record from SSMS.
However, when I run it in SSRS, SSRS return me empty record.
Btw, the query is created as Dataset in SSRS.
Is it a must to pass in 2 parameters? But I have already set "allow blank value ("")".
Report Parameter Properties1: https://i.stack.imgur.com/9AJwO.png
There is no filter created on this simple table format.
SSRS Report
Does anyone have any idea what has gone wrong with my SSRS setup?
To add on:
In Dataset properties, i have added "Spilt" for multiple parameter.
Expression for parameter
Setting for Default Values
Parameters - Default Value
sql
sql-server ssrs-2008 asked Nov 27 '18 at 7:36
VYEVYE
11
I have created a SQL that can pass in multiple parameters and more than one condition.
Example
SELECT *
FROM movement
WHERE Arrivalid IN (@ArrivalID) OR DepartureID IN (@DepartureID)
With this script, SELECT * FROM movement WHERE Arrivalid IN ('A_1234') OR DepartureID IN (''), i am able to pull out record from SSMS.
However, when I run it in SSRS, SSRS return me empty record.
Btw, the query is created as Dataset in SSRS.
Is it a must to pass in 2 parameters? But I have already set "allow blank value ("")".
Report Parameter Properties1: https://i.stack.imgur.com/9AJwO.png
There is no filter created on this simple table format.
SSRS Report
Does anyone have any idea what has gone wrong with my SSRS setup?
To add on:
In Dataset properties, i have added "Spilt" for multiple parameter.
Expression for parameter
Setting for Default Values
Parameters - Default Value
sql
sql-server ssrs-2008 sql
sql-server ssrs-2008 asked Nov 27 '18 at 7:36
VYEVYE
11
asked Nov 27 '18 at 7:36
VYEVYE
11
edited Nov 27 '18 at 8:25
VYE
asked Nov 27 '18 at 7:36
VYEVYE
11
asked Nov 27 '18 at 7:36
VYEVYE
11
asked Nov 27 '18 at 7:36
VYEVYE
11
11
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Assuming you want to be able to enter either arrival or departure IDs then you should be able to just do this...
SELECT *
FROM movement
WHERE (Arrivalid IN (@ArrivalID) OR @ArrivalID IS NULL)
OR (DepartureID IN (@DepartureID) OR @DepartureID IS NULL)
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
add a comment |
Try setting default value for that parameter:
answered Nov 27 '18 at 7:46
KatusicKatusic
842
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
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%2f53494801%2fsql-query-result-and-ssrs-result-dont-match%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assuming you want to be able to enter either arrival or departure IDs then you should be able to just do this...
SELECT *
FROM movement
WHERE (Arrivalid IN (@ArrivalID) OR @ArrivalID IS NULL)
OR (DepartureID IN (@DepartureID) OR @DepartureID IS NULL)
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
add a comment |
Assuming you want to be able to enter either arrival or departure IDs then you should be able to just do this...
SELECT *
FROM movement
WHERE (Arrivalid IN (@ArrivalID) OR @ArrivalID IS NULL)
OR (DepartureID IN (@DepartureID) OR @DepartureID IS NULL)
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
add a comment |
Assuming you want to be able to enter either arrival or departure IDs then you should be able to just do this...
SELECT *
FROM movement
WHERE (Arrivalid IN (@ArrivalID) OR @ArrivalID IS NULL)
OR (DepartureID IN (@DepartureID) OR @DepartureID IS NULL)
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
Assuming you want to be able to enter either arrival or departure IDs then you should be able to just do this...
SELECT *
FROM movement
WHERE (Arrivalid IN (@ArrivalID) OR @ArrivalID IS NULL)
OR (DepartureID IN (@DepartureID) OR @DepartureID IS NULL)
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
answered Nov 27 '18 at 23:33
Alan SchofieldAlan Schofield
6,28511020
6,28511020
add a comment |
add a comment |
Try setting default value for that parameter:
answered Nov 27 '18 at 7:46
KatusicKatusic
842
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
add a comment |
Try setting default value for that parameter:
answered Nov 27 '18 at 7:46
KatusicKatusic
842
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
add a comment |
Try setting default value for that parameter:
answered Nov 27 '18 at 7:46
KatusicKatusic
842
Try setting default value for that parameter:
answered Nov 27 '18 at 7:46
KatusicKatusic
842
answered Nov 27 '18 at 7:46
KatusicKatusic
842
answered Nov 27 '18 at 7:46
KatusicKatusic
842
answered Nov 27 '18 at 7:46
KatusicKatusic
842
842
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
add a comment |
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
i do have some list of default value for Arrival and Departure. I do not have issue, if i have both parameters entered. However, it there is only 1, the record will be empty in SSRS.
– VYE
Nov 27 '18 at 8:10
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
Can you try pass NULL value, and then in dataset make line: SET @Parameter = isnull(@Parameter,'') before select? That will set all null inputs to blank text.
– Katusic
Nov 27 '18 at 8:44
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
your issue should be solved via @AlanSchofield's suggestion above. Its a query thing and not ssrs. Try running your stored proc or query by declaring those exact same params and running the query. Rem ssrs just render's what sql server throws at it.
– junketsu
Nov 28 '18 at 20:55
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%2f53494801%2fsql-query-result-and-ssrs-result-dont-match%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
