SQL Server Reporting Services

Here are the slides from my presentation.

SQL Server Magazine is hosting an online conference (free!) to prepare users for the release of SQL Server 2008.  I will be presenting on SSRS 2008 and SSRS integration with SharePoint.

http://events.unisfair.com/index.jsp?eid=157&seid=291&code=120707head

Half my presentation is SSRS 2008 and half is integration with SharePoint.  It is a lot of content to cover in 60 minutes, but I feel like it gives a good overview into the features and benefits of each.  After working with SSRS integrated with SharePoint for a while now, I'm just as excited about showing it off as I am showing the new features of SSRS 2008.

More often than not, I find clients use the out of the box UI for executing reports. That means the experience of selecting parameters is entirely up to built-in interface. One of the great headaches with this (and there are a couple, like the size of the parameter value controls) is when your parameters refresh without good reason. There aren’t any cascading parameters on the screen or the parameter that changed has no dependency to other parameters.

What goes on behind the scenes to cause parameter refreshes is a bit of mystery. DateTime parameters are one of the prime culprits for causing an unwanted refresh. When you have a datetime parameter that has its default value set by using an Expression as one of the first parameters, it causes parameters to refresh when any subsequent parameter is changed. The way to resolve this is to change the default value to be set from a dataset rather than an expression.

Go from this:

To this:

I’ve had to run a few SQL Cumulative Hotfix patches to solve a few problems in my MOSS Integrated SSRS Environemnt like this:

http://support.microsoft.com/kb/942530

Or

http://support.microsoft.com/kb/936253

My warning is to make a backup of your rsreportserver.config file before you actually apply the hotfix. In both circumstances, the <DATA> region was reset and I lost the entry I had in there for my custom data extension.

One of the deficiencies of SSRS is the lack of metadata capabilities for creating a taxonomy around reports. For example, the only pieces of metadata you can tag a report with are the RDL File name itself, the ‘Author’ and a ‘Description’. What makes this worse is that Author and Description are stored in the RDL and are not available for display on the report. I’ve worked with several clients that have developed their own databases to store additional metadata about reports and created a user interface for entering the data in these report metadata repositories. They want to track information about a report such as version, description, primary data sources for the report, business owner and internal report names. Generally, they display much of the metadata from their home grown system on the report itself, which often can enhance the user experience and with supporting the report. This metadata is often valuable to report consumers and to IT departments troubleshooting and tracking their reports.

Using MOSS 2007 integration with SSRS, the same thing can be accomplished without creating a custom database and interface.

In MOSS integrated mode, reports are stored in a SharePoint document library. Storing RDL in a document library gives you the same document management capabilities any document (such as Word) in SharePoint would have. For example, in the document library where you store a set of reports, you can easily turn on versioning as well as add both built in SharePoint columns and custom columns to track additional information about your report. Built-in SharePoint columns such as Report Description, Report Category, and Report Title are all valuable pieces of metadata to track for your report. You may want to add custom columns like Department, Data Source or a Unique Identifier.

Once you have determined the metadata you want to track about your report, each individual report can be tagged with this metadata. You can even create a template for the document library and use that template for any library where you store your report.

After you’ve tagged your reports with various metadata, it is available to be crawled by MOSS Search and displayed in a user's search results. But how do we display the metadata on the report? Wouldn’t it be great to show automatically incremented version numbers on a report? Somehow, we need to query the document library and display the report’s metadata stored in the library on the report at execution time.

There are several ways to use a SharePoint list (aka – a document library) as a datasource for the report. I use a third party component from Enesys to achieve this. The tool supplied by Enesys is easy to set up and configure and easy to integrate into your reports. Once you have a dataset querying the report’s parent document library, you can display the metadata on the report.

Document Library:

Report Dataset:

Display on the Report: