U2U Blog

for developers and other creative minds

Building custom reports for SQL Server management

Recently I presented a session at the ITPROceed conference on ‘Administration intelligence’: using Business Intelligence solutions for analyzing log files, generate documentation, do disk quota estimations and other typical administration tasks. You can find the full presentation at SlideShare.

In this blog post, I want to focus on one of the techniques explained in this talk: creating Reporting Services reports to run in Management Studio.

SQL Server Management Studio reports

SQL Server Management Studio contains a bunch of predefined reports. In the Object Explorer, right click any database, select Reports –> Standard Reports and you get a listing of nearly 20 predefined reports:


If we select any of these reports, a new tab open in Management Studio in which the report renders. There are a few special things to notice about these reports:

  1. They can be exported in PDF, Word and Excel format, as well as being printed
  2. It can be a multi-page report. There is no explicit way to go to the next or previous page, but if you scroll past the end of the current page, you go to the next page.
  3. The report is context sensitive: if you open the same report by clicking on a different database, it shows information for that specific database.

Before we dive into building our own reports, I just wanted to point out that we have these predefined reports not only at the level of databases, but also at the server instance level, as well as on the Data Collection node (Management), the Integration Services Catalogs (from 2012 on) and the SQL Server Agent node. Just go ahead and try these predefined reports: They provide very useful information without the effort of building reports yourself… plus they might inspire you in building your own reports.

From report consumer to report producer

These predefined reports can make our life easier, but sometimes you want a report different from these predefined reports.Building a custom report is easy.

Step 1: Retrieving the data

The first (and often most difficult) step in creating any report is in retrieving the right data. Luckily SQL Server provides tons of information via the many management views. Just start typing SELECT * FROM sys. and the completion tool will show you the huge list of possible completions. If you want to read more on these before you continue, checkout the web, e.g. mssqltips.com has some interesting reading material for you.

In this example we want to report on log file sizes: what are the biggest log files I have on any server, and home many room for growth (or shrinking) do we still have in there.

A query with which we can get started is this one:

FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Log File(s) Size (KB)'
,'Log File(s) Used Size (KB)'
AND instance_name <> '_Total'
This returns a table such as this:
But in reporting it is important to present the data as easy digestible as possible. It is easier to build reports if different measures (in our case log size and log used size) are in different columns versus all in one single column (as they are right now). The PIVOT statement in T-SQL might help us out on that. Also, simple resorting and renaming might improve the readability of the result. So after a bit of a rewrite we end up with this query:
SELECT [Database Name]
,[Log File(s) Used Size (KB)] AS LogUsedSize
,[Log File(s) Size (KB)] AS LogTotalSize
SELECT RTRIM(instance_name) AS [Database Name]
,cntr_value AS [Size in Kb]
,RTRIM(counter_name) AS [Counter Name]
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Log File(s) Size (KB)'
,'Log File(s) Used Size (KB)'
AND instance_name <> '_Total'
) AS src
PIVOT(SUM([Size in Kb]) FOR [Counter Name] IN (
[Log File(s) Size (KB)]
,[Log File(s) Used Size (KB)]
)) AS pvt
ORDER BY [Log File(s) Size (KB)] DESC
Just copy the query in the clipboard, we will need it for the next step:

Step 2: Building the actual report

