SSRS - Extract value from a column from a dataset based on condition from 2 other columns
I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:
Category Month Amount
A Jan 20
A Feb 25
A Mar 10
R Jan 15
R Feb 50
R Mar 55
On the report I need:
Jan Feb Mar
A 20 25 10
R 15 50 55
I have tried placing this expression in each group row column, for example, in the Jan column it would be:
=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)
But I get the result 0
, like false result.
Could you please let me know how to solve this problem.
reporting-services
add a comment |
I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:
Category Month Amount
A Jan 20
A Feb 25
A Mar 10
R Jan 15
R Feb 50
R Mar 55
On the report I need:
Jan Feb Mar
A 20 25 10
R 15 50 55
I have tried placing this expression in each group row column, for example, in the Jan column it would be:
=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)
But I get the result 0
, like false result.
Could you please let me know how to solve this problem.
reporting-services
add a comment |
I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:
Category Month Amount
A Jan 20
A Feb 25
A Mar 10
R Jan 15
R Feb 50
R Mar 55
On the report I need:
Jan Feb Mar
A 20 25 10
R 15 50 55
I have tried placing this expression in each group row column, for example, in the Jan column it would be:
=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)
But I get the result 0
, like false result.
Could you please let me know how to solve this problem.
reporting-services
I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:
Category Month Amount
A Jan 20
A Feb 25
A Mar 10
R Jan 15
R Feb 50
R Mar 55
On the report I need:
Jan Feb Mar
A 20 25 10
R 15 50 55
I have tried placing this expression in each group row column, for example, in the Jan column it would be:
=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)
But I get the result 0
, like false result.
Could you please let me know how to solve this problem.
reporting-services
reporting-services
edited Nov 10 at 23:21
Strawberryshrub
1,1081216
1,1081216
asked Nov 10 at 15:39
user10581977
43
43
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you need it as expression, the correct expression would be:
=Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))
But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value
and into your column group Fields!Month.Value
. And where the row and column group meets you can also drag and drop the Fields!Amount.Value
because the default aggregate function in the matrix is Sum()
. Which is exactly what you need Sum(Fields!Amount.Value))
.
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that yourFields!Month.Value
doesnt work. Is this field aDate/Time
? TryCDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
– Strawberryshrub
Nov 12 at 5:21
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
add a comment |
SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with
=IIF(Fields!Category.Value = "A" ,1, 0)
Another with
=IIF(Fields!Month.Value = "Jan", 1, 0)
Another with
=IIF(1=1,Fields!Amount.Value, 0)
When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.
=sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))
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%2f53240524%2fssrs-extract-value-from-a-column-from-a-dataset-based-on-condition-from-2-othe%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
If you need it as expression, the correct expression would be:
=Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))
But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value
and into your column group Fields!Month.Value
. And where the row and column group meets you can also drag and drop the Fields!Amount.Value
because the default aggregate function in the matrix is Sum()
. Which is exactly what you need Sum(Fields!Amount.Value))
.
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that yourFields!Month.Value
doesnt work. Is this field aDate/Time
? TryCDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
– Strawberryshrub
Nov 12 at 5:21
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
add a comment |
If you need it as expression, the correct expression would be:
=Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))
But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value
and into your column group Fields!Month.Value
. And where the row and column group meets you can also drag and drop the Fields!Amount.Value
because the default aggregate function in the matrix is Sum()
. Which is exactly what you need Sum(Fields!Amount.Value))
.
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that yourFields!Month.Value
doesnt work. Is this field aDate/Time
? TryCDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
– Strawberryshrub
Nov 12 at 5:21
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
add a comment |
If you need it as expression, the correct expression would be:
=Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))
But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value
and into your column group Fields!Month.Value
. And where the row and column group meets you can also drag and drop the Fields!Amount.Value
because the default aggregate function in the matrix is Sum()
. Which is exactly what you need Sum(Fields!Amount.Value))
.
If you need it as expression, the correct expression would be:
=Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))
But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value
and into your column group Fields!Month.Value
. And where the row and column group meets you can also drag and drop the Fields!Amount.Value
because the default aggregate function in the matrix is Sum()
. Which is exactly what you need Sum(Fields!Amount.Value))
.
answered Nov 10 at 15:59
Strawberryshrub
1,1081216
1,1081216
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that yourFields!Month.Value
doesnt work. Is this field aDate/Time
? TryCDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
– Strawberryshrub
Nov 12 at 5:21
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
add a comment |
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that yourFields!Month.Value
doesnt work. Is this field aDate/Time
? TryCDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
– Strawberryshrub
Nov 12 at 5:21
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
– user10581977
Nov 11 at 18:56
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
@Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
– user10581977
Nov 11 at 19:32
Your expression is correct
=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that your Fields!Month.Value
doesnt work. Is this field a Date/Time
? Try CDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.– Strawberryshrub
Nov 12 at 5:21
Your expression is correct
=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)
. The only possibilty is that your Fields!Month.Value
doesnt work. Is this field a Date/Time
? Try CDate(Fields!Month.Value)
and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.– Strawberryshrub
Nov 12 at 5:21
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
– user10581977
Nov 12 at 14:42
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
@Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
– user10581977
Nov 12 at 16:59
add a comment |
SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with
=IIF(Fields!Category.Value = "A" ,1, 0)
Another with
=IIF(Fields!Month.Value = "Jan", 1, 0)
Another with
=IIF(1=1,Fields!Amount.Value, 0)
When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.
=sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))
add a comment |
SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with
=IIF(Fields!Category.Value = "A" ,1, 0)
Another with
=IIF(Fields!Month.Value = "Jan", 1, 0)
Another with
=IIF(1=1,Fields!Amount.Value, 0)
When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.
=sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))
add a comment |
SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with
=IIF(Fields!Category.Value = "A" ,1, 0)
Another with
=IIF(Fields!Month.Value = "Jan", 1, 0)
Another with
=IIF(1=1,Fields!Amount.Value, 0)
When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.
=sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))
SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with
=IIF(Fields!Category.Value = "A" ,1, 0)
Another with
=IIF(Fields!Month.Value = "Jan", 1, 0)
Another with
=IIF(1=1,Fields!Amount.Value, 0)
When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.
=sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))
answered Nov 14 at 11:33
RET
351111
351111
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53240524%2fssrs-extract-value-from-a-column-from-a-dataset-based-on-condition-from-2-othe%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