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

Useful Character code chart

Here's a link to a useful character code chart:


http://tlt.its.psu.edu/suggestions/international/bylanguage/mathchart.html


and to get the Windows Character Map Utility:

Start>>Programs>>Accessories>>System Tools

and simply copy and paste the relevant character or obtain the unicode number from the keystoke value, where applicable, or convert the hex to decimal.

Hexadecimal to decimal converter:

http://easycalculation.com/hex-converter.php

Array example - Crystal

Local Numbervar counter := 1;
Local Stringvar Array taskdates;
Local Datetimevar dates := CurrentDate;
Local Datetimevar finishdate := DateAdd("yyyy",5,CurrentDate);
Local Stringvar returnstring;

Do
(
//set array size to counter, initally 1, preserving previous size and values
Redim Preserve taskdates[counter];
//assign dates value, initially CurrentDate to array element numbered counter, initially 1
taskdates[counter] := ToText(dates, "dd-MMM-yyyy");
//increment date variable by 1 year
dates := DateAdd("yyyy",1,dates);
//increment counter by 1
counter := counter+1;
)
//continue do loop while date variable is less than or equal to finishdate
While dates <= finishdate ;

returnstring := Join(taskdates, Chr(13))