SSRS: Identifying Subreports From Report Catalog With XML Query

While running through our SSRS execution log as part of our spring clean, we noticed that reports which were embedded as subreports were not being included in the figures. While this avoids double-counting in the execution log, it muddies the water a little when you're looking for reports that have fallen out of use. To avoid automatically flagging these reports as unused, we looked for a way of identifying subreports from the XML data in dbo.Catalog.

Viewing the ContentXML values in SSMS's built-in XML editor helped me to set up our query.

The XML query I came up with is made up of two parts: the node query, which brings back a list of ReportName nodes and a value query which pulls the text value from each of those nodes.

Subreports can be embedded at any point within a report, so the simplest way to locate them is to use a double-slash prefix in the node query to search the entire document ('//*:Subreport/*:ReportName'). The asterisks are included to avoid problems with namespaces. The value query only needs to pull the text from within the ReportName tag, which is identified with '.'.

Here's the complete query:

-- Pull the RDL contents as varbinary(max) WITH ItemContentBinaries AS ( SELECT ItemID, [Name], [Path], CONVERT(varbinary(max), [Content]) AS [Content] FROM dbo.Catalog WHERE Type IN (2, 7) -- Report or Report Part ) -- strip off the BOM if it exists , ItemContentNoBOM AS ( SELECT ItemID, [Name], [Path], CASE WHEN LEFT(Content, 3) = 0xEFBBBF THEN CONVERT(varbinary(max), SUBSTRING(Content, 4, LEN(Content))) ELSE Content END AS Content FROM ItemContentBinaries ) -- convert to XML , ItemContentXML AS ( SELECT ItemID, [Name], [Path], CONVERT(xml, Content) AS ContentXML FROM ItemContentNoBOM ) -- query the xml doc for path /Subreport/ReportName SELECT [Name] AS ReportName, Subreport.value('.', 'nvarchar(max)') AS SubreportName, REPLACE([Path], [Name], Subreport.value('.', 'nvarchar(max)')) AS SubreportPath FROM ItemContentXML

CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Subreport/*:ReportName') AS Subreports(Subreport)

SSRS: Using a Lightbox to Display Drill-Through Reports More Effectively

A lightbox is a web page element which acts as a popup window without moving the user away from the original page. When a user clicks on a lightbox-enabled link, the page content is dimmed and a new container is overlaid, showing the new content.

A lightbox-enabled site, before and after a link is clicked

Lightboxes are commonly used on image sites to produce popup image galleries and used more generally to produce login prompts and dialog boxes. They enhance user experience by keeping focus on the original content and they emphasise the link between the source page and the new content.

I've used JavaScript actions in my SSRS reports to show drill-through content in a new window for a long time (as described here: http://www.sqlchick.com/entries/2010/8/22/opening-a-link-in-ssrs-within-a-new-window.html), but it occurred to me that using a lightbox would make for a more elegant solution.

A technique called Javascript Injection can be used to insert a reference to an external javascript file into an SSRS report. I first read about this technique here: https://blogs.infosupport.com/reporting-services-javascript-injection/. In this case, I've used this technique to load a copy of JQuery, along with a lightbox plugin called Fancybox (http://fancybox.net), into the report page. Once these files are loaded, a second code segment feeds in the URL I want to display and activates the lightbox.

A demonstration of the lightbox in an SSRS report with closing transition

I've configured Fancybox to close with a shrinking transition. When the lightbox is closed, it shrinks down in the direction of the source link and the text of the link is highlighted. This helps the user remember where the lightbox originated.

Try it Yourself

I've included a link below to a zip file containing all the files required to reproduce this lightbox effect. I've also included a set of instructions detailing how to use them in your own project.

Zip file link: https://drive.google.com/file/d/0B0hUyvkAteJkQTJheVZtcTFSNk0

Instructions:

• Unzip the file onto your web server. In this example I'm using a folder called /scripts, sitting at the root level. If you've used a different folder, you'll need to change the JAVASCRIPTPATH and CSSPATH values at the top of the loadpopup.js file.
• Open your RDL file in your editor of choice (I'm using VS2012), add a Go to URL action to a textbox and click the expression button. 

• Paste in the following code:

="javascript:scriptelem=document.createElement('script'); scriptelem.id='loadjs'; scriptelem.src='[[path to loadpopup.js]]';"
       & " document.getElementsByTagName('head')[0].appendChild(scriptelem); var carryon = function(){pagePopup(document.activeElement, '"
       & [[target url]]
       & "');}; scriptelem.onreadystatechange = function() {var r = scriptelem.readyState; if (r === 'loaded' || r === 'complete') "
       & "{carryon(); scriptelem.onreadystatechange = null;}}; void(scriptelem.onload = carryon);"

• Replace [[path to loadpopup.js]] with the path to your loadpopup.js file (in my case, /scripts/js/loadpopup.js) and replace [[target url]] with the page you would like to display in the popup. This URL can include field values.

NB. The target URL must point to a page on the same server as your RDL. Modern web browsers (at least the ones I've tested) don't allow scripts to use cross-site references.

• Deploy the report to the server (The lightbox links will not work in preview mode.)
• Test it out! If the textbox with the action does not link anywhere, check the expression in the URL action for syntax errors. If it does contain a link but the lightbox still isn't showing, bring up the browser's debugger (by pressing F12, usually) and check the output window for clues as to what the problem might be.

I've tested this technique in IE8 to 11 and Chrome v26 onwards. I'd be interested to hear how you get on implementing this on your own instance. Happy to take any questions below.

Visualising the Complete Patient Pathway

When a patient spends time in hospital, they interact with many different departments and the data related to each of these interactions can be recorded on many different electronic systems. These systems usually share a unique identifier to identify the patient, such as their hospital number or NHS number, but very rarely share spell or appointment identifiers. This can make it problematic when trying to piece together the full patient pathway.

To solve this problem, I import the records from these systems into a single simplified dataset called PatientEvents. The schema for this dataset consists of a patient identifier, an event type identifier, the date/times the event started and finished, and a freeform description field.

Event Type	Start Date/Time	End Date/Time	Event Description
A & E Attendance	12 Apr 2017 10:04	12 Apr 2017 11:47	Service point: Ambulance Triage Primary Diagnosis: Dislocation/fracture/joint injury/amputation
Radiology Procedure	12 Apr 2017 11:00	12 Apr 2017 11:06	Specialty: ACCIDENT & EMERGENCY Modality: Radiology Exam Name: XR Finger Index Rt
Spell Admission	12 Apr 2017 11:47	12 Apr 2017 11:47	Admission Source: Usual place of Residence Admission Method: Emergency - Local A&E
Episode	12 Apr 2017 11:47	12 Apr 2017 16:17	Clinician transfer to Smith Z, Acute Medicine Primary Diagnosis: S631: Dislocation of finger Primary Procedure: W663: Primary manipulative closed            reduction of fracture dislocation of joint NEC
Ward Stay	12 Apr 2017 11:47	12 Apr 2017 13:53	Ward transfer to ACUTE MED ASSESS UNIT
Clinic Appointment	12 Apr 2017 13:10	12 Apr 2017 13:20	Consultant: Jones, Z Specialty: Fracture Clinic: FRACTURE CLINIC Outcome: Future Appointment
Ward Stay	12 Apr 2017 13:53	12 Apr 2017 15:33	Ward transfer to AE ACUTE RESUS
Drug Administered	12 Apr 2017 16:00	12 Apr 2017 16:00	Item(s): PARACETAMOL 500 mg Tablets
Spell Discharge	12 Apr 2017 16:17	12 Apr 2017 16:17	Discharge Destination: Usual Place of residence

Arranged in date order, the PatientEvents records can be read like a narrative of the patient's journey. When mapped onto a horizontal timeline, a user can intuitively correlate and cluster events.

Presenting data on a timeline allows a user to quickly summarise the pathway and identify connections between events

A lookup table, PatientEventTypes, assigns each event type a colour and group. This means that, for example, surgical procedures, pre-op assessments and post-op electronic forms can be grouped into the same 'Theatres' row of the timeline, with each one using an individual colour to allow them to be easily distinguished.

The Theatres group combines pre-op assessments (brown), surgical procedures (dark orange) and post-op electronic forms (light orange)

Outpatient appointments are given a pale colour to indicate that they have been cancelled or DNAed

The end result, the Patient Pathway Viewer, is a browser-based application which combines the timeline and ordered narrative concepts on a single page. The timeline can be navigated by dragging with the mouse/finger and zoomed in/out using scroll wheel or pinch. Clicking an item in the timeline highlights that event in the narrative view and vice-versa.

Using the Patient Pathway Viewer, a user can quickly get an overview of a patient's hospital history, identify if they are a regular visitor, and see any future follow-up appointments they have booked in. All within a single window.

The Science Bit

I have designed the Patient Pathway Viewer to be as simple to implement as possible. I've written it using plain HTML/Javascript and it consumes data in a simple CSV format. I've set up a web service to provide this data, but it can be configured to use standard text files. This means that the software and data files can be hosted on any basic web server without the need for server side scripting.

Because the software is intellectual property of Royal Liverpool and Broadgreen University Hospitals Trust, I'm not able to publish the viewer on the web or release the source code, but I've listed the Javascript libraries used below in the hope that it will help you recreate it, should you want to. I'm happy to answer any questions in the comments section.

Library	Description	License
JQuery	Provides much of the cross-browser functionality	MIT
JQuery-CSV	JQuery plugin which handles the conversion of CSV data files	MIT
CHAPS Links Library Timeline	This drives the timeline charts	Apache 2.0
JQuery-Layout	JQuery plugin which fixes layout problems in legacy browsers	MIT
Moment.js	Handles date and time formatting	MIT

SSRS: Colour Coding Charts to Match Excel 2007

Charts in SSRS can be configured to use custom colour palettes. I quite liked the colour scheme used by default in Excel 2007 charts, so I used an eyedropper tool to work out the colour codes it used. Here are the first 16:
            #40699C
            #9E413E
            #7F9A48
            #695185
            #3C8DA3
            #CC7B38
            #4F81BD
            #C0504D
            #9BBB59
            #8064A2
            #4BACC6
            #F79646
            #AABAD7
            #D9AAA9
            #C6D6AC
            #BAB0C9
 

You can replace a chart's palette in BIDS by viewing the XML code of your RDL, searching for the <Palette> tag and replacing it with the following code:
            <Palette>Custom</Palette>
            <ChartCustomPaletteColors>
                <ChartCustomPaletteColor>#40699c</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#9e413e</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#7f9a48</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#695185</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#3c8da3</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#cc7b38</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#4f81bd</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#c0504d</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#9bbb59</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#8064a2</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#4bacc6</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#f79646</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#aabad7</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#d9aaa9</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#c6d6ac</ChartCustomPaletteColor>
                <ChartCustomPaletteColor>#bab0c9</ChartCustomPaletteColor>
            </ChartCustomPaletteColors>

SSRS: Stored Procedures Running Slowly in SSRS

Occasionally, a stored procedure that runs quickly when executed directly can run slowly when used in a RDL dataset. One of the reasons for this can be due to the way SSRS passes query parameters to SQL Server. Unexpected parameter types can cause the cached execution plan to perform poorly.
A solution to this is to force the stored procedure to recompile the execution plan each time it is run, by adding the following query hint to the end of your procedure:

option (recompile)

It's worth pointing out that this will slightly reduce the overall speed of the procedure. So, if your procedure is called very regularly, you might want to avoid using this hint.

TSQL: Printing strings longer than 4000 characters

When you're debugging a dynamic SQL query, it can be helpful help to output the final query onto the message window. The main problem with this is that the PRINT command has a limit of 4000 unicode characters, as demonstrated here:

DECLARE @seqno AS int = 0

DECLARE @sql AS nvarchar(MAX)

SET @sql = 'SELECT ' + CAST(@seqno AS varchar) + ' AS sequence'+ CHAR(10)

WHILE (@seqno < 1000)

BEGIN

      SET @seqno = @seqno + 1

      SELECT @sql = @sql + 'UNION SELECT ' + CAST(@seqno AS varchar) + CHAR(10)

END

PRINT @sql --Limited to 4000 characters.

     

EXEC sp_executesql @sql

The string can be outputted in 4000 character chunks, but because each PRINT command adds it's own new-line character, the final result can appear chopped up and may be invalid. To overcome this, the following method finds an existing new-line character and uses it as a break off point:

DECLARE @seqno AS int = 0

DECLARE @sql AS nvarchar(MAX)

SET @sql = 'SELECT ' + CAST(@seqno AS varchar) + ' AS sequence'+ CHAR(10)

WHILE (@seqno < 1000)

BEGIN

      SET @seqno = @seqno + 1

      SELECT @sql = @sql + 'UNION SELECT ' + CAST(@seqno AS varchar) + CHAR(10)

END

/* Print entire statement */

