SSRS: Dynamic Datasets in Excel using SSRS

I recently came across a requirement for an Excel template which would contain a dataset which could be refreshed by the end user on demand.

For security reasons, we could not allow the user direct access to the sproc on our reporting database. Instead, I discovered a way to pull data into Excel from an SSRS report. This gives the benefit of using an already established reporting outlet with its own built-in security model.

To set this up (in Excel 2007), open a new sheet and go to the 'From Web' option in the Data ribbon. In the address bar, enter the URL of your report in the following format:

http://SERVERNAME/reportserver?REPORTPATH&rs:Format=html4.0&rc:Toolbar=false&rs:Command=render

(Note the string of options at the end of the URL (&rs:Format=html4.0&rc:Toolbar=false&rs:Command=render). These are necessary for the link to work correctly.)

eg: http://59.145.122.172/reportserver?/Report+Project1/Report2&rs:Format=html4.0&rc:Toolbar=false&rs:Command=render   [This server has nothing to do with me. Just some public SSRS server.]

Report parameters can be specified in the URL string by tagging them onto the end. eg. &StartDate=2011-01-01

Next, click the table selector attached to the main data table and click Import.

That's it. This dataset can now be refreshed by anyone who has access to the report.

*** One thing to point out is that the complete dataset must be visible on the first page of the report. To make sure of this, open up the RDL, go into the tablix properties and enable the 'Keep together on one page if possible' option.

SQL Server: Keep a Log of Your Executed Queries in SSMS

Every SQL Server DBA has experienced the odd Management Studio crash. They usually happen close to the end of a five hour session involving ten different tabs and a mountain of code. Occassionally, SSMS will look on you favouribly and restore some of your work. Usually, not.

A while back, I discovered an add-in for Management Studio which has helped me recover from these little frustrations. The SSMS Tool Pack is a suite of small utilities which generally make SSMS easier to work with. The most useful of these utilities is a query execution logger. It maintains two seperate logs. The first contains a copy of every query executed within SSMS. The second is a complete snapshot of the code sitting in the current tab.

As well as helping to recover from a SSMS crash, the logs can act as a memory aid. The execution log window allows you to search against execution date, server name and, of course, the code itself. Struggling to remember that smart-arse method you sussed out last year? Fire up the execution history window and tell it what you know.

By default, the Tool Pack's query log is saved to a file on the local drive. Although, it can be configured to save to a database table instead, which can speed up lookups and searches.

As well as the query logging tool, the Tool Pack contains 12 other handy utilities, including a code formatter, a tool to quickly store and recall snippets of code and a feature which adds a server-specific strip of colour to the each query tab - to help remind you that the DROP DATABASE command you are about to fire is going to be run on the live server.

The Tool Pack add-in is available for free download at http://www.ssmstoolspack.com. It's developed and maintained by one guy, Mladen Prajdić. So, if you get some use out of it, be sure to drop him a few Euros through his donations page.

T-SQL: Including Total Row Count in Your Dataset

A common way to include aggregate amounts in a query is to use a subquery:

SELECT ProductID
     , Name
     , ProductNumber
     , Color
     , (SELECT COUNT(*) FROM Production.Product WHERE ProductID BETWEEN 970 AND 980) AS RC
FROM Production.Product
WHERE ProductID BETWEEN 970 AND 980

ProductID       Name                            ProductNumber           Color           RC
970             Touring-2000 Blue, 46           BK-T44U-46              Blue            11      
971             Touring-2000 Blue, 50           BK-T44U-50              Blue            11      
972             Touring-2000 Blue, 54           BK-T44U-54              Blue            11      
973             Road-350-W Yellow, 40           BK-R79Y-40              Yellow          11      
974             Road-350-W Yellow, 42           BK-R79Y-42              Yellow          11      
975             Road-350-W Yellow, 44           BK-R79Y-44              Yellow          11      
976             Road-350-W Yellow, 48           BK-R79Y-48              Yellow          11      
977             Road-750 Black, 58              BK-R19B-58              Black           11      
978             Touring-3000 Blue, 44           BK-T18U-44              Blue            11      
979             Touring-3000 Blue, 50           BK-T18U-50              Blue            11      
980             Mountain-400-W Silver, 38       BK-M38S-38              Silver          11      

This query returns the total number of rows in the dataset. However, it's bulky and requires duplication of code.

A neater solution is to use the PARTITION BY clause:

SELECT ProductID
     , Name
     , ProductNumber
     , Color
     , COUNT(*) OVER (PARTITION BY 1) AS RC
FROM Production.Product
WHERE ProductID BETWEEN 970 AND 980

The PARTITION BY clause can also be used to count the number of records falling into a particular category:

SELECT ProductID
     , Name
     , ProductNumber
     , Color
     , COUNT(*) OVER (PARTITION BY Color) AS RC
FROM Production.Product
WHERE ProductID BETWEEN 970 AND 980

ProductID       Name                            ProductNumber           Color           RC
977             Road-750 Black, 58              BK-R19B-58              Black           1       
978             Touring-3000 Blue, 44           BK-T18U-44              Blue            5       
979             Touring-3000 Blue, 50           BK-T18U-50              Blue            5       
970             Touring-2000 Blue, 46           BK-T44U-46              Blue            5       
971             Touring-2000 Blue, 50           BK-T44U-50              Blue            5       
972             Touring-2000 Blue, 54           BK-T44U-54              Blue            5       
980             Mountain-400-W Silver, 38       BK-M38S-38              Silver          1       
973             Road-350-W Yellow, 40           BK-R79Y-40              Yellow          4       
974             Road-350-W Yellow, 42           BK-R79Y-42              Yellow          4       
975             Road-350-W Yellow, 44           BK-R79Y-44              Yellow          4       
976             Road-350-W Yellow, 48           BK-R79Y-48              Yellow          4

This query returns the number of records which contain the same Color value as the current record.