Since my Access days, I've believed in renaming important* controls in reports. Now that I use Reporting Services daily, this practice still holds: ambiguous names like "Textbox1" or "Textbox112" become "ReportName" or "FinalBalance", "table1" becomes "WidgetSalesTable", and "chart1" becomes "MonthlyTrendChart". As well as better organising the report while in development and adding only a small amount of time to actually do, renaming controls is useful to me later when I'm maintaining a report as I can see which controls contain which data points. There's one further advantage to renaming controls in a Reporting Services report: because reports are stored in XML format,...

Recently I investigated a Reporting Services problem where a user first ran a report with one parameter, then switched applications to do some work, then returned to the report and attempted to run it with a different parameter. The end result was that they received an "execution xxx cannot be found (rsExecutionNotFound)" error. I too see this "execution cannot be found" error intermittently, sometimes after coming back to a report I'd run earlier and clicking "Back" or trying to expand a section on the report, and sometimes when my browser loads up tabs from a previous session. And The Reason Is You Behind...

As I discussed last week in part 1 of this article, color can be used to highlight common elements across groups. As promised, here's the necessary code to achieve this effect in Reporting Services 2005 (or you can download the finished report here): 1. Create a new report, and add a query. I've used AdventureWorks and a simple query that returns employees and years of service: --get employees by Department, with years of service from AdventureWorks SELECT TOP 50 D.[Name] AS [DepartmentName], EMP.[EmployeeId], C.[FirstName] + ' ' + C.[LastName] AS [EmployeeName], ...

Often a report must be able to be grouped by one or more categories, depending on the user's requirements. Sometimes, across groups, it's helpful to highlight common elements. Imagine that the illustration below lists employees by department (the main grouping), with the last column being a common element to all employees: It's difficult to identify common elements across the main group for the last column in figure 1, even though values like "AAAAA" and "ZZZZZ" are repeated. In fact, it's not possible to draw any conclusions about the last column (like how many employees share the common element "ZZZZZ") without physically scanning...

Microsoft's David Lean has a great series of posts on "heatmapping" and other color-based conditional formatting in Reporting Services tables and charts (link is to part 4 of the series) (via Robert Bruckner).   Particularly useful are Dave's algorithms that work on colors and shades and conversion to hex values; part 2 of the series has how to change hue, while part 3 details how to change saturation. And, Dave packs as much useful content into his tips and asides as his main content! Tags: reporting services, sql server, color

Fellow Aussie Grant Paisley has set up a site called "Report Surfer" where you can view and share sample Reporting Services reports. The site covers Reporting Services 2005 and 2008, with a growing list of techniques demonstrated and comes in handy for viewing reports in the "report packs" from Microsoft, without installing. Reports can be run on the site or downloaded; free registration is required. The site is in beta and looks like it could be a worthwhile resource (including a list of Reporting Services bloggers in the sidebar, which I'm reading now). Tags: reporting services, sql server, sample

Reporting Services offers two "out of the box" methods to manage subscriptions (subscriptions are scheduled report executions, controlled by Reporting Services): Each report has a "Subscriptions" tab (depending on permissions) which shows your/all subscriptions (again, depending on permissions) for that report only All your subscriptions can be managed using the "My Subscriptions" link in the top right-hand corner of Report Manager There's one key thing missing from the two methods: how does an administrator or power user manage all subscriptions in Reporting Services? The Query I'm going to provide a starting point for a better way...

A requirement for the reporting portion of an upcoming system of mine is that the Y-axis of charts can start at 0, or a defined number, or by letting the chart decide (automatic). This, like many of the requirements for this particular system, is because of the way Excel charts work. Dynamically setting the minimum axis in a Reporting Services chart can be done by typing an expression in the "Scale", "Minimum" box (for SSRS 2005 and greater only). Note you don't have the benfit of the expression editor: In the above image I've passed the minimum with the dataset. I'm sure...

Hiding and showing columns is simple in Reporting Services - each column has a "Visibility" property that can be set using an expression. The expression could be driven by a parameter, calculation or data and only needs to resolve to "True" or "False". One way I use to hide and show columns is allowing the user to select which columns to show using a multi-value parameter (to do this, you must be running at least Reporting Services 2005 which introduced multi-value parameters). Here's my method, step-by-step: Step 1 is to set up the parameter. In the "Parameters" dialog, add a new parameter...

The Reporting Services Catalog table in the ReportServer database contains the RDL XML in the Content column, serialised to binary format. I had an issue recently where I needed to retrieve the RDL XML from the Content column from a backup of the ReportServer database using SQL Server 2005. Here's the code that did the job: SELECT [Name], CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML FROM ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK) WHERE --get only reports, not folders or other resources [Type] =...

I haven't posted anything on Reporting Services recently. So, I have two links for the infamous matrix control: Advanced Matrix Reporting Techniques by David Leibowitz on Red Gate's Simple Talk site How to format the subtotals of a Reporting Services matrix differently, using InScope by fellow Aussie and SQL Server MVP Rob Farley Because I feel I need to compensate :-) Tags: sql server, database, reporting services, matrix

In Reporting Services, the syntax for changing the text color of a table cell, say to red if the value is negative, is to set the "Color" property to this expression: =IIf(Me.Value < 0, "Red", "Black") Another useful formatting tool is changing the background color of a cell depending on the cell's value, perhaps to achieve a "heatmap" effect. I find it's far better to do this using code than the macro-type language I used above. Setting the background color in code can be achived by putting an expression like the following into the "BackgroundColor" property, replacing the section in square brackets with...

Reporting Services puts parameter selection before the top of the report, with (at most) 2 parameters on a row. If your report has a lot of parameters (my example below has 5), the parameter section can take up a lot of room: As an aside, having multiple parameters is better than having multiple reports which differ only by a WHERE clause in the database. Parameters are your friend - it's just the parameter display (which is not configurable at all) that I'm trying to look at here. So taking up a lot of screen real estate with parameters is a bad thing....