Count Distinct Over Partition

To get a count of distinct values over a windowing partition:

DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No)
+ DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No desc)
- 1

Green bar matrix report - SSRS

I was recently asked by an anonymous blog poster (if reading please identify yourself!) how to go about creating a 'green-bar' style cross-tab/matrix report in both Crystal and SSRS.

Here's how to do it in SSRS (Cystal to follow at a later date):


1) Create an inner row grouping, in this case named 'Count' and given a grouping expression of =1















2) for the cell 'Count', set the property Value =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")

3) for the cell 'Count', set the property BackgroundColor =value















4) for the matrix data cell, named 'STATUS', set the property BackgroundColor =ReportItems!count.Value














5) for the outer row grouping, named 'TITLE, set the property BackgroundColor =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")















6) To hide the inner row grouping cell, 'Count', set
the following properties:
-set width to a minimum
-set BorderStyle Left =None
-set Color =value
-set CanGrow =False
set FontSize =1pt

for the outer row grouping cell:
-set BorderStyle Right =None