Conditional Formatting on Chart with Benchmark

[sconti11]

I have been trying to create a simple conditional format on a column chart so that those columns that fall under the benchmark goal (which is the 2nd value in the chart, and is a Line type).

Here is the expression I was using:

=iif((Fields!Score.Value < Fields!BenchGoal.Value), "Red", "Black")

But when I run the report all columns return black even though there are several columns that fall under the mark.

Any ideas as to what I am doing wrong???

 

[xlbo]

Are the 2 values returned in the same dataset?
If so, simplest answer is to do the calc there

CASE Score - BenchGoal
WHEN <0 then 'Red'
ELSE 'Black' End as BarColour

Then in the bar background colour property simply set to expression = Fields!BarColour.value



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sconti11]

I appreciate the response. Yes the two values that I am testing are within the same data set. But when I attempt to apply the case statement i get errors with proper syntax.

I may be constructing the CASE incorrectly. Below is what I have...sorry if it is big

SELECT a.FullName, a.ShortName, a.SchoolYear, a.TestAdministrationDate, a.Score, a.Grade, a.Trait_SN, a.Trait_FN, a.ScoreMinimum, a.ScoreMaximum, a.IsBenchmark,
a.FieldColor, a.Student_FN, a.Student_LN, a.Test_Admin_AssessmentID, a.BenchGoal, b.MonthName, b.WeekOfMonth, (CASE (a.Score - a.BenchGoal) When (< 0) Then 'Red' Else 'Black' End) as Barcolor
FROM (SELECT Test_Admin_Assessment.FullName, Test_Admin_Assessment.ShortName, Test_Admin_Assessment.ModifiedBy, Test_Results.SchoolYear,
Test_Results.TestAdministrationDate, Test_Results_Trait.Score, TD_Assessment_Grade.Grade, TD_Available_Traits.FullName AS Trait_FN,
TD_Available_Traits.ShortName AS Trait_SN, TD_Available_Trait_Score_Sub_Category.ScoreMinimum,
TD_Available_Trait_Score_Sub_Category.ScoreMaximum, TD_Available_Trait_Score_Sub_Category.IsBenchmark,
TD_Available_Trait_Score_Sub_Category.FieldColor, Student.Student_FN, Student.Student_LN, Test_Results.Test_Admin_AssessmentID, NULL
AS BenchGoal
FROM Test_Admin_Assessment INNER JOIN
Test_Admin_Trait ON Test_Admin_Assessment.ID = Test_Admin_Trait.Test_Admin_AssessmentID INNER JOIN
Test_Results ON Test_Admin_Assessment.ID = Test_Results.Test_Admin_AssessmentID INNER JOIN
Test_Results_Trait ON Test_Admin_Trait.ID = Test_Results_Trait.Test_Admin_Trait_ID AND
Test_Results.ID = Test_Results_Trait.Test_Results_ID INNER JOIN
TD_Assessment_Grade ON Test_Admin_Assessment.AssessmentGradeID = TD_Assessment_Grade.ID INNER JOIN
TD_Traits ON Test_Admin_Trait.TestedTraitID = TD_Traits.ID AND TD_Assessment_Grade.ID = TD_Traits.AssessmentGradeID INNER JOIN
TD_Available_Traits ON TD_Traits.AvailableTraitID = TD_Available_Traits.ID INNER JOIN
TD_SubTraits ON TD_Traits.ID = TD_SubTraits.TraitID INNER JOIN
TD_Available_Trait_Score_Sub_Category ON TD_SubTraits.AvailableSubTraitID = TD_Available_Trait_Score_Sub_Category.ID AND
Test_Results_Trait.Score >= TD_Available_Trait_Score_Sub_Category.ScoreMinimum AND
Test_Results_Trait.Score <= TD_Available_Trait_Score_Sub_Category.ScoreMaximum INNER JOIN
Student ON Test_Results.Student_Number = Student.Student_Number AND Test_Results.SchoolYear = Student.SchoolYear AND
Test_Results.DistrictCode = Student.DistrictCode
WHERE (TD_Available_Traits.ShortName = @trait)) AS a FULL OUTER JOIN
(SELECT DISTINCT MonthName, WeekOfMonth, Date, SchoolYear, Year, Month, StandardDate
FROM dim_Date
WHERE (SchoolYear = @schoolyr)) AS b ON a.TestAdministrationDate = b.Date
UNION
SELECT a_1.FullName, a_1.ShortName, a_1.SchoolYear, a_1.TestAdministrationDate, a_1.Score, a_1.Grade, a_1.Trait_SN, a_1.Trait_FN, a_1.ScoreMinimum,
a_1.ScoreMaximum, a_1.IsBenchmark, a_1.FieldColor, a_1.Student_FN, a_1.Student_LN, a_1.Test_Admin_AssessmentID, a_1.BenchGoal, b_1.MonthName,
b_1.WeekOfMonth
FROM (SELECT Test_Admin_Assessment_1.FullName, Test_Admin_Assessment_1.ShortName, Test_Admin_Assessment_1.ModifiedBy, Test_Results_1.SchoolYear,
Test_Results_1.TestAdministrationDate, Test_Results_Trait_1.Score, TD_Assessment_Grade_1.Grade, TD_Available_Traits_1.FullName AS Trait_FN,
TD_Available_Traits_1.ShortName AS Trait_SN, TD_Available_Trait_Score_Sub_Category_1.ScoreMinimum,
TD_Available_Trait_Score_Sub_Category_1.ScoreMaximum, TD_Available_Trait_Score_Sub_Category_1.IsBenchmark,
TD_Available_Trait_Score_Sub_Category_1.FieldColor, Student_1.Student_FN, Student_1.Student_LN, Test_Results_1.Test_Admin_AssessmentID,
MIN(TD_Available_Trait_Score_Sub_Category_1.ScoreMinimum) AS BenchGoal
FROM Test_Admin_Assessment AS Test_Admin_Assessment_1 INNER JOIN
Test_Admin_Trait AS Test_Admin_Trait_1 ON Test_Admin_Assessment_1.ID = Test_Admin_Trait_1.Test_Admin_AssessmentID INNER JOIN
Test_Results AS Test_Results_1 ON Test_Admin_Assessment_1.ID = Test_Results_1.Test_Admin_AssessmentID INNER JOIN
Test_Results_Trait AS Test_Results_Trait_1 ON Test_Admin_Trait_1.ID = Test_Results_Trait_1.Test_Admin_Trait_ID AND
Test_Results_1.ID = Test_Results_Trait_1.Test_Results_ID INNER JOIN
TD_Assessment_Grade AS TD_Assessment_Grade_1 ON Test_Admin_Assessment_1.AssessmentGradeID = TD_Assessment_Grade_1.ID INNER JOIN
TD_Traits AS TD_Traits_1 ON Test_Admin_Trait_1.TestedTraitID = TD_Traits_1.ID AND
TD_Assessment_Grade_1.ID = TD_Traits_1.AssessmentGradeID INNER JOIN
TD_Available_Traits AS TD_Available_Traits_1 ON TD_Traits_1.AvailableTraitID = TD_Available_Traits_1.ID INNER JOIN
TD_SubTraits AS TD_SubTraits_1 ON TD_Traits_1.ID = TD_SubTraits_1.TraitID INNER JOIN
TD_Available_Trait_Score_Sub_Category AS TD_Available_Trait_Score_Sub_Category_1 ON
TD_SubTraits_1.AvailableSubTraitID = TD_Available_Trait_Score_Sub_Category_1.ID AND
Test_Results_Trait_1.Score >= TD_Available_Trait_Score_Sub_Category_1.ScoreMinimum AND
Test_Results_Trait_1.Score <= TD_Available_Trait_Score_Sub_Category_1.ScoreMaximum INNER JOIN
Student AS Student_1 ON Test_Results_1.Student_Number = Student_1.Student_Number AND Test_Results_1.SchoolYear = Student_1.SchoolYear AND
Test_Results_1.DistrictCode = Student_1.DistrictCode
WHERE (TD_Available_Traits_1.ShortName = @trait) AND (TD_Available_Trait_Score_Sub_Category_1.IsBenchmark = 'True')
GROUP BY Test_Admin_Assessment_1.FullName, Test_Admin_Assessment_1.ShortName, Test_Admin_Assessment_1.ModifiedBy, Test_Results_1.SchoolYear,
Test_Results_1.TestAdministrationDate, Test_Results_Trait_1.Score, TD_Assessment_Grade_1.Grade, TD_Available_Traits_1.FullName,
TD_Available_Traits_1.ShortName, TD_Available_Trait_Score_Sub_Category_1.ScoreMinimum,
TD_Available_Trait_Score_Sub_Category_1.ScoreMaximum, TD_Available_Trait_Score_Sub_Category_1.IsBenchmark,
TD_Available_Trait_Score_Sub_Category_1.FieldColor, Student_1.Student_FN, Student_1.Student_LN, Test_Results_1.Test_Admin_AssessmentID)
AS a_1 FULL OUTER JOIN
(SELECT DISTINCT MonthName, WeekOfMonth, Date, SchoolYear, Year, Month, StandardDate
FROM dim_Date AS dim_Date_1
WHERE (SchoolYear = @schoolyr)) AS b_1 ON a_1.TestAdministrationDate = b_1.Date

 

[xlbo]

change:
(CASE (a.Score - a.BenchGoal) When (< 0) Then 'Red' Else 'Black' End) as Barcolor

to:
CASE WHEN (a.Score - a.BenchGoal) < 0 Then 'Red' Else 'Black' End as Barcolor


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question