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!








Friday, 30 October 2009

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

Wednesday, 28 October 2009

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

Thursday, 22 October 2009

Condensing large character string and removing carriage returns - SSRS

***Update ***
Function created:

for condensing a large character string and removing any carriage returns (such as in a comments field).

Function shortcomment(byval commentstring as string, stringlength as integer) as string

if commentstring="" or isnothing(commentstring)
return commentstring
else

commentstring=replace(commentstring, chr(10)," ")
if commentstring.length >stringlengthcommentstring=left(commentstring, stringlength) & "....."
return commentstring
else
return commentstring
end if
end if
end function


to implement:

=code.shortcomment(Fields!comments.Value, 10)

where 10 is the condensed length

Tuesday, 20 October 2009

Format numbers in formula - CR

ToText({fldName}, 0)

0 indicates the number of decimal places

Thursday, 15 October 2009

Condensing large character string and removing carriage returns - SSRS

Useful formula for condensing a large character string and removing any carriage returns (such as in a comments field).


=iif(len(Replace((Fields!Comments.Value),CHR(10)," "))>25, (left(Replace((Fields!Comments.Value),CHR(10)," "),25) & "....."),Fields!Comments.Value)


...next stage will be to create a function...

http://sqlreportingservicescrystalreports.blogspot.com/2009/10/condensing-large-character-string-and_22.html

Tuesday, 6 October 2009

Newline in textbox - SSRS

To create a linebreak in a textbox:


="This is the first line " + VBCRLF + "This is a newline"

Script to determine SQL Server Reporting Services parameters, path and default values - SQL

--Find all the reports, their parameters and default values
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd --ReportDefinition
)
SELECT
NAME
, PATH
, x.value ('@Name', 'VARCHAR(100)') AS ReportParameterName
, x.value ('DataType[1]', 'VARCHAR(100)') AS DataType
, x.value ('AllowBlank[1]', 'VARCHAR(50)') AS AllowBlank
, x.value ('Prompt[1]', 'VARCHAR(100)') AS Prompt
, x.value ('Hidden[1]', 'VARCHAR(100)') AS Hidden
, x.value ('data(DefaultValue/Values/Value)[1]', 'VARCHAR(100)') AS Value
FROM
(
SELECT PATH
, NAME
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
WHERE CONTENT IS NOT NULL AND TYPE = 2
) A
CROSS APPLY ReportXML.nodes('/Report/ReportParameters/ReportParameter') R(x)
--WHERE NAME = 'Sales_Report'
--Use the where clause above to look for a specific report

ORDER BY NAME

taken from Sankar Reddy
http://www.mssqltips.com/tip.asp?tip=1839

Adding Null Values for Multi-Valued Parameters - SSRS

When we use multi-valued parameters, we would want to have null values as an option as we may want it to be selected by default. The problem is, there is no null value for multi-valued parameters. One possible solution could be to create a view that has a null value. To do this, we can make use of the UNION ALL command present in SQL.

CREATE VIEW viewDomainWithNull
AS SELECT -1 AS [Domain Id], 'NULL' AS [Domain Name]
UNION ALL
SELECT [Domain Id],[Domain Name] FROM Domain

Taken from
http://www.codeproject.com/KB/reporting-services/SSRSaaka3.aspx

Friday, 2 October 2009

Remove Embedded image from a Report Definition - SSRS

I'd developed a report that if it was parred down (all datasets, parameters, images, textboxes removed) would make a good template for future reports.

So, having deleted everything to form my template I noticed that the RDL file was a significant size. On opening up, it was still referencing a number of Embedded Images.

To remove:
Report - Embedded Images... - and delete