Problem I have a report that lists all databases for which a full database backup has not been applied in the previous 24 hours from the time the report is run. When I originally created the report I had a manageable number of databases to support and I was the sole DBA. The environment has grown significantly since then. Furthermore, I now have a junior Database Administrator that will eventually be responsible for making sure backups are completing as required. When I was just in the DBA role I knew which databases were important and needed to have backup failures addressed immediately and which databases were test or training versions, or were of lesser importance. In short, I need to upgrade my report to provide a triage mechanism to responsible staff members that are not as familiar with the SQL Server environment as I am. How can I do so?
Solution Let's start by taking a look at the initial design of the report as shown below. As you can see, due to the length of the report it may be difficult to discern which database backup failure I should look into first, unless I have a first-hand understanding of each SQL Server database (server and database names have been changed to protect the innocent - and myself).
To address the need, I came up with the idea of adding a priority field to a couple tables within my SQL Server metadata repository. I added this field on the tables that contain my job, instance and database metadata. I could not simply set one priority status at the instance level, since I may (and do) have databases of varying importance on my SQL Server instances. However, I could not set the priority at only the database level since some SQL Server Agent jobs for a "Very Important" database may not be all that important. I think of the importance of backing up a database versus a job that posts current connection settings to Reporting Services. Finally, I could not simply set the priority at the job level, since there are many uses for reports that require a triaging at the database or instance level that have absolutely nothing to do with SQL Server Agent jobs.
That being said, I added this priority field to tables at the three levels of Instance, Database, and Job that persisted between refreshes of the SQL DBA Repository (the SSIS job that feeds this repository runs three times per day and wipes clean most tables). Once the database table structure was modified to account for importance of a database I incorporated this new field into the data set that serves as the source of the report's data.
The final step, was to add the conditional formatting code to the BackgroundColor and Color properties for the text box that contains the aging of the last database backup. This code looks to the value of the Priority field in the report's data set. Where the value of this field is equal to "High", the text box in the report will show as red with white text for readability. Where priority is equal to Low, the text box will be green with white lettering. Finally, all other values for Priority would show as yellow, with black lettering. Below are two screen shots with the logic.
Now that the conditional formatting is in place and the Priority field has been added to the report's data set, the same report provides better insight towards triaging my response for backup assessment as shown below.
A few commands in Reporting Services goes along way to focus my team on the critical backups that need to be addressed on a daily basis.
This is really nice post. But i am looking for creating report as you have created for backup jobs. I want to create a report that can generate report of the daily backup jobs. some thing similar to you. so can you please provide me the link or suggest me that how can i generate that report. Thanks.
Thursday, October 02, 2008 - 6:24:51 PM - timmer26
Will this same type of conditional formatting work in SSRS 2005? We have MS SQL and SSRS 2005. I am looking to create a heatmap using this conditional formatting technique. All other articles related to heatmaps reference using a custom dll, but your conditional formatting technique works just fine for me if it works with 2005. Thanks for your help and the very useful information provided!
Tuesday, December 09, 2008 - 2:22:59 PM - timmer26
I am using the BI Dev Studio report designer for VS 2005 (ver 8.0). I do not have access to the latest version. I have seen the steps to create this solution and they appear to be using the latest version of the report designer which has the ability to apply a function to determine the background color. Do you know how I can set the background color using a function to conditionally format the field's background color in the older version?
Friday, February 11, 2011 - 12:51:46 AM - Vikash Kumar Singh
I am trying to schedule a report in SSRS 2008 R2 which runs every hour.Evry time time it runs, the report may or may not have data. I need to email the report when the report got data in it. Is it possible to do it in SSRS?