Pagination - SSRS

To remove pagination on the webpage set InteractiveHeight to 0

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

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

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

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)