Building custom reports for SQL Server management 26 June 2014 Nico-Jacobs Business Intelligence, SQL Server, Reporting Services, Talks 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: They can be exported in PDF, Word and Excel format, as well as being printed 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. 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_countersWHERE 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 LogTotalSizeFROM ( 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 srcPIVOT(SUM([Size in Kb]) FOR [Counter Name] IN ( [Log File(s) Size (KB)] ,[Log File(s) Used Size (KB)] )) AS pvtORDER 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! Download If you just want to download the .rdl file we created in this post, you can find it at http://1drv.ms/1yQjzMD
Using reporting services with Oracle package / stored procedure 16 December 2010 Nico-Jacobs SQL Server BI, Reporting Services Reporting services can create data sets on top of Oracle stored procedures. Sure, the manual says so, but this afternoon, I tried to get things up and running, and I want to share some of the dead ends and problems I experienced in that, maybe it can help you avoid these obstacles. Don’t panic, not all paths are dead ends, we got to the stored procs in Oracle via Reporting Services . Please note that I’m not an oracle expert, nor did I spend days figuring these things out. So please, if you can share your experience, please do so! Getting started There are two important decisions that can cause this querying to go wrong: Which data provider do we use? How do we create the procedure in Oracle First the data provider. I installed Oracle 10g Express edition on the same machine that was running Microsoft Reporting Services 2008 R2. To test the connection with Oracle independent of the Microsoft product stack, I also installed the freeware version of Toad. A first frustration that I ran into was that I was unable to connect to Oracle from Toad on my 64 bit version of windows 7. I could access Oracle from the web interface, but not from Toad. Installing the client tools for Oracle express didn’t help . So I moved to 32 bit box of Win7. Installing Oracle Express with client tools on this box and… hurray, Toad can talk to Oracle. Next, I used Integration Services to push the DimGeography table from AdventureworksDW into my Oracle. Time to query the data from Reporting Services. I first try sending an explicit query with the three data providers that I have on my machine: Microsoft OleDb Provider for Oracle (MSDAORA) Oracle Provider for OLE DB (OraOLEDB) Oracle (OracleClient .Net) MSDAORA Configuring the data provider is straight forward, only remember to not use “.” or “(local)” as the name of the local machine, but use localhost (or 127.0.0.1) instead. When I create a dataset with query type Text, I can start the graphical query builder. The query builder lists nicely all tables and views, and is clever enough to put double quotes around my object names. Also query type Table is available, but unfortunately, this one does not list the available tables, I must type the table name myself. Don’t forget to put double quotes around the table name, or Oracle can become angry at you! Query type Stored Procedure is available as well, but whatever I tried, I could not get it working. Nothing shows up in the drop-down box, typing the procedure name results in an error asking for parameters, when I start adding parameters in the call as well, it either claims ORA-06550 PLS-00201 identifier must be declared, or ORA-06550 PLS-00306 wrong number or types of arguments, or ORA-01008 not all variables bound I gave up on this one. OraOLEDB Also easy to configure this Oracle OleDb driver, just as MSDAORA. When building a query in the graphical query builder environment, the query builder doesn’t put double quotes around table and column names, which causes error ORA-00942: table or view does not exist. Of course, we can manually add the quotes, but it’s nice if computers work for us, not the other way around. Besides this, the driver seems to give the same results as MSDAORA, and I can’t seem to get the stored procedure calls up and running. Next! OracleClient Last one down the list. Configuring the DataSource is comparable with the two previous: local connections should refer to Localhost or 127.0.0.1, not just “.”. As query type, only Text and Stored procedure is available, not Table. The Text graphical query builder behaves as with MSDORA. But the very good news is that if you create an Oracle stored procedure in the proper way (more on that in a second), that you can select the stored procedure radio button and the drop-down box will be populated with the Oracle stored procedures! Just select one from the list and it will behave exactly as on sql server: All the regular parameters of the stored procedure become report parameters, and a data set is built based on the first table returned by the stored procedure. Building the Oracle stored procedure Oracle is somewhat different from SQL Server when it comes to stored procedures. In Oracle you can create a package, a container in which you can declare and implement variables, stored procedures, functions,…. In contrast to SQL Server, a stored procedure in Oracle cannot return a table, only functions can do so. But … we still need to use the stored procedure anyhow, if we want it to show up in the Reporting services drop-down box. The trick is to make sure this stored procedure builds up the result in a REF CURSOR, and make this ref cursor a parameter! To illustrate this, I show you the code to get a list of all cities in a particular country (a lot of thanks to Philippe Clercq for writing this!) CREATE OR REPLACE PACKAGE sp_country AS type ref_cursor is REF CURSOR; PROCEDURE get_city(country_name IN varchar2 default null,po_cursor out ref_cursor); END sp_country; CREATE OR REPLACE PACKAGE sp_country AS PROCEDURE get_city(country_name IN varchar2,po_cursor out ref_cursor) IS BEGIN open po_cursor for SELECT "GeographyKey", "City", "StateProvinceCode", "StateProvinceName", "CountryRegionCode", "EnglishCountryRegionName", "SpanishCountryRegionName", "FrenchCountryRegionName", "PostalCode", "SalesTerritoryKey" FROM DW."DimGeography" g where upper(g."EnglishCountryRegionName") like upper(country_name||'%'); END; END sp_country; And with that, we could finally build our Reporting Services report on top of Oracle Express Edition (XE) and have the stored procedures show up in the drop down box. Of course, once your data is in, it doesn’t matter anymore whether you build your reports on top of SQL Server or Oracle. Nico
Jump to report action hides parameters 04 October 2009 Nico-Jacobs SQL Server BI, Reporting Services Reporting services offers three types of actions. One of them is the “Jump to report” action, often used for a drill-through scenario where you jump from an overview report into a report that provides detailed data. Usually, the detailed report has parameters (such as the month and region for which we want to see the details) which are set by the overview report. So usually, end users do not need to see the parameters of the detail report, since they already set these parameters by invoking the action. But sometimes, the detail report has parameters that still need to be set explicitly. Unfortunately, the parameter list is always collapsed, which might be confusing for end users. However, I didn’t discovered a way to convince Reporting services to show the parameter area… as long as we are using the jump to report action. But there is a “jump to URL” action as well, in which we can control the complete URL. If you combine that with the power of reporting services URL rendering, you can get a drill-through report with visible parameters. The price you pay is more work in writing the correct expressions to manually build the URL, which makes your report slightly more expensive to maintain… everything comes at a price…