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...