SQL Server, SSRS and Crystal Reports

SQL - duplicate values

2018-06-06T14:51:00.002+01:00

List of employees for example. Quick way to find duplicate values using LAG windowing function

;with c as (
SELECT [FirstName]
      ,[LastName],
   [FirstName] + ' ' +   [LastName] x
      ,[StaffNumber]
FROM Employee
),
cc as(
SELECT FirstName, LastName, StaffNumber, x , lag (x) over (partition by LastName order by x) dup
from c)
select FirstName, LastName, StaffNumber, x , dup From cc
where x=dup
order by LastName

Count Distinct Over Partition

2018-05-24T15:32:00.000+01:00

To get a count of distinct values over a windowing partition:

DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No)
+ DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No desc)
- 1

Using javascript to encode a URL with an ampersand

2017-09-01T16:34:00.005+01:00

javascript escape function:

="javascript:void(window.open('http://xxx/reportserver?/Folder A/Report X" & "&rs:Command=Render" & "&rc:Parameters=true" & "&param1="
& replace(
Fields!description.Value, "&", "'+escape('&')+'"
)
& "'));"

SSRS chart display % on labels issue

2017-05-23T14:00:00.002+01:00

SSRS chart display % on labels issue

To show percentage symbol after a literal value:

Chart series label - Properties
Format = 0\% or 0.00\% (depending on no. of dec. places reqd)

ssrs url javascript

2017-02-13T12:41:00.000+00:00

="Javascript:" 
    & IIF(left(Fields!Name.Value,11)="RESTRICTED-", 
        "alert('Restricted!'); ","") & IIF(Fields!Name_Alert.Value = 1, "alert('Alternate Alert!'); ","") 
    & "void(window.open('" 
    & Globals!ReportServerUrl 
    & "/Pages/ReportViewer.aspx?%2fJPD%2fPO_Dashboard%2fJuvenile_Profile&rs:Command=Render" 
    & "&rc:Parameters=true" 
    & "&Emp_Number=" 
    & Parameters!Param1.Value 
    & “&ID=" & Fields!ID.Value & "'));"

Courtesy of Christoper Brown at:

http://stackoverflow.com/questions/18003013/open-ssrs-url-in-new-window

=iif(

COUNT(Fields!ABC.Value)>0,

"javascript:void(window.open('http://XXX/reportserver?/my Reports/this report"
& "&rs:Command=Render"
& "&rc:Parameters=true"
& "&p1=" & Parameters!p1.Value
& "&f1=" & Fields!f1.Value
& "&f2" & Fields!f2.Value &
"'));",NOTHING)

URL encoding

Replace(Fields!myfield.Value,"&","' + escape('&') + '")

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6462b0f6-d784-4c7a-afe0-2813bec5770a/url-action-with-javascript-and-ssas-parameter?forum=sqlreportingservices

SSRS custom sort

2016-10-11T09:48:00.002+01:00

Public Function CustomSortOrder(ByVal RAG As String) as Integer

Select Case RAG

Case = "Up to 6 month"

Return 1

Case = "7 to 12 months"

Return 2

Case = "More than 12 months"

Return 3

End Select

End Function

=Code.CustomSortOrder(Fields!RAG.Value)

ssrs subreport background colour

2016-06-30T15:44:00.001+01:00

Ssubreport properties.
Select Parameters and add a parameter.
The name column is the name of the parameter in the subreport (rowcolour) and value is the value to set it to.
Set Value to the same expression used to set the background color for the row.
background color =Parameters!rowcolour.Value into the expression builder.

combine multiple rows into one

2015-04-20T11:41:00.002+01:00

LEFT OUTER JOIN (

SELECT ref, STUFF

((SELECT ' ' +

com_text

FROM comm

WHERE ref = q1.ref

ORDER BY comseq ASC

FOR XML PATH('')), 1, 1, '') [Comments]

FROM comm AS q1

GROUP BY ref

) AS Comm ON Comm.ref = x.ref

Green bar for a group header - SSRS

2014-05-06T11:43:00.001+01:00

=IIF(RunningValue(Fields!xxx.Value,COUNTDISTINCT,NOTHING) MOD 2 = 1,

"White","PaleGreen")

=iif(RunningValue(Fields!xxx. Value,CountDistinct,"parentgroupname") Mod 2,"WhiteSmoke","White")

