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