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!








Wednesday, 30 June 2010

Display dataset query in the report - SSRS

To display a dataset query in the report, simply place the following code in a textbox expression:

=datasets!datasetname.commandtext

Monday, 28 June 2010

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}

Tuesday, 22 June 2010

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.

Wednesday, 2 June 2010

Auxiliary CTE of Months - SQL

WITH CTE_Months
AS
(
SELECT Mths ='January ' UNION ALL
SELECT Mths ='February ' UNION ALL
SELECT Mths ='March ' UNION ALL
SELECT Mths ='April ' UNION ALL
SELECT Mths ='May ' UNION ALL
SELECT Mths ='June ' UNION ALL
SELECT Mths ='July ' UNION ALL
SELECT Mths ='August ' UNION ALL
SELECT
Mths ='September ' UNION ALL
SELECT Mths ='October ' UNION ALL
SELECT
Mths ='November ' UNION ALL
SELECT
Mths ='December '
)
SELECT Mths FROM CTE_Months