Performance of SQL commands and subreports in Crysal Reports - CR

I've been recently attempting to imrpove the performance of a number of 'legacy' Crystal Reports. One in question contained approximately 7 subreports with the main report returning 300+ records. I saw this an ideal candidate for using solely a SQL command.

I'd re-written the Crystal using said SQL command and then compared the performance of both existing subreport and new SQL command versions using SQL Server Profiler.

The first image shows the existing subreport version:

The second shows the SQL command version:

I was surprised to see the values returned by the SQL command version which seemed vastly larger than the sum of all the 'subreport' performance values. But, on closer inspection I realised that the subreport was only returning data for the first page in Crystal Reports whereas the SQL version was gleaning all the database info in 1 go.

The below image shows the performance of the subreport version when the user refreshes the report on page one and subsequently moves to page two (page one returns two main report rows whereas page report returns three).

As this report contains over 70 pages it is plain to see that using the subreport version the database would be queried multiple times and incur all the related network traffic. It is thus beneficial to go with the SQL command version (as originally expected!). Summing up all the CPU and read and writes also confirms this.

One concern that I've been unable to resolve is that Crystal Report seems to create two SQL batches when using a SQL command thus doubling the performance overheads. The same query when run in SQL Server Management Studio records only 1 SQL batch as expected. I need to look into this as I'm not fully sure why this is occuring.

Using SQL commands, parameters and subreports - CR

My report contains a number of subreports, each comprising a SQL command and using a parameter value passed from the main report.

1) Set up the main report parameter, in this instance called ?School

2) Add a subreport, Add Command with the Where clause as follows:

WHERE (School.SchoolKey = {?School})

3) For the Parameter list, Click Create:

Parameter Name = School
Value Type = Number

It will then prompt for a parameter value, enter 1 (anything will suffice at this stage)

NOTE: When the database field is a uniqueidentifier, use 'String' type and for the prompt use a value in the uniqueidenfier format: {29D2B899-393A-4592-9B00-D289884A1F94}

4) Back in the main report, right click the subreport and Change Subreport Links:

Available Fields = ?School
Subreport parameter field to use = ?School (make sure to select the user-generated one and not the automatically generated one named similar to ?PM-?School)

5) Voila! Main report parameters should now be passed to SQL Commands in subreports.

FInd current financial year start and end dates - CR

To find the start date and finish date of the current financial year for subsequent use in various calculations:

Start of year formula:

Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar startfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN startfinancialyear := (DATESERIAL (YEAR(dates)-1,4,1))
ELSE startfinancialyear := (DATESERIAL (YEAR(dates),4,1));


End of year formula:
 
Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar endfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN endfinancialyear := (DATESERIAL (YEAR(dates),3,31))
ELSE endfinancialyear := (DATESERIAL (YEAR(dates)+1,3,31));


Alteration: The End of year formula gets the last day of the financial year, i.e. 31st March. However, it returns the value for the very start of the day (31/03/xxxx 00:00:00) which when incorporated into a selection formula could potential lead to records logged during that final day being excluded.

A correction would be:

End of year formula (correction):
 
Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar endfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN endfinancialyear := DATEADD("s" , -1, (DATESERIAL (YEAR(dates),4,1)))
ELSE endfinancialyear := DATEADD("s" , -1, (DATESERIAL (YEAR(dates)+1,4,1)));

Thus, returning a value of "31/03/xxxx 23:59:59"

(Currently set up for use in the UK)

Group Header continued... - CR

To display 'continued' or similar when a group continues onto a new page, firstly ensure Repeat Group Header is checked in Group Expert then add the following formula to the Group Header:

IF InRepeatedGroupHeader THEN {?School} & " continued"
ELSE {?School}

Show Database name - SQL

To show the database name:


SELECT DB_NAME() AS DataBaseName


This also proves useful in Crystal Report development where 'test' and 'live' databases in use. Simply add the above code to a Command in Database Expert and show the DatabaseName field in the report header.

Nth largest value - SSRS

I recently had a query regarding replicating Crystal Reports nth largest functionality in Reporting Services (hope this helps, Andy!).

For example, In Crystal Reports, its simply a case of using Insert>>Summary

I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.

Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)

SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson

The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.

SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc


By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.

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

Check whether a string was all in uppercase - CR

I needed a quick way to check whether a string was all uppercase.
Using the InStr function:

InStr ({fieldname},(UpperCase ({fieldname})) ,0 )

The syntax being:
InStr([start,]string1,string2[,compare])
start- an optional starting position
string1,2 - strings to compare
compare - either 0 or 1, 0 being a binary comparison, 1 being textual.

http://msdn.microsoft.com/en-us/library/wybb344c(VS.85).aspx

Example Select Case to get time period bandings - CR

Example Select Case to get time period bandings:

select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"

Checkboxes - CR

Provide Checkboxes for a boolean field.


If {fldName} = true then Chr(254) else Chr(168)


and set the font to wingdings.

Display current month - CR

MonthName(Month(CurrentDate))

Get last day of month - CR

local numbervar thismonth := Month(CurrentDate)+1;
local numbervar thisyear := Year(CurrentDate);
local datevar endday;


endday := Date(thisyear, thismonth, 1)-1; //get the last day of the month

Get first day of month - CR

local numbervar thismonth := Month(CurrentDate);
local numbervar thisyear := Year(CurrentDate);
local datevar startday;


startday := Date(thisyear, thismonth, 1); //get the first day of the month

Display every nth record - CR

Put the following formula in the Suppress section of the Section Expert:

remainder((recordnumber,10,)<>0

This will display every 10th record

Format numbers in formula - CR

ToText({fldName}, 0)

0 indicates the number of decimal places

Format Dates in formula - CR

ToText({fldName}, "dd-MMM-yyyy")

List of Value Limit - CR

I've just happened upon a major shortcoming of Crystal Reports. A dynamic parameter is only retrieving a limited number of records. On further inspection I found this from Brian Bischof:


'There is an undocumented registry entry that you should know about. By default, CR only reads 1,000 records before creating the list of values to display. You have to add a registry entry to override the default behavior.

HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\LOV If you are using R2, then change 11.0 to 11.5.

Add a string called MaxRowsetRecords and set a new maximium number of records. Making it 0 gives you unlimited records'

Crystal Reports Processing Engine - CR

If Then Else Functions - CR

if {Element.ElementName} = "True/False"
and {SurveyDataElement.ElementValue}="1"
then "True"
else
if {Element.ElementName} = "True/False"
and {SurveyDataElement.ElementValue}="0"
then "False"