T-SQL Tuesday #50 – Automation Equates To Saved Time

SqlTuesday50Hemanth D (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009 and we hit the 50th “episode” this month. Could this be coincidentaly occurring during the 50th anniversary of Doctor Who?? I think not…

Anyhow, the topic this month is automation.

I can play that in 3 notes

There used to be a game show called Name That Tune. A contestant had to underbid the other contestant on what the minimum number of (musical) notes they needed in order to recognize a piece of music. Automating tasks remind me of this game show. First, you automate step one, then step two. Each time you tweak it and tune your automated process, you’re seeing just how far you can go… and how little manual work you are left with.

The first step

I’m going to talk about automating the first step of creating an SSRS report. I’ll be working with SSRS 2012, but the steps are the same for 2008 and 2008R2. The only difference is WHERE you store the templates so that they can be easily leveraged. Since that is the last thing you do, you’ll have to wait for the end of this post. So, hold on to your Tardis, and let’s go!

The first thing you need to know, is what are all the common elements to all of your reports. Here is a list of the elements I found to be the same on my reports:TSQL Tuesday 50 - Image 1

  • The location of the title: While my title was different each time, the font, font color, font size, and location remained static. So I created a placeholder for my title.
  • The company logo: The company logo was static as well as its location.
  • The color scheme: Since I used the same five colors, I created a temporary “pallet” for my colors. This was a mini Tablix control, with each of the five cell’s background color set to a different color in my pallet. I left it on the report until I was done setting all the properties of the report, then I deleted the Tablix. (No more looking up the colors in my documentation. Win!)
  • The location of the parameters: I personally think that the parameters should always be displayed on the report. This helps when troubleshooting a paper/pdf copy of a report. It also lets the users know the boundaries of the data they are looking at. (Note: While not depicted in my image, I put my parameters under the logo.)
  • The company address: Static location.
  • The confidentiality notice: Static location.
  • The page numbers: Static location.
  • The report identifier: This is a special number that helps you identify your report. Mine always has three parts.
    • TX or DW to mark the report as having transactional or data warehouse based data. This allows me to speak intelligently about a report that someone shows me in a meeting, especially since our data warehouse data was always older than our transactional data.
    • A number that corresponded to the documentation for the report. In our case, it was the TFS (Team Foundation Server) number.
    • An iteration number. This iteration number was specific to how many times the report was re-introduced into production. This allowed me to verify that the user was looking at the latest copy of the report, and it allowed me to document how many times the owner had requested changes to the report.
Creating the templates

A template is an RDL file saved in the templates folder. I created three templates for my team. Each template was identical to the others, except for two things. The paper size and orientation. I needed to make different templates to accommodate these attributes so that the controls that were centered or right aligned ended up in the correct location for viewing and printing.

  • SSRS 2008(R2) Location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
  • SSRS 2012 Location: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But wait! There’s more

If you want to go a bit further with your templates, you can add a watermark that can be leveraged during development all the way through user acceptance testing. The watermark will then be suppressed in production. The watermark says DRAFT. I found it helped with certain end users, who were getting caught up in the data (even though it was fake) and they weren’t focusing on the design and algorithms present in the report.

Here are the steps to add the dynamic watermark:

  1. Create a table in the database that contains the name and ID of the environment you are in, plus a parameter that dictates whether to show or hide the watermark. Note: You can also use this table to point to development file locations for documents and development URLs referenced in your reports.
  2. Add an image to the background of the report body that says DRAFT.
  3. Set the Background Repeat property to Repeat
  4. Create a data source in the report that points to the environment table you created in step 1 (preferably through a stored procedure).
  5. Use the data source to hide or show the background image of the report.

If you use shared data sources, then add the data source to the template directory so that it can be added to new projects the same way report templates are.

There you have it! You just automated quite a few (initial) steps for creating SSRS reports.

Thanks for all the fish

Thanks go out to Hemanth for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://sqlchow.wordpress.com/.

7 comments

  1. Favio says:

    Hello there, during the SQL.la presentation you mentioned you had the presentation word document, etc. in a share location (i think a skydrive) and you said we could download your Report Word Document which you recommended to use to gather all of the requirements. Can you share the link? Thanks!

  2. I like your color palette trick 🙂 Wouldn’t it be great if SSRS had a Format Painter button, just like any Office application already has? That would save us so much time! Ow, this reminds me, when I came back from PASS two years ago I wanted to submit a suggestion for this on MS Connect. But someone had already done that. The item is in need of extra attention though, here it is: https://connect.microsoft.com/SQLServer/feedback/details/310035/ssrs-report-designer-vs

    Hopefully this gets it a couple more votes! 🙂

    • SQLMickey says:

      Thanks Valentino. I wish I would have thought it up years ago. It would have saved me so much time. I love the Format Painter button idea! Thanks for the connect link. I will definitely be voting for it!

      Mickey

%d bloggers like this: