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:
image

 

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:

SELECT *
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:
image
 
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
FROM (
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
image
 
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:
image
 
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):
image 
 
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:
image
 
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:
image
 
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:
image
 
Now preview the report again:
image
 

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

Download

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

Loading GPX waypoints with Excel PowerQuery

Plotting data on maps is becoming popular: We can use the geometry and geography data type in SQL Server, use the Reporting Services maps, Excel PowerView allows mapping coordinates on a map, and the recently released PowerMap plug-in in Excel offers beautiful visualizations of map data.

image

But how can we easily get coordinates into these tools? Some tools connect with the Bing lookup service: you provide an address, Bing translates this into latitude-longitude coordinates. But sometimes we want to explicitly insert coordinates into the database. For instance, imagine you collecting location data with a smart phone or GPS device. Many of these devices store information in GPX or KML format. In this article, we discuss how we can load the waypoint coordinates from a GPX file into Excel. From there we can use it in Excel Power Map or Excel Power View reports, or we can use the SQL Server Import/Export wizard to import the Excel file into a SQL Server table, from which we can then use Reporting Services on this.

PowerQuery

PowerQuery is a free plugin for Excel 2010 and 2013. If you haven’t done so, download it from the Microsoft download site, and run the installer. Once installed, we can start loading different types of files into it, filter and manipulate the data, and in the end stored the result in either a regular Excel sheet, or in a PowerPivot model. Since GPX files are just XML files, we can load the file that way. GPX files can store coordinates in different ways. The two most popular are as a track (a bunch of points who make up a walk or route) or as waypoints: markers or points of interest. In the first example, we will load a local gpx file extracting the trackpoints. To save you all the trouble of repeating the transformation steps, I have included the M script. To rerun this:

  1. Start Excel
  2. In the PowerQuery ribbon select Get External Data –> From Other Sources –> Blank Query
  3. Go to the View ribbon, and select Advanced Editor
  4. Paste the query you see below into the query window, and replace the path of the gpx file with the actual path on your machine
  5. Click Done
  6. In the lower right corner, select the destination: If you plan on using PowerMap or PowerView on this data, select the Load to Data Model option
  7. In the Home ribbon select Apply and Close
let
Source = Xml.Tables(File.Contents("c:\MyGPXFile.gpx")),
#"Expand trk" = Table.ExpandTableColumn(Source, "trk", {"name", "desc", "number", "http://www.topografix.com/GPX/Private/TopoGrafix/0/1", "trkseg"}, {"trk.name", "trk.desc", "trk.number", "trk.http://www.topografix.com/GPX/Private/TopoGrafix/0/1", "trk.trkseg"}),
#"Expand trk.trkseg" = Table.ExpandTableColumn(#"Expand trk", "trk.trkseg", {"trkpt"}, {"trk.trkseg.trkpt"}),
#"Expand trk.trkseg.trkpt1" = Table.ExpandTableColumn(#"Expand trk.trkseg", "trk.trkseg.trkpt", {"Attribute:lat", "Attribute:lon"}, {"trk.trkseg.trkpt.Attribute:lat", "trk.trkseg.trkpt.Attribute:lon"}),
RemovedOtherColumns = Table.SelectColumns(#"Expand trk.trkseg.trkpt1",{"trk.trkseg.trkpt.Attribute:lat", "trk.trkseg.trkpt.Attribute:lon"}),
ChangedTypeWithLocale = Table.TransformColumnTypes(RemovedOtherColumns, {{"trk.trkseg.trkpt.Attribute:lat", type number}, {"trk.trkseg.trkpt.Attribute:lon", type number}}, "en-US")
in
ChangedTypeWithLocale
If you want to load the waypoints instead of a track, you can repeat the same steps, except using this script:
let
Source = Xml.Tables(Web.Contents("http://www.topografix.com/fells_loop.gpx")),
ChangedType = Table.TransformColumnTypes(Source,{{"time", type datetime}, {"Attribute:version", type number}, {"Attribute:creator", type text}}),
#"Expand wpt" = Table.ExpandTableColumn(ChangedType, "wpt", {"ele", "time", "Attribute:lat", "Attribute:lon"}, {"wpt.ele", "wpt.time", "wpt.Attribute:lat", "wpt.Attribute:lon"}),
RemovedOtherColumns = Table.SelectColumns(#"Expand wpt",{"wpt.ele", "wpt.time", "wpt.Attribute:lat", "wpt.Attribute:lon"}),
ChangedType1 = Table.TransformColumnTypes(RemovedOtherColumns,{{"wpt.time", type datetime}}),
ChangedTypeWithLocale = Table.TransformColumnTypes(ChangedType1, {{"wpt.ele", type number}, {"wpt.Attribute:lat", type number}, {"wpt.Attribute:lon", type number}}, "en-US")
in
ChangedTypeWithLocale
Notice that this last script loads the gpx file from the internet, replace Web.Content with File.Content if you want to load from a local file.

PowerMap

Once we have loaded the data in an Excel PowerPivot model, its easy to get it into a PowerMap. First, if you haven’t done so, download and install PowerMap. Open the Excel workbook in which you loaded the PowerQuery. We will use in the example the waypoints we loaded with the second example.

  1. Go to the Excel Insert ribbon, and click Map –> Launch Power Map
  2. The Power Map dialogue opens (you need to be connected to the internet, since it talks to Bing Maps) and prompts for the geographical information. Select the latitude and longitude fields, and specify explicitly which field is latitude and longitude:
    image
  3. Click Next. We now already see a map with the waypoints, but we can also plot extra information that is in the GPX file. For instance, we copied in this example also the time the waypoint was visited, and the elevation. So we can ask PowerMap to draw higher data bars for points with higher elevation, and we can put the time into the time axis:
    image
  4. Finally, by clicking the settings icon in the upper right corner we can make the data bars lower, less opaque and more narrow:
    image
  5. This leads now to this visualization:
    image
    You can use this as a starting point to learn and experiment more with Power Map. Or use the model we made earlier on in Power View reports (from the Excel Insert ribbon as well). 

I hope this gives you some inspiration to get started building your own visualizations on top of either GPS information you collect yourself, or information you find on internet or intranet. If you want to learn more on using these Excel data processing and reporting options (known as PowerBI), subscribe to one of our PowerBI classes.