Conditional Formatting/Colors in Sparklines in SSRS
I've looked at several websites that all suggest going into Chart Data > Values, and then right-clicking to change the formula of the Fill Color attribute to a formula that defines how I want the color scheme to look, but I tried that already, and I don't get back what I anticipated.
My data (e.g., Fields!DailySalesGoalPassFail.Value) is aggregated in the Sparkline by Avg, and categorized by EntryDate. If a single location is selected, each of the Pass/Fail values will be either "1" or "0" for any given EntryDate, but if multiple locations are selected, I want the Pass/Fail to be able to return something in-between (for instance, ".5" if 1 of 2 stores in the group pass).
Furthermore, I want to be able to color the nodes of the Sparkline green if it's =1, red if =0, and yellow if it's somewhere between, but every time I try to write a Switch statement to handle that and the starting value is 0, then the whole line is red, even if the value later climbs above 0.
=Switch(
Fields!DailySalesGoalPassFail.Value = 1, "Green",
Fields!DailySalesGoalPassFail.Value = 0, "Red",
true, "Yellow"
)
What am I doing wrong?
reporting-services average ssrs-2012 sparklines
asked Nov 20 at 21:31
Don Ford
288
add a comment |
I've looked at several websites that all suggest going into Chart Data > Values, and then right-clicking to change the formula of the Fill Color attribute to a formula that defines how I want the color scheme to look, but I tried that already, and I don't get back what I anticipated.
My data (e.g., Fields!DailySalesGoalPassFail.Value) is aggregated in the Sparkline by Avg, and categorized by EntryDate. If a single location is selected, each of the Pass/Fail values will be either "1" or "0" for any given EntryDate, but if multiple locations are selected, I want the Pass/Fail to be able to return something in-between (for instance, ".5" if 1 of 2 stores in the group pass).
Furthermore, I want to be able to color the nodes of the Sparkline green if it's =1, red if =0, and yellow if it's somewhere between, but every time I try to write a Switch statement to handle that and the starting value is 0, then the whole line is red, even if the value later climbs above 0.
=Switch(
Fields!DailySalesGoalPassFail.Value = 1, "Green",
Fields!DailySalesGoalPassFail.Value = 0, "Red",
true, "Yellow"
)
What am I doing wrong?
reporting-services average ssrs-2012 sparklines
asked Nov 20 at 21:31
Don Ford
288
add a comment |
I've looked at several websites that all suggest going into Chart Data > Values, and then right-clicking to change the formula of the Fill Color attribute to a formula that defines how I want the color scheme to look, but I tried that already, and I don't get back what I anticipated.
My data (e.g., Fields!DailySalesGoalPassFail.Value) is aggregated in the Sparkline by Avg, and categorized by EntryDate. If a single location is selected, each of the Pass/Fail values will be either "1" or "0" for any given EntryDate, but if multiple locations are selected, I want the Pass/Fail to be able to return something in-between (for instance, ".5" if 1 of 2 stores in the group pass).
Furthermore, I want to be able to color the nodes of the Sparkline green if it's =1, red if =0, and yellow if it's somewhere between, but every time I try to write a Switch statement to handle that and the starting value is 0, then the whole line is red, even if the value later climbs above 0.
=Switch(
Fields!DailySalesGoalPassFail.Value = 1, "Green",
Fields!DailySalesGoalPassFail.Value = 0, "Red",
true, "Yellow"
)
What am I doing wrong?
reporting-services average ssrs-2012 sparklines
asked Nov 20 at 21:31
Don Ford
288
I've looked at several websites that all suggest going into Chart Data > Values, and then right-clicking to change the formula of the Fill Color attribute to a formula that defines how I want the color scheme to look, but I tried that already, and I don't get back what I anticipated.
My data (e.g., Fields!DailySalesGoalPassFail.Value) is aggregated in the Sparkline by Avg, and categorized by EntryDate. If a single location is selected, each of the Pass/Fail values will be either "1" or "0" for any given EntryDate, but if multiple locations are selected, I want the Pass/Fail to be able to return something in-between (for instance, ".5" if 1 of 2 stores in the group pass).
Furthermore, I want to be able to color the nodes of the Sparkline green if it's =1, red if =0, and yellow if it's somewhere between, but every time I try to write a Switch statement to handle that and the starting value is 0, then the whole line is red, even if the value later climbs above 0.
=Switch(
Fields!DailySalesGoalPassFail.Value = 1, "Green",
Fields!DailySalesGoalPassFail.Value = 0, "Red",
true, "Yellow"
)
What am I doing wrong?
reporting-services average ssrs-2012 sparklines
reporting-services average ssrs-2012 sparklines
asked Nov 20 at 21:31
Don Ford
288
asked Nov 20 at 21:31
Don Ford
288
asked Nov 20 at 21:31
Don Ford
288
asked Nov 20 at 21:31
Don Ford
288
asked Nov 20 at 21:31
Don Ford
288
288
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I think your Switch() depends on the wrong Field. Because your Fields!DailySalesGoalPassFail.Value is as you said aggregated by Avg and most likely a number. If your Fields!DailySalesGoalPassFail.Value has for example values from 1 to 100 this coloring will work at the fill expresssion:
=Switch(Fields!DailySalesGoalPassFail.Value < 50, "Green",
Fields!DailySalesGoalPassFail.Value > 50, "Red")
I think what you really want is to depend your sparkline on the pass/fail value. In this case you can write the following at the fill expression (I am not sure where your pass/fail value comes from. I am guessing its a parameter now):
=Switch(Parameters!PassFail.Value = 1, "Green",
Parameters!PassFail.Value = 0, "Red",
Parameters!PassFail.Value < 1 And Parameters!PassFail.Value > 0, "Yellow")
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
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%2f53401862%2fconditional-formatting-colors-in-sparklines-in-ssrs%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
I think your Switch() depends on the wrong Field. Because your Fields!DailySalesGoalPassFail.Value is as you said aggregated by Avg and most likely a number. If your Fields!DailySalesGoalPassFail.Value has for example values from 1 to 100 this coloring will work at the fill expresssion:
=Switch(Fields!DailySalesGoalPassFail.Value < 50, "Green",
Fields!DailySalesGoalPassFail.Value > 50, "Red")
I think what you really want is to depend your sparkline on the pass/fail value. In this case you can write the following at the fill expression (I am not sure where your pass/fail value comes from. I am guessing its a parameter now):
=Switch(Parameters!PassFail.Value = 1, "Green",
Parameters!PassFail.Value = 0, "Red",
Parameters!PassFail.Value < 1 And Parameters!PassFail.Value > 0, "Yellow")
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
add a comment |
I think your Switch() depends on the wrong Field. Because your Fields!DailySalesGoalPassFail.Value is as you said aggregated by Avg and most likely a number. If your Fields!DailySalesGoalPassFail.Value has for example values from 1 to 100 this coloring will work at the fill expresssion:
=Switch(Fields!DailySalesGoalPassFail.Value < 50, "Green",
Fields!DailySalesGoalPassFail.Value > 50, "Red")
I think what you really want is to depend your sparkline on the pass/fail value. In this case you can write the following at the fill expression (I am not sure where your pass/fail value comes from. I am guessing its a parameter now):
=Switch(Parameters!PassFail.Value = 1, "Green",
Parameters!PassFail.Value = 0, "Red",
Parameters!PassFail.Value < 1 And Parameters!PassFail.Value > 0, "Yellow")
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
add a comment |
I think your Switch() depends on the wrong Field. Because your Fields!DailySalesGoalPassFail.Value is as you said aggregated by Avg and most likely a number. If your Fields!DailySalesGoalPassFail.Value has for example values from 1 to 100 this coloring will work at the fill expresssion:
=Switch(Fields!DailySalesGoalPassFail.Value < 50, "Green",
Fields!DailySalesGoalPassFail.Value > 50, "Red")
I think what you really want is to depend your sparkline on the pass/fail value. In this case you can write the following at the fill expression (I am not sure where your pass/fail value comes from. I am guessing its a parameter now):
=Switch(Parameters!PassFail.Value = 1, "Green",
Parameters!PassFail.Value = 0, "Red",
Parameters!PassFail.Value < 1 And Parameters!PassFail.Value > 0, "Yellow")
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
I think your Switch() depends on the wrong Field. Because your Fields!DailySalesGoalPassFail.Value is as you said aggregated by Avg and most likely a number. If your Fields!DailySalesGoalPassFail.Value has for example values from 1 to 100 this coloring will work at the fill expresssion:
=Switch(Fields!DailySalesGoalPassFail.Value < 50, "Green",
Fields!DailySalesGoalPassFail.Value > 50, "Red")
I think what you really want is to depend your sparkline on the pass/fail value. In this case you can write the following at the fill expression (I am not sure where your pass/fail value comes from. I am guessing its a parameter now):
=Switch(Parameters!PassFail.Value = 1, "Green",
Parameters!PassFail.Value = 0, "Red",
Parameters!PassFail.Value < 1 And Parameters!PassFail.Value > 0, "Yellow")
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
answered Nov 21 at 5:57
Strawberryshrub
1,1141216
1,1141216
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
add a comment |
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
I switched it to using the Avg aggregate, rather than the Field.Value, and that did it. Thanks for giving me a second pair of eyes.
– Don Ford
Nov 21 at 14:46
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%2f53401862%2fconditional-formatting-colors-in-sparklines-in-ssrs%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