To build a report which we can run in Management Studio, we need to have SQL Server Data Tools (SSDT) installed (the tools formerly known as Business Intelligence Development Studio, BIDS, so you can use BIDS as well if you’re not yet on 2012). This is part of the regular SQL Server setup, so you can find it on your SQL Server 2012 or later installation media. But you can also just go and download it from the Microsoft Download Center.
After downloading, installing and starting this tool (which is just a Visual Studio template), we instruct it to create a new project. In the list of installed templates, you should find back the Business Intelligence group, select the Reporting Services group within, and on the right, click the Report Server Project Wizard and provide a useful project name:
Once you click OK, the wizard will start. Click Next to get past the start screen.
On the next screen the wizard asks which type of database connection we want to have, and to which server and database we want to connect. This is the tricky part, because we actually want to connect to whatever server and database we click upon in Management Studio object explorer. But now, we first need to develop this report in SSDT. Luckily, there is an easy way out: If we use the Microsoft SQL Server data source type, whatever server and database we select will be replaced with the ‘current’ server and database whenever we run this from Management Studio. So lets just connect to a server (I’m using localhost over here, but any server to which you can connect from SSDT will do):
Notice that if you want your report to not query the current database but a fixed database (e.g. always master or msdb), you should use three part names in your query (e.g. SELECT * FROM msdb.dbo.suspect_pages).
Next screen asks for the query to retrieve the data for this report. Paste the query we’ve written earlier in this blog into this window. The next screen asks for a report type, just confirm the tabular report. Then we have a screen that asks for the report design. Just drag all three fields into the Details window and click Finish. Then we hit the last wizard screen where we give this report a meaningful name: Log Size. Click Finish.

Step 3: fine-tuning the report

After we finish the wizard we end up in the editor. Click the preview tab to get an impression of what the report will look once deployed:
This report only looks slightly better than the table we got in Management Studio earlier on. We need to make a couple of improvements.
Switch back to the Design tab to widen the database name column (just drag the column header to the right).
Finally we want to have a graphical indication on the size of these log files. To achieve this, right-click the rightmost column header in our report and add a column to the right:
Next, from the Toolbox window, drag the Data Bar component into the bottom cell of this newly created column. In the pop-up window, just confirm the default data bar type. Finally, click the blue bar that appears in this cell. On the right another pop-up window appears. Click the green plus next to Values and click LogTotalSize:
Now preview the report again:

Step 4: Run in Management Studio

Now that we have a report which is easy to read, let’s make sure we can easily open up this report in Management Studio. In SSDT, in the File menu select Save Log File.rdl As... In the save dialog, navigate to My Documents > SQL Server Management Studio > Custom Reports and save it over there. Notice that this is not required, Management Studio can open reports from any file location; It’s just the default location for these files.
Finally, we can now open up Management Studio, right click any object in the object explorer, select Reports > Custom Reports, and select the file we just saved there.
If you can connect to multiple instances of SQL Server, try out this report on all of them, and you will see how the report takes the server context into account.
Right click to print and export the report… you can have all the options we had on the predefined reports.
This is not the end of it, it’s just the start. There is a lot more to learn about building Reporting Services reports, as well as about all the useful information we can extract from SQL Server. So, give it a try!


If you just want to download the .rdl file we created in this post, you can find it at http://1drv.ms/1yQjzMD