DECLARE @SqlLength int = 0

DECLARE @CutOff int = 0

WHILE @SqlLength < LEN(@sql)

BEGIN

      SET @CutOff = 4000 - CHARINDEX(CHAR(10),REVERSE(SUBSTRING(@sql, @SqlLength, 4000)))

      PRINT SUBSTRING(@sql, @SqlLength, @CutOff)

      SET @SqlLength = @SqlLength + @CutOff + 1

END

/* END Print entire statement */

     

EXEC sp_executesql @sql

SSRS: Dynamic Datasets in Excel using SSRS

I recently came across a requirement for an Excel template which would contain a dataset which could be refreshed by the end user on demand.

For security reasons, we could not allow the user direct access to the sproc on our reporting database. Instead, I discovered a way to pull data into Excel from an SSRS report. This gives the benefit of using an already established reporting outlet with its own built-in security model.

To set this up (in Excel 2007), open a new sheet and go to the 'From Web' option in the Data ribbon. In the address bar, enter the URL of your report in the following format:

http://SERVERNAME/reportserver?REPORTPATH&rs:Format=html4.0&rc:Toolbar=false&rs:Command=render

(Note the string of options at the end of the URL (&rs:Format=html4.0&rc:Toolbar=false&rs:Command=render). These are necessary for the link to work correctly.)

eg: http://59.145.122.172/reportserver?/Report+Project1/Report2&rs:Format=html4.0&rc:Toolbar=false&rs:Command=render   [This server has nothing to do with me. Just some public SSRS server.]

Report parameters can be specified in the URL string by tagging them onto the end. eg. &StartDate=2011-01-01

Next, click the table selector attached to the main data table and click Import.

That's it. This dataset can now be refreshed by anyone who has access to the report.

*** One thing to point out is that the complete dataset must be visible on the first page of the report. To make sure of this, open up the RDL, go into the tablix properties and enable the 'Keep together on one page if possible' option.