Hello,
I need help with this report that utilizes rank and quartiles. I tried to explain it below the best way possible.



Company
Date
Gravity
pH
BP
11/1/2008
98
98

11/2/2008
94
92

11/2/2008
92
95

11/3/2008
94
99

11/3/2008
91
92


Company
Date
Gravity
pH
Chevron
11/1/2008
94
90

11/2/2008
97
97

11/2/2008
88
94

11/3/2008
96
95

11/3/2008
90
93


KPI target for gravity is within 5 of 100%
KPI target for pH is within 3 of 100%

BP has 1 record dated 11/1/2008 within 5 % points of 100 so that’s 1 out of 5 and 20% for that field and would go in the 1st quartile as red, shaded only up to the number in the quartile.

Chevron has 2 records dated 11/2/2008 and 11/3/2008 within 5 % points of 100 so that’s 2 out of 5 and 40% for that field and would go in the 2nd quartile shaded yellow, shaded only up to the number in the quartile.

1st quartile
2nd quartile
3rd quartile
4th quartile


Customer: BP






20.0

Score


20.0%
Overall Rank

2
Out of


2 out of 2



How can I write this to do exactly what i explained above? I wrote this below in SSRS and its still not the way i need it.

SELECT Customer
,[Cycles]
,[pH]
,[UnfilteredPhosphate]
,[FreeChlorine]
,[Turbidity]
,[DeltaPhosphate]
,[BacteriaCount]
,[CarbonSteelCorrosion]
,[AdmiraltyCorrosion]
,[OverallKPI]
,PlantType,Location,SystemName,pHControl

FROM [KPI].[dbo].[customerdata] cu join
dbo.customer c on c.custid = cu.custid
where Customer = @Customer
and pHControl = @phcontrol
and systemname = @system
and Location = @location
and PlantType = @PlantType




(for quartile) =IIf(Fields!pH.Value > 25, IIf(Fields!pH.Value > 50, IIf(Fields!pH.Value > 75, "Red", "Yellow"), "Yellow"), "Green")

this is what i get:

Desalter ReportCustomerlocationgravitydesaltertempinletsaltinletsolidswashwateroutletfreewaterBP Decatur, AL60 87 90 09 80 98 BestWorstGravity :60ValueRankOut OfDesalter Temperature :87 2.50%149153