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, 26 August 2009

Pagination - SSRS

To remove pagination on the webpage set InteractiveHeight to 0

Tuesday, 25 August 2009

Date Stamp trigger - SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trg_test]
ON [dbo].[JobLog]
AFTER UPDATE
AS
BEGIN
IF

UPDATE(JobLogSignOff)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON
;
UPDATE
JobLog
SET JobLogSignOffTimeStamp = GETDATE()
WHERE
JobLogID in (SELECT JobLogID FROM inserted)
-- Insert statements for trigger here
END
END

No drop trigger - SQL

USE [JobLog]
GO
/****** Object: DdlTrigger [trig_nodrop] Script Date: 07/08/2009 16:59:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trig_nodrop]
ON DATABASE
FOR
drop_table, alter_table
AS
PRINT
'...you cannot do that, ha ha ha...'
ROLLBACK
;

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trig_nodrop] ON DATABASE

Monday, 24 August 2009

Printed by and time - SSRS

="Printed by " + User!UserID + " on " + Globals!ExecutionTime.ToString()

Execution Time - SSRS

="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds <> 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

Page x of y - SSRS

="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()

Report Execution - SSRS

--run against ReportServer
USE
ReportServer
GO
SELECT
dbo.Catalog.Name, dbo.Catalog.Path, dbo.ExecutionLog.UserName, dbo.ExecutionLog.TimeStart, dbo.ExecutionLog.TimeEnd,
dbo.ExecutionLog.TimeDataRetrieval, dbo.ExecutionLog.TimeProcessing, dbo.ExecutionLog.TimeRendering, dbo.ExecutionLog.Format,
dbo.ExecutionLog.Parameters, dbo.ExecutionLog.Status
FROM
dbo.Catalog INNER JOIN
dbo.ExecutionLog ON dbo.Catalog.ItemID = dbo.ExecutionLog.ReportID
ORDER BY
dbo.ExecutionLog.TimeStart DESC
-----sum use
USE
ReportServer
GO
SELECT
dbo.ExecutionLog.UserName, COUNT(dbo.ExecutionLog.UserName)
FROM
dbo.Catalog INNER JOIN
dbo.ExecutionLog ON dbo.Catalog.ItemID = dbo.ExecutionLog.ReportID
GROUP BY
dbo.ExecutionLog.UserName
ORDER BY
COUNT(dbo.ExecutionLog.UserName)DESC

A4 Portrait layout - SSRS

For A4 Portrait settings:

Report Properties:
Grid Spacing  0.2cm

InteractiveSize
    Width  21cm
    Height  0cm

Margin all 0.5cm

PageSize
    Width  21cm
    Height  29.7cm

Body Properties:
Size
    Width  20cm
    Height  28.7cm

Friday, 21 August 2009

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'

Insert Function - SQL

insert into Establishment ( EstablishmentName, EstablishmentCode)
select Col1, Col2
from test

Bulk Insert Function - SQL

delete from dbo.testlistofestabs;
go
bulk insert testlistofestabs
from 'D:\JOBLOGGING\Establishment import\list of estabs.txt'
with
(
fieldterminator = '\t', --tab
rowterminator = '\n' --new line
)
go

Get current SQL Server Version - SQL

select @@version


For SQL Server 2005:


For SQL Server 2008 R2:




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"

Select Case Function - CR

select ({CADDue_sp;1.DATE DUE}-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 DRAWING STATUS"

Dealing with Nulls - CR

if isnull({ISSUE.DESCRIPTION})
then "There are currently no issues logged against this project"
else ({ISSUE.REFERENCE} & " - " &{ISSUE.DESCRIPTION})

Detailed or Overview report toggled by parameter - CR

Create parameter {?DetailedOverviewReportParam}, two fields with values 1 & 0 and descriptions 'Detailed Report' and 'Overview Report' respectively.

Create formula {DetailedOverview}
shared stringvar fullsuppress := {?DetailedOverviewReportParam}

Put both {?DetailedOverviewReportParam} and {DetailedOverview}in header area, make invisible (to initiate when report is run).

In Section Expert, formula for Suppress (No Drill Down):
shared stringvar fullsuppress;
fullsuppress = '0'

Green bar report - CR

If(RecordNumber Mod 2=0)
then crGreen
else DefaultAttribute



Note: Record Number can be replaced with GroupNumber to alternate Group row colours


Thursday, 20 August 2009

Format Dates - SSRS

=Format(Fields!myDate.Value, "M/d/yy") - 2/11/08
=Format(Fields!myDate.Value, "MM/dd/yyyy") - 02/11/2008
=Format(Fields!myDate.Value, "d-MMMM-yy") - 11-December-08
=Format(Fields!myDate.Value, "d-MMM-yyyy") - 11-Dec-2008
=Format(Fields!myDate.Value, "M/d/yyyy H:mm") - 2/11/2008 13:50
=Format(Fields!myDate.Value, "MMM-dd-yyyy") - Feb-11-2008

Switch Function - SSRS

Example use of Switch function (similar to Select Case)

=Switch(Fields!STATUS.Value="CHECKED", chr(252),
Fields!STATUS.Value="DEFERRED", chr(198),
Fields!STATUS.Value="NOT REQD", chr(251))

returns various wingding characters.

chr(252) - Tick
chr(198) - up and right arrow
chr(251) - Cross

Default location of the templates folder - SSRS

To create your own reusable templates, put your RDL files in this location:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\

URL command sections - SSRS

&rs:Command=Render
&rc:Parameters=false - hide parameters in report
&rc:Toolbar=false - hide toolbar

DateAdd Function - SSRS

=DateAdd(DateInterval.Month, 6, Today())
=DateAdd(”D”, 6, Today())
=DateAdd(”Month”, 1, Today())

Display subreport when null returns - SSRS

create dummy dataset in subreport
select 'abc' as dummy
create text field.........no data available....set visibility to =CountRows("dataset") > 0

Conditional formatting - using a function - SSRS

Another option is to write a function to do this and an example below involves a case statement. In the color property of the textbox, you can reference the function like so:

=Code.GetColour(Fields!SubTotal.Value)

If you had multiple textboxes on a report that required the same colour formatting, this technique would be a bit more reusable.

Public Function GetColour(ByVal InputValue As Double) As String
Select
Case InputValue
Case 1000
GetColour = "Red"
Case 10000
GetColour = "Blue"
Case 30000
GetColour = "Green"
Case Else
GetColour = "Black"
End
Select
End

Function

Green bar report - SSRS

=iif(RowNumber(Nothing) Mod 2, “LightGreen”, “White”)

IsNothing Function - SSRS

=Iif(IsNothing(Fields!ANYFIELD.Value), "The Field Is Null", Fields!ANYFIELD.Value)