List all Stored Procedures within a db - SQL

2013-12-24T10:54:00.001+00:00

SELECT
*

FROM
TFSheffieldNew.INFORMATION_SCHEMA.ROUTINES

WHERE
(ROUTINE_TYPE = 'PROCEDURE')

Auxiliary Numbers Table - Populated by CTE (Based on Itzik Ben-Gan)

2013-10-28T10:43:00.001+00:00

USE [Auxiliary]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Nums]
([n] [int] NOT NULL, PRIMARY KEY CLUSTERED
([n] ASC ))
GO
;WITH x00(n) AS (SELECT 1 UNION ALL SELECT 1),
x02 (n) AS (SELECT 1 FROM x00 a, x00 b),
x04 (n) AS (SELECT 1 FROM x02 a, x02 b),
x08 (n) AS (SELECT 1 FROM x04 a, x04 b),
x16 (n) AS (SELECT 1 FROM x08 a, x08 b),
x32 (n) AS (SELECT 1 FROM x16 a, x16 b),
cTally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM x32)

INSERT INTO Nums(n)
SELECT * from cTally
WHERE n <= 1000000;
GO

Based on the CTE used here:
http://sqlreportingservicescrystalreports.blogspot.co.uk/2009/12/auxilary-cte-of-numbers-sql.html

To obtain SQL Server 2008R2 Product Key

2013-01-28T10:19:00.001+00:00

To obtain SQL Server 2008R2 Product Key:
USE MASTER

GO

EXEC XP_REGREAD 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\100\BIDS\Setup','ProductCode'

GO

Convert time to string - SQL

2012-11-20T17:40:00.000+00:00

Scenario where a db field contains seconds, the user wanted a SSRS report displaying various aggregates of the seconds field as Hours - Minutes - Seconds.

IFOBJECT_ID('dbo.udf_SCC_TimeToString') IS NOT NULL
DROP FUNCTION dbo.udf_SCC_TimeToString ;
GO

CREATEFUNCTION dbo.udf_SCC_TimeToString
(
@timesec INT) --input in seconds as integer

RETURNSVARCHAR(25)

/*==========================================

Dom Horton

17/10/2012

==========================================*/

AS

BEGIN
DECLARE
@return
AS VARCHAR(25),

@a AS INT,

@b AS INT,

@hours AS INT,

@mins AS INT,

@secs AS INT;

SET@a = @timesec%3600 --get no. of seconds over the hour

SET@hours = (@timesec - @a)/3600 --get no. of seconds of completed hours, divide by hour in seconds to get completed hours

SET@b = @a --hold no. of secs over the hour

SET@a = @b%60--get no. of secs over the min

SET@mins = (@b - @a)/60 --get no. of seconds of completed mins, divide by min in secs to get completed minutes

SET@secs = @a

SET@return =

CASE

WHEN @timesec >=3600

THEN

convert(varchar(10),@hours) + ' hr ' +

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

WHEN @timesec >=60

THEN

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

ELSE

convert(varchar(10),@secs) + ' sec '

END;

RETURN
@return

END

Example below showing the Stored Procedure in use:

and in use in a SSRS report:

Missing indexes - SQL

2012-09-06T14:06:00.000+01:00

SELECT index_handle, database_id, object_id, equality_columns, inequality_columns, included_columns, statement

FROM sys.dm_db_missing_index_details

Index usage - SQL

2012-09-06T14:05:00.000+01:00

SELECT OBJECT_NAME(I.object_id) AS TableName, I.name, I.index_id, I.type_desc, I.is_unique,

I.fill_factor, I.is_padded, I.is_disabled, I.is_hypothetical,

IUS.index_id , IUS.user_seeks, IUS.user_scans, IUS.user_lookups, IUS.user_updates, IUS.last_user_seek,

IUS.last_user_scan, IUS.last_user_lookup, IUS.last_user_update, IUS.system_seeks, IUS.system_scans, IUS.system_lookups, IUS.system_updates,

IUS.last_system_seek, IUS.last_system_scan, IUS.last_system_lookup, IUS.last_system_update

FROM sys.indexes AS I LEFT OUTER JOIN

sys.dm_db_index_usage_stats AS IUS

ON I.object_id = IUS.object_id AND

I.index_id = IUS.index_id

ORDER BY OBJECT_NAME(I.object_id)

Useful SSRS expression syntax

