SSRS: Use Stored Procedures in Datasets

This is my contribution to T-SQL Tuesday #24 hosted by Brad Schulz (blog) on the subject of Prox ‘n’ Funx (Stored Procedures and Functions to you and me :-) ).

I'm a big believer in using Stored Procedures (or at the very least, UDFs) for your Reporting Services datasets. and separating your presentation layer from your data layer and moving the SQL code away from the RDL.

The benefits of this are that you as long as the meta-data of your Stored Procedure stays the same, then you able to modify and enhance your SQL code without having to touch the RDL. You essentially abstract away the source code from the report.

Perhaps you're improving performance by moving to JOINs from cursors, extended the business logic to only return rows that meet new criteria or simply doing a refactoring of SQL code to standardise your table names. All of these don't affect the presentation layer and having them reside as Stored Procedures on the database, gives huge maintenance benefits.

Other advantages include having all your T-SQL held in the one place and knowing that you are aware of the impact of any changes without having to worry about dependancies elsewhere. Also, you will often be re-using code (eg for parameter datasets) and using a single stored procedure helps reduce duplication of effort (and probably performance benefits too).

Of course, there are downsides to this approach. If you need to introduce a new parameter to a report (which is passed to your dataset) then you have to change both the RDL and the stored procedure. I can see this being a slight irritation as you now have 2 deployments whereas holding the SQL code "inline" means a simple upload of the new report.

For me though, the former approach still wins and I advocate using Stored Procedures for Reporting Services datasets. I've been experimenting recently with putting Stored Procedures used for Reports into their own schema (acting as a namespace) although I can't categorically say whether this has been a success or not (Jamie Thomson (Blog | Twitter) has an interesting blog post which touches on Schema usage here).

SSRS: Full report not rendered via ReportExecutionService

I've come across an issue when using the ReportExecutionService to render a report through a .Net App using code similar to that demonstrated here.

Essentially, I'm trying to render a report which contains 3 layers of nested subreports all using different datasets. When viewing the report through the SSRS report viewer, you get a question mark on the paging section of the toolbar:



This question mark is a result of the On-demand processing engine introduced in SSRS 2008 and basically means that the report is rendered in stages, giving a potential performance gain on large reports. The question mark indicates that it knows there is more to come, just not sure exactly how much.

So, as I understand it, if you have a report with 2 subreports accessing different datasets - only the first one will be run to minimise the data sent to the client. When user starts navigating through the report and comes to the end of the first report, the rest of the report will be rendered. Rob Bruckner explains more here.

All well and good from a performance perspective and typically doesn't cause any issues apart from the strange question mark appearing (even this can be worked around) and users just navigate through the report. However, when trying to access it through the webservice, the same behaviour is honoured and only the first section of the report is rendered - equivalent to the first page when viewing through the SSRS report viewer. This is incredibly frustrating as I know that the report is over 3 pages long but I only ever get the first page in my deliverable. Worse still, there doesn't seem to be a way to override this behaviour (its the same in R2 I believe) and something i'd like to see addressed by MS (on a report by report basis).

At first, I was stumped as even using the TotalPages workaround referenced above, I couldn't get the full report to render. But, then I took a closer look a the "master" reports and noticed that they all had orphaned data sources defined - a legacy from when the reports held definitions rather than subreports. As a last shot, I removed these from the reports as they weren't needed at all and was delighted to see that the full report now rendered correctly through the webservice.

I'm not clear exactly why these orphaned connection strings were causing such an issue but all I know is that clearing them did the job.

SSRS: Using SSIS as Data Source - a gotcha

Although using SSIS as a data source for SSRS isn't a supported configuration in SQL Server and has indeed been deprecated from 2008R2, i've been trying to make use of it for a particular scenario (using SQL2008). Its not the most straight forward of processes as it requires changes to config files, careful use of dataset names and plenty of security jiggery pokery but i'm not going to touch on that here.

The issue I came across was due to me having SQL2005 on my machine previous to upgrading to 2008. When I developed my report with SSIS as the datasource in BIDS, everything worked perfectly and it was only when it was deployed to the report server that I got the error message:


Initially, I thought it was a permissions problem (as thats what the majority of issues appear to be using this configuration) but after exhausting all other possibilities and actually READING the error message it became apparant that the issue was a little more fundamental than that. Logging had shown that the package wasn't even being executed and the message bears that out. The issue seemed to be more that the command being passed to the SSIS engine wasn't correct. It was then that i'd remembered that I'd upgraded and there was a good chance that it was trying to call my SQL2005 version of DTEXEC. Fortunately, i'd seen another post from Jens which suggested how the problem might be fixed.

Hey presto, changing the version of the SSIS extension element in rsreportserver.config did the trick (with a restart of the SSRS service).



TO:



So I may still have plenty of security issues to resolve and as the feature is no longer supported in SQL2008R2 and above, I doubt whether I'll go ahead and use it in Production but hopefully someone may find this useful.

SSRS: SQL2008 RDLC

I thought i'd experiment with writing a WPF app which can expose some SQL2008 reports that i've developed. Notwithstanding the workaround required to use ReportViewer in WPF (see here) it appears that due to the timeline of when VS2008 and SQL2008 were developed/released, you can't use ReportViewer 2008 with SQL2008 reports!! See the quote below from this forum thread:

"Visual Studio 2008 was released much earlier than SQL Server 2008, so ReportViewer 2008 is based on the 2005 version of RDL. A SQL Server Reporting Services 2008 server report is based on the 2008 version of RDL, so, it can't be degrade to 2005 RDLC".

Bit of a pain, but it sounds like this should all be fixed up in Visual Studio 2010.

SSRS: Format Date Parameter Labels in RS

Recently creating a report with a dataset populated parameter list with INT as the value and DATETIME as the label.

I wanted to have a sensible title along the lines of: Report for date: DD/MMM/YYYY rather than the full nasty datetime. Easy i thought. Just use Parameter!MyDate.Label in a textbox and wrap it with a Format expression. To my chagrin however, i could not get this to work. Doing the following:

=Format(Parameters!MyDate.Label, "dd MMM yyyy")

resulted in a display of dd MMM yyyy. Not what i wanted at all.

Although the label is a DATETIME in the dataset, i wondered whether it was being converted to a string by RS so i tried to explicitly convert it to a date before running the format clause:

=Format(CDate(Parameters!MyDate.Label), "dd MMM yyyy")

Again, no dice but with #Error.

The closest I have been able to get is to just TRIM the label to the 1st 10 characters, which doesn't give me the exact format i'm after and I don't believe is the most robust solution.

=Left(Parameter!MyDate.label,10)

I've posted on the MSDN forums and hope for a response.