Comments (45) -

  • Neighbors watch online free

    7/13/2014 7:20:48 AM |

    Very good article. I definitely appreciate this site. Keep it up!

  • Kristi Annala

    7/19/2014 7:01:45 AM |

    "Incoming search terms for the article" KeywordLuv

  • Carey Woelfel

    7/19/2014 7:01:46 AM |

    site:.edu -"You must be logged in to post a comment" "Powered by WordPress" "You may use these HTML tags and attributes"

  • Dave Foley

    7/19/2014 10:31:28 PM |

    "powered by Serendipity" "Remember Information?"

  • Dante Sotak

    7/19/2014 10:31:36 PM |

    site:.edu -"You must be logged in to post a comment" "Powered by WordPress" "Leave a Reply"

  • Jonathan Braddy

    7/31/2014 12:39:36 AM |

    very well this really is excellent web site, spouse! i will notice your effort for you to, follow for you BTW!!!

  • mouse click the following internet site

    8/5/2014 7:33:02 PM |

    Thank you for sharing excellent informations. Your web-site is so cool. I'm impressed by the details that you have on this blog. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for more articles. You, my pal, ROCK! I found simply the information I already searched everywhere and simply could not come across. What a great site.

  • Carroll B. Merriman

    8/6/2014 11:57:06 AM |

    Hi there, just wanted to say, I loved this post. It was inspiring. Keep on posting!

  • Keven Cozad

    8/16/2014 1:41:37 PM |

    There is certainly a great deal to learn about this subject. I really like all the points you've made.

  • Odtrucia Alkoholowe Warszawa

    8/18/2014 6:12:50 AM |

    Polecamy najszybsze Odtrucia Alkoholowe Warszawa. Najlepsze odtrucia poalkoholowe w Warszawie.

  • this

    8/25/2014 5:51:34 PM |

    I simply want to mention I am just all new to blogs and definitely loved your website. Almost certainly I’m likely to bookmark your blog post . You certainly come with exceptional stories. Thanks a lot for revealing your website page.

  • my homepage

    9/12/2014 4:15:17 AM |

    I am not sure where you are getting your info, but great topic. I needs to spend some time learning more or understanding more. Thanks for excellent information I was looking for this information for my mission.

  • additional info

    9/27/2014 3:43:08 PM |

    I just want to say I am very new to weblog and seriously liked this page. More than likely I’m likely to bookmark your website . You amazingly have outstanding writings. Thanks a lot for sharing with us your website page.

  • Marybelle

    10/1/2014 3:05:18 AM |

    Woh I like your content material , saved to bookmarks ! .

  • Blanch

    10/1/2014 3:27:02 AM |

    I don't understand why a presidential candidate who says he supports public education would want to eliminate funding for public television shows that not only entertain but also educate children.

  • Brant Jeansonne

    10/8/2014 5:49:23 PM |

    Sometimes I just think that people write and don't really have much to say. Not so here. You definitely have something to say and you say it with style, my man! You sure do have an interesting way of drawing people in, what with your videos and your words. You've got quite a one-two punch for a blog!

  • my response

    10/8/2014 8:37:00 PM |

    I simply want to mention I am very new to blogging and honestly enjoyed your blog site. Most likely I’m want to bookmark your blog post . You surely have perfect posts. Cheers for revealing your website page.

  • Particia Hooker

    10/10/2014 1:48:21 PM |

    Thanks and i must say Neat job for this post. You might want to check out on <a href="http://www.physioinmotion.com.sg";>diploma in physiotherapy</a> too. Thanks

  • snapdeal coupons

    10/13/2014 7:12:27 AM |

    The idea is just about the really website I got at any time visitied. the information is undoubtedly exceptional.

  • we are fans

    10/13/2014 2:39:53 PM |

    Wiodaca role przejal w polowie lat 90. HTML Help, czy szablonowy struktura pomocy w aplikacjach Windows 95 tudziez jego nastepcow. Bazujac na dokumentach HTML, nadawal sie zgola az do tworzenia ksiazek elektronicznych, co zawdzieczal sila utworzenia dobrego systemu nawigacyjnego, skorowidza badz systemu wyszukiwawczego. tez tudziez tutaj zbytnio ostateczna postac odpowiada kompilator Microsoftu, oddanie zreszta niedobry, gdyz nie potrafiacy skompilowac rozmaitych formatow multimediow. nie predzej stosowanie pewnych sztuczek pozwalalo wkomponowac sposrod archiwami dodatkowe formaty, m.in. PDF.

  • Agripina Fesenbek

    10/16/2014 6:32:22 PM |

    I was wondering where you got your layout? Its healthy looking.

  • pop over here

    10/16/2014 8:07:39 PM |

    I have not checked in here for some time because I thought it was getting boring, but the last several posts are good quality so I guess I will add you back to my everyday bloglist. You deserve it my friend Smile

  • hogan online

    10/26/2014 9:05:31 PM |

    Toyota surely have launched the company's top of the line adaptation towards Executive Ceed spectrum you could very an interesting idea restricted Home theater marketing. A sensible and then easy-to-use <a href="http://www.nadirstar.com/libraries/";>hogan online</a> resource to collision specific objectives of the offering comparatively cheap professional sports car fight regarding a Euro potential customers.

  • like this

    10/29/2014 12:37:13 PM |

    I simply want to mention I am newbie to weblog and absolutely loved you're blog site. Almost certainly I’m likely to bookmark your blog post . You surely have outstanding writings. Kudos for revealing your web page.

  • scarpe hogan

    10/30/2014 1:24:52 PM |

    Toyota provide formed ones own top of the line edition to qualify for the Master Ceed range a lot of extremely alluring rapid Video advertisements. An ingenious and as a result perceptive <a href="http://www.nadirstar.com/libraries/";>hogan online shop recensioni</a> method of have an impact on ones own reasons for giving you low-priced basketball coupe hair-styling for a Eu prospects.

  • hogan rebel

    10/31/2014 10:25:56 PM |

    Honda may have marketed most of the top of the line release within the Professional player Ceed assortment with many somewhat persuading short Television for computer ads. A smart and intuitive <a href="http://www.nadirstar.com/libraries/";>hogan interactive</a> resource to result ones own aims associated with giving you fair rigolade coupe styling to a wonderful Western european projected audience.

  • hogan uomo

    11/1/2014 8:15:06 PM |

    Honda now have set up their unique up-to-date format into the Commercial Ceed variety quite a few quite appealing concise Tv for pc merchandising. A brilliant and simply perceptive <a href="http://www.nadirstar.com/libraries/";>hogan uomo online</a> procedure to consequence a aims most typically associated with proposing most affordable running coupe fashion to Western european clients.

  • godaddy india coupons

    11/3/2014 6:02:56 AM |

    This kind is probably the ideally internet site I have you ever visitied. the details are certainly important.

  • godaddy promo codes india

    11/3/2014 1:38:49 PM |

    The following is among the leading business site I've really ever visitied. the details are no more than awesome.

  • acai berry

    11/4/2014 4:06:23 AM |

    This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!

  • click

    11/6/2014 10:13:15 AM |

    I simply want to say I am just very new to blogging and site-building and really liked you're web page. Likely I’m planning to bookmark your blog . You certainly have exceptional posts. Many thanks for sharing your web site.

  • Clark Adcox

    11/8/2014 4:09:37 AM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon servicing</a> best in the best singapore.

  • visit the following website

    11/11/2014 8:10:03 AM |

    Fantastic goods from you, man. I've understand your stuff previous to and you are just extremely fantastic. I really like what you have acquired here, really like what you are stating and the way in which you say it. You make it entertaining and you still take care of to keep it sensible. I can't wait to read far more from you. This is really a tremendous site.

  • Merrill Prisco

    11/11/2014 3:45:23 PM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon servicing</a> best in the best singapore.

  • Trenton Heppding

    11/12/2014 2:59:22 AM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon servicing</a> best in the best singapore.

  • DEBORA Laurence

    11/12/2014 6:29:52 AM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 6:54:15 AM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • Nenita Bruley

    11/13/2014 1:44:48 PM |

    Always a plesure to read your content, seems you really have a talent for creating great content!Call centre and administration outsourcing http://spiknspan.com.au/

  • Leonor Delle

    11/13/2014 5:45:02 PM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon servicing</a> best in the best singapore.

  • how to make ice cream without ice cream maker

    11/27/2014 8:10:37 PM |

    You really make it seem so easy with your presentation but I find this matter to be actually something which I think I would never understand. It seems too complex and very broad for me. I'm looking forward for your next post, I will try to get the hang of it!

  • zovi coupons

    12/1/2014 7:40:23 PM |

    This really is exceedingly valid matter as increased throughout the weblog. Truly appreciate raving about across.

  • flipkart coupons

    12/1/2014 9:13:35 PM |

    This is certainly completely legitimate level as raised within your article. Recognize the value of speaking about across.

  • Ernie Gean

    12/2/2014 12:47:46 PM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon servicing</a> best in the best singapore.

  • Silvana Parke

    12/2/2014 2:56:45 PM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon servicing</a> best in the best singapore.

  • homemade ice cream cake

    12/7/2014 1:14:59 AM |

    Keep functioning ,remarkable job!

  • Jerrold Ashbrook

    12/7/2014 6:01:42 AM |

    Howdy! I just want to give a huge thumbs up for the nice info you've right here on this post. I might be coming back to your blog for extra soon.

  • Kenneth Signs

    12/7/2014 9:47:20 AM |

    You have mentioned very interesting points! ps nice  site.

  • Ma Crepeau

    12/7/2014 11:34:22 AM |

    Some really  nice stuff on this site, I enjoy it.

  • idlife

    12/8/2014 7:23:17 AM |

    Nice post! Check out my site sometime!

  • Bruce Lipski

    12/8/2014 6:51:54 PM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon services</a> best in the best singapore.

  • Wilburn Echternach

    12/10/2014 7:01:00 PM |

    The Lastest <a href="http://www.skyzone.com.sg";>aircon services</a> best in the best singapore.

  • Homemade Ice Cream Without Ice Cream Maker

    12/11/2014 2:15:42 AM |

    Hey, you used to write fantastic, but the last several posts have been kinda boring¡K I miss your tremendous writings. Past several posts are just a little out of track! come on!

  • best homemade ice cream recipe

    12/11/2014 8:49:26 AM |

    I really appreciate this post. I¡¦ve been looking all over for this! Thank goodness I found it on Bing. You have made my day! Thank you again

  • making ice cream

    12/11/2014 8:50:41 AM |

    Great write-up, I¡¦m regular visitor of one¡¦s blog, maintain up the excellent operate, and It is going to be a regular visitor for a long time.

  • Mental Health Social Network

    12/14/2014 7:06:04 PM |

    Hello, nice post!

  • firstcry coupons

    12/14/2014 10:20:16 PM |

    This can be extraordinarily applicable idea as greater around the wordpress blog. Recognize writing about in and around.

  • UniqueI

    12/17/2014 9:16:37 AM |

    An interesting discussion is worth comment. I think that you should write more on this topic, it might not be a taboo subject but generally people are not enough to speak on such topics. To the next. Cheers

  • fishinglovers

    12/17/2014 10:16:46 AM |

    Great post.Thanks Again. Want more.

  • fishinglovers

    12/18/2014 2:01:58 AM |

    Really enjoyed this blog post. Want more.

  • buy levitra 20mg

    12/22/2014 5:38:00 AM |

    I really appreciate this post. I have been looking all over for this! Thank goodness I found it on Bing. You have made my day! Thank you again!

  • ice cream flavors

    12/22/2014 11:35:42 AM |

    There is noticeably a lot to realize about this.  I consider you made some good points in features also.

  • check this site out

    1/1/2015 9:56:21 PM |

    It's the best time to make some plans for the long run and it is time to be happy. I have read this post and if I may just I desire to recommend you few interesting things or tips. Maybe you could write subsequent articles relating to this article. I wish to read more things about it!

  • he said

    1/1/2015 9:56:23 PM |

    I just like the valuable info you provide for your articles. I’ll bookmark your blog and check again here regularly. I am fairly sure I will be informed a lot of new stuff right here! Good luck for the next!

  • hvac duct cleaning Bozman MD

    1/4/2015 11:36:17 PM |

    always i used to read smaller posts that  also clear their motive, and that is also happening with this piece of writing which I am reading now.

  • scaners

    1/5/2015 7:54:22 PM |

    obviously like your web site however you have to check the spelling on several of your posts. A number of them are rife with spelling issues and I to find it very troublesome to inform the reality then again I will surely come again again.

  • lA7Zm

    1/6/2015 7:17:17 PM |

    16541 998942Delighted for you to discovered this site write-up, My group is shopping a lot more often than not regarding this. This can be at this moment undoubtedly what I are already seeking and I own book-marked this specific site online far too, I�ll often be maintain returning soon enough to appear at on your special weblog post. 417418

  • porn

    1/13/2015 10:41:50 PM |


  • money

    1/18/2015 9:14:18 AM |


  • porno

    1/19/2015 7:52:52 AM |


  • pil

    1/20/2015 4:12:03 AM |


  • how to make homemade ice cream

    1/20/2015 8:53:04 PM |

    Hi, Neat post. There is an issue with your website in web explorer, would test this¡K IE still is the market chief and a huge element of folks will pass over your great writing due to this problem.

  • pill

    1/22/2015 5:02:37 AM |


  • her explanation

    3/3/2015 7:49:47 PM |

    I just want to say I am very new to blogging and absolutely enjoyed this blog. More than likely I’m planning to bookmark your site . You amazingly have amazing articles. Bless you for sharing with us your web page.