2012-08-08T10:22:00.003+01:00

=IIF(Fields!a.value>100, True, False)

--------------------------------------------------------
=IIF(Fields!a.value >=10, "Green", IIF(Fields!a.value >=1, "Blue", "Red"))

values >=10 are green, between 1 & 9 are blue, less than 1 are red

--------------------------------------------------------

=SWITCH(
    Fields!a.value >=10, "green",
    Fields!a.value >=1, "blue",
    Fields!a.value = 1, "yellow",
    Fields!a.value <=0, "Red")

>=10 are green, between 1 & 9 blue, =1 yellow, <=0 red

--------------------------------------------------------

SWITCH function finds the first expression that's true. It will not catch errors further along.
IIF function evaluates all parts of the expression.

--------------------------------------------------------

=IIF(Rownumber("scope") Mod 2 = 0, "Khaki", "White")

set "scope" to reset row colours for every group

--------------------------------------------------------

Using Count function on a uniqueidentifier field - SQL

2011-10-06T13:39:00.001+01:00

SELECT COUNT(D.DOCUMENTID) AS DocCount, P.TITLE
FROM DOCUMENT AS D inner join
PROJECT AS P ON D.PROJECTID = P.PROJECTID
GROUP BY P.PROJECTID, P.TITLE

When running this query I encountered the following error:

Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.

To avoid this error when trying to Count a uniqueidetifier field you have to CAST it into a char :

SELECT COUNT(CAST(D.DOCUMENTID AS char(36))) AS DocCount, P.TITLE
FROM DOCUMENT AS D inner join
PROJECT AS P ON D.PROJECTID = P.PROJECTID
GROUP BY P.PROJECTID, P.TITLE

A3 Landscape dashboard layout - SSRS

2011-09-29T10:31:00.001+01:00

For A3 Landscape dashboard style settings:

Report Properties:
Grid Spacing 0.2cm

InteractiveSize
    Width 42cm
Height 0cm

Margin all 0.5cm

PageSize
    Width 42cm
Height 29.7cm

Body Properties:
Size
Width 41cm
    Height 28.7cm

Resolving Common Connectivity Issues

2011-09-05T09:40:00.000+01:00

Useful link to resolving common connectivity issues. Applies to SSAS but also proves to be of use with IIS, SSRS & SQL Server.

http://msdn.microsoft.com/en-us/library/cc917670.aspx

Performance of SQL commands and subreports in Crysal Reports - CR

2011-08-16T10:50:00.000+01:00

I've been recently attempting to imrpove the performance of a number of 'legacy' Crystal Reports. One in question contained approximately 7 subreports with the main report returning 300+ records. I saw this an ideal candidate for using solely a SQL command.

I'd re-written the Crystal using said SQL command and then compared the performance of both existing subreport and new SQL command versions using SQL Server Profiler.

The first image shows the existing subreport version:

The second shows the SQL command version:

I was surprised to see the values returned by the SQL command version which seemed vastly larger than the sum of all the 'subreport' performance values. But, on closer inspection I realised that the subreport was only returning data for the first page in Crystal Reports whereas the SQL version was gleaning all the database info in 1 go.

The below image shows the performance of the subreport version when the user refreshes the report on page one and subsequently moves to page two (page one returns two main report rows whereas page report returns three).

As this report contains over 70 pages it is plain to see that using the subreport version the database would be queried multiple times and incur all the related network traffic. It is thus beneficial to go with the SQL command version (as originally expected!). Summing up all the CPU and read and writes also confirms this.

One concern that I've been unable to resolve is that Crystal Report seems to create two SQL batches when using a SQL command thus doubling the performance overheads. The same query when run in SQL Server Management Studio records only 1 SQL batch as expected. I need to look into this as I'm not fully sure why this is occuring.

Using SQL commands, parameters and subreports - CR

2011-06-03T09:57:00.001+01:00

My report contains a number of subreports, each comprising a SQL command and using a parameter value passed from the main report.

1) Set up the main report parameter, in this instance called ?School

2) Add a subreport, Add Command with the Where clause as follows:

WHERE (School.SchoolKey = {?School})

3) For the Parameter list, Click Create:

Parameter Name = School
Value Type = Number

It will then prompt for a parameter value, enter 1 (anything will suffice at this stage)

