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?
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")