D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.

My alternative to scraps of paper lying about.

Others may find bits of it useful!

Tuesday, 20 April 2010

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

Friday, 9 April 2010

Useful Character code chart

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


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:


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;

//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))