NOTE: When the database field is a uniqueidentifier, use 'String' type and for the prompt use a value in the uniqueidenfier format: {29D2B899-393A-4592-9B00-D289884A1F94}

4) Back in the main report, right click the subreport and Change Subreport Links:

Available Fields = ?School
Subreport parameter field to use = ?School (make sure to select the user-generated one and not the automatically generated one named similar to ?PM-?School)

5) Voila! Main report parameters should now be passed to SQL Commands in subreports.

Display parameter Label field - SSRS

2011-03-16T13:10:00.000+00:00

I was familiar with displaying a parameter's value field by using the expression:
=Parameters!parameterfield.Value

But in many cases this value will be a key field and pointless to display. The 'Label' field is required and is obtained by simply changing the expression to:
=Parameters!parameterfield.Label

Simple and obvious really!

Return a random row - SQL

2011-03-09T15:39:00.000+00:00

Instead of using the RAND() function which I believe is not truly random since using the same seed value results in the same output:

Whereas, CHECKSUM(NEWID()) would work:

and can thus be used to return a random row:

SELECT TOP(1) School.SchoolNameFROM School
ORDER BY CHECKSUM(NEWID());

Nob Hill Software - Useful information we can get out of a query execution

2011-02-15T15:59:00.000+00:00

Here's a guest article by Nob Hill Software. http://www.nobhillsoft.com/

Much has been written about Query Execution Plans (we’ll call them QEPs), and how they can be used for analyzing how SQL Server interprets your TSQL code and what it does behind the scenes –
information you can use to do such things as find bottlenecks, build indexes where they might help, and generally improve the performance of your code.

Well, we have found more uses for it. There is more information in the QEP, information that’s not typically used, yet is highly useful and very easy to retrieve:

1. IS YOUR CODE BROKEN?

TSQL code can get ‘broken’ all the time. You might be referencing tables or columns that don’t exist.
They might have even existed at the time you wrote the procedure, but have been removed since, leaving your procedure ‘broken’ - It will raise an error next time its executed. How many of those do YOU have in your database?

Now, if a code is broken, SQL Server will let you know about that when attempting to retrieve its execution plan. Strictly speaking, this is not QEP information. Its information you will get while trying to get the QEP. And that is not the only way to get that information. But it’s the easiest.

2. WHAT ENTITIES ARE USED IN FOR WHAT OPERATION?

Among all the logical and physical operations, and effects on memory etc, QEP also contains the very basic information of what entities it is working with, and what it does on such entities. This information can be then used to build a basic ‘browser database’, which you can use to answer questions like: which code inserts to this table? I got data missing… which procedure removes records from here?

In order to get any SQL’s execution plan in SQL Server, all you need to do is:

set SHOWPLAN_ALL on
write your SQL here
set SHOWPLAN_ALL on

If you get an error, this means the stored procedure is broken, and the error would say why.
Otherwise, you can get the execution plan in a tabular format, and retrieve the specific entities used from it.

We put this functionality into one of our freebie products, Diana Lite. You can build the full ‘browser database’ out of the collection of any database’s SQL Code, then easily filter it and get to whatever you are looking for:

Sweet, Huh?

http://www.nobhillsoft.com/Freebies.aspx

FInd current financial year start and end dates - CR

2011-01-19T15:06:00.003+00:00

To find the start date and finish date of the current financial year for subsequent use in various calculations:

Start of year formula:

Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar startfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN startfinancialyear := (DATESERIAL (YEAR(dates)-1,4,1))
ELSE startfinancialyear := (DATESERIAL (YEAR(dates),4,1));

End of year formula:

Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar endfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN endfinancialyear := (DATESERIAL (YEAR(dates),3,31))
ELSE endfinancialyear := (DATESERIAL (YEAR(dates)+1,3,31));

Alteration: The End of year formula gets the last day of the financial year, i.e. 31st March. However, it returns the value for the very start of the day (31/03/xxxx 00:00:00) which when incorporated into a selection formula could potential lead to records logged during that final day being excluded.

A correction would be:

End of year formula (correction):

Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar endfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN endfinancialyear := DATEADD("s" , -1, (DATESERIAL (YEAR(dates),4,1)))
ELSE endfinancialyear := DATEADD("s" , -1, (DATESERIAL (YEAR(dates)+1,4,1)));

Thus, returning a value of "31/03/xxxx 23:59:59"

(Currently set up for use in the UK)