It was a great honor to be asked to join my associates from SolidQ at the Microsoft Virtual Academy Studios in Redmond and talk about how to upgrade to SQL Server 2012 and 2014. These recordings, which are also on my YouTube Channel, include the material I covered in these sessions. The entire series of studio presentations are hosted on Channel 9 and here at the MVA with accompanying assessment surveys and resources.
In these studio sessions, I am joined by my fellow authors of the 429 page SQL Server 2014 Upgrade Technical Guide; Richard Waymire, Ron Talmage and Jim Miller from SolidQ. Jim and I were responsible for the Business Intelligence content. In our sessions Jim covered SSIS and SSAS Multidimensional, and I covered SSAS Tabular, BI tools and SSRS. In this edited portion of the SSAS session, Jim begins with a brief summary of multidimensional upgrade options and I continue to discuss opportunities to implement SSAS Tabular solutions. BI topics apply equally to SQL Server 2012 and 2014 upgrades. Continue reading →
Can an SSRS report be designed to drill-through to an Excel workbook in-context (showing only the same filtered detail data)? I have to admit that I have been chasing this solution for a few years now before I was motivated enough to work it through to a viable result. More than a few consulting clients have asked if it is possible to build a dashboard solution with the option to drill-through to transactional details in Excel. The answer has always been “well, sort of” …we could only drill-through to an SSRS report and then export the contents of that report to Excel. That answer usually wasn’t good enough for the Excel power users who need to create their own workbook formulas and calculations, and use other Excel formatting and features; like PivotTables, slicers and conditional visualizations. Over the past few years, I have used some clumsy work-around techniques and discovered things like: if the target workbook were published in SharePoint and managed in a web part, workbook parameters can be used with great effort to achieve this task. However, that option has not proven to be a practical solution in most cases. As my good friend Steve Eaton once said: “Anything is possible if you have a positive mental attitude, tons of money and supernatural powers.” I’ll admit that I’m short on two of the three but I do have persistence and I’m bull-headed enough to apply a little out-of-the-box thinking now and again. The technique I will demonstrate will work in a standard Reporting Services deployment with any edition of Excel on the desktop.
I’ll start with a quick demonstration of the finished solution. The Order Details report, shown here in Internet Explorer, gets data from a data warehouse (AdventureWorksDW2014 in this example). The order details and line items are stored in our line-of-business transactional database (for this demo, AdventureWorks2014). As you can see, I choose a data range using the standard date parameter UI. I’ve also exposed a Product parameter using a set of cascading lists and custom actions in the report header (I’ll cover that technique in another post). The relevant point is that we’re selecting some parameters to get a filtered view of the report data. After choosing the date range, I use the Category, Subcategory and Product lists in the report header to select Clothing, then Socks and then the product: Racing Socks, L. The cascading list simplifies the selection from among several hundred products.
A list of orders is displayed for the selected data range and product. In the list, I click the Sales Order Number for which I want to see the order and line details in my Excel report.
This displays a link “button” in the table header with a summary of the order number I selected.
When I click this link, the web browser confirms that I want to open the Excel file. This existing file stored in a network file share contains a connection to the transactional database with the order detail information. When the report opens, it applies the filters and shows the order with line item details. Our business users are thrilled with this because they’re actually using Excel with any features and capabilities they want. Rather than dumping a copy of data into a static table, live data is presented in PivotTables or charts which contain their calculation formulas and custom formatted data. If the business user decides to add another column, calculation, chart or other item to their report; they simply save it with those changes and use that as their detail report going forward. The new drill-through data just magically shows up in their workbook file with those additions the next time they drill-through from the summary report.
How Does It Work?
Reporting Services allows us to use parameters to pass information between reports – and that’s awesome if you’re only using Reporting Services. It allows you to maintain the context of properties and filtering options. But, if you’re not using Reporting Services and don’t have some kind of mechanism to “pass” parameters (like QueryString parameters to send information to a web page), we need to put those values some place so the target “report” (i.e. Excel workbook in this case) can retrieve them and apply them as filters. So, where would be a reliable place to store parameter information? How about SQL Server! Novel, huh?
To get started, open SQL Server Management Studio and create a database named ReportStandards. Let’s add all of the objects at once and then I’ll step through the use of each one. For demonstration purposes, I have not taken time to optimize this database and adding a few indexes would be advisable in a production scenario. Execute this T-SQL script:
Stores sets of parameter values for a user running a report
InsertReportUserFilter stored procedure
Inserts a parameter record into the ReportUserFilters table including the current user, table name, key value and a time stamp
ClearReportUserFilters stored procedure
Removes stored parameter records for the current user
Returns a set of filter parameters to be used in a SQL WHERE clause IN function
PrepareReportSalesOrder stored procedure
An implementation for a specific report
I’ll open the finished SSRS report in Report Builder to show you the working solution.
There are several features of this report that aren’t directly related to the Excel drill-through action so I’ll fast-forward through those after a brief summary. This is like that scene in The Princess Bride where the masked swordsmen wakes up after being mostly dead and brought back to life by Miracle Max. Inigo says “let me e’splain… No. There is too much… let me sum up.” Here’s the summary:
The relevant report elements are circled in red and annotated with red numbers. Everything else is standard stuff that I would have designed into a report that doesn’t do this drill-through thing to Excel. The non-circled elements are parameters, datasets and other report elements that let the user interact with the report and filter a list of orders for a selected product and data range. I’ll refer to these numbers as I describe these report design elements.
Item 1 is a report parameter named SalesOrderNumber. A report action on the SalesOrderNumber textbox in the table (item 5) sets this parameter value. The parameter is defined as Text type, is hidden and has a default value of -1. The default value is a placeholder value that isn’t a valid SalesOrderNumber value.
The Orders dataset is just a plain old query that returns order records from the AdventureWordksDW2014 database filtered on the ProductKey, OrderDateFrom and OrderDateTo parameters. Nothing fancy here:
rs.OrderDate BETWEEN @OrderDateFrom AND @OrderDateTo
ProductKey = @ProductKey
The table (Item 5) is unsophisticated as well. The SalesOrderNumber textbox in the detail row has a report action defined. Open the textbox properties window and select the Action page which looks like this in the designer:
The target report expression (labelled “Specify a report”) just refers to the Globals!ReportName object. This target re-runs this report when the user clicks this textbox. All the parameters but the last one are simply used to maintain their current values when the report is re-rendered. Ignore the ShowParameters item as well. The SalesOrderNumber parameter is set to pass the SalesOrderNumber field value so we know which order the user selected.
Item 3 is a dataset named SetupSalesOrderReport which serves two purposes. Most importantly, it writes the selected SalesOrderNumber value to a table so it can be used to filter the result set in the Excel workbook. This dataset is a simple stored procedure that returns a flag value used to display the drill-through link. Item 4 is a textbox that serves this purpose and its Hidden property is set to the following expression:
This simply says “if the SetupSalesOrderReport Result field value is ‘Success’, show this textbox”.
The SetupSalesOrderReport dataset is references the PrepareReportSalesOrder stored procedure and passes the SalesOrderNumber report parameter. When the selected order number is passed to the parameter in this report action, the procedure stores the value and returns “Success”. This, in turn, displays the textbox showing the link. The Value of the textbox uses the following expression to display a dynamic instruction to the user:
=”Sales order ” & Parameters!SalesOrderNumber.Value & ” details in Excel”
..and the Action for this textbox uses a Go to URL link using this expression:
=”file:\\\\tsclient\D\Projects\Excel Drillthrough Reports\Sales Order Details.xlsx”
Any valid UNC path will work here, prefixed with “file:\\”. This particular path is for a folder on my laptop that I am accessing from within a virtual machine I use for development and demonstrations. You will need to grant file system permission to the folder or share to be able to open this file.
The Excel “report” is a standard workbook. I’m using Excel 2013 but any supported version or edition of Excel will work. The important element of this solution component is the connection used to drive the PivotTables in the workbook. You can use a SQL statement to define a connection/query in Excel but it’s much easier to use a view. This report uses the following view which I created in the AdventureWorks2014 database:
create view vReportSalesOrderDetail
inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
inner join Sales.Customer c on soh.CustomerID = c.CustomerID
inner join Person.Person cp on c.PersonID = cp.BusinessEntityID
inner join Sales.SalesPerson sp on soh.SalesPersonID = sp.BusinessEntityID
inner join HumanResources.Employee e on soh.SalesPersonID = e.BusinessEntityID
inner join Production.Product p on sod.ProductID = p.ProductID
soh.SalesOrderNumber in (select * from ReportStandards.dbo.GetReportUserFilters(‘FactResellerSales’) )
After selecting this view when defining the connection in Excel, the command text is simply:
I’ve updated the connection properties to refresh the data when the workbook file is opened. This will run the query and return live sales order data and apply the filtering logic that was added from the Reporting Services report.
Two PivotTables are added to the worksheet along with a calculated field (just to demonstrate that calculations can be performed in Excel rather than externally). Several styling enhancements are added in Excel such as data bars, font and color changes, hiding the grids, etc.
There is a lot of opportunity to enhance this solution depending on specific business needs. For example, a report name column can be added to the ReportUserFilters table to store user/parameter values separately for each report. I haven’t used the InsertDateTime column in this example but this could be used to go back and run the detail report for a point-in-time.
The previous example only inserted one parameter value but the following enhancement could be used to pass multiple selected parameter values from SSRS so they would all be included in the detail report:
Please join my associates and I for an all-day SQL Server Upgrade workshop on November 3rd
If you are planning an upgrade to a newer version of SQL Server, you won’t want to miss this all-day, preconference workshop.
Join John Martin, Sr. Technology Evangelist from Microsoft, will spend the morning discussing migration planning from SQL Server 2005.
In the afternoon, SolidQ mentors Ron Talmage, Richard Waymire, Jim Miller and Paul Turley will talk about and demonstrate upgrading specific workloads (projects and features) from older product versions to newer product versions. We will introduce the comprehensive SQL Server 2014 Upgrade Whitepaper that we recently wrote for publication by Microsoft.
Additional to upgrading specifically from SQL Server 2005 to SQL Server 2014, we will also talk about upgrading from SQL Server 2008 and 2008 R2 to SQL Server 2012 and 2014.
An upgrade and/or migration is far more than just a case of moving a database or installing a new version of SQL Server, there have been so many changes since SQL 2005 arrived that we need to do a lot of tasks to ensure we have a successful upgrade project.
This session will guide you through the process, looking at not only the technology but the methodology, processes and tools that we have at our disposal to make sure that when we do move from SQL Server 2005 to 2012/2014 or to the cloud with SQL Server in an Azure VM or Azure SQL Database that we do so in a way that we can be confident of success. We will take a special look at workload-specific upgrade needs for OLTP, HA, SSAS/SSRS, and SSIS environments.
In my afternoon section, I will demonstrate the capabilities of SSAS Tabular models and discuss upgrading and migrating Reporting Services from 2005, 2008 and 2008R2 to 2012 and 2014.
This fact is probably not documented very well but this is an important factor in SSRS visual report design…
The use of rectangles is very much a core part of Reporting Services report design and a difference in behavior is expected. Cells in a tablix will grow vertically to accommodate wrapping text and when that happens any object on the same row will stretch to fit the row height. For images, charts (which are rendered as images), gauges, indicators (which are gauges) and sparklines (which are charts); this means that the image must be resized and scaled to fit. Image scaling adds a lot of rendering overhead. If you place any of these objects in a rectangle, this prevents the image scaling and speeds things up considerably. This is by design and a recommended practice.
In several comments to that post, users noted slow rendering when charts and sparklines were used in a table. In addition to the rectangle trick, one user suggested “Setting the SizeToReportContent to false on the report viewer allows the images to be consolidated into one image and the performance improves dramatically.”
In July, 2012, Jason Thomas posted on a similar topic:
I’m very excited to see my first feature article published in SQL Server Pro Magazine titled Custom Programming to Enhance SSRS Reports; How to write a custom assembly and use it to build a dynamic report dataset. The article was posted online in April and featured in the July printed and electronic edition. SQL Server Pro (formerly known as “SQL Server Magazine” or “SQLMag”) is published by Penton Media and is the largest publication for the SQL Server community. Please read the article, download the code, work through the exercise and let me know if you have comments or questions.
I posted an early draft of this article in my blog last year titled Using Custom Assemblies in Reports to Generate Query Logic (parts 1 and 2). The code was cleaned-up and tech edited in the new article which I recommend as the most reliable source (not that I write bad code, mind you, but it never hurts to have a formal tech review.)
As part of SQL Server Pro eLearning series, I’ll be presenting three 75 minute sessions on March 26th on using Expressions to enhance report capabilities. The sessions are online with live chat from 11:00 to 4:30 Eastern Time.
We can now use Visual Studio 2012 to create and edit SQL Server BI projects! Woo Hoo.
In the past, if you wanted to develop and maintain BI projects in the same development environment as other Visual Studio projects, you had to install multiple versions of Visual Studio. This may no longer be necessary if you install the new Business Intelligence for Visual Studio 2012 (SSDT BI). Continue reading →
Just wanted to get the word out that our SSRS 2012 training course is available. Are you looking for expert training on SQL Server 2012 Reporting Services? SolidQ now offers a SSRS training course with focus on designing report solutions for Business Intelligence and SharePoint integration. Not using SharePoint? Private classes can be adapted to use Reporting Services in native mode. Training is available for public and private events, onsite or remote delivery.
Designing BI Report Solutions with SQL Server 2012 Reporting Services and SharePoint 2010 Continue reading →
Creating a report with two datasets that reference the same pair of parameters, I’m seeing the following error:
The Value expression for the query parameter ‘DatesYear’ contains an error: The expression references the parameter ‘DatesYear’, which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case. (rsRuntimeErrorInExpression)
An error has occurred during report processing. (rsProcessingAborted) Continue reading →
There has been an active discussion thread on Chris Webb’s blog about the similarities & differences between various Microsoft BI & reporting tools. This is becoming a very popular topic and I thought I’d post my reply to that thread here. I encourage you to read the entire post on Chris’s blog here: Some thoughts on what Office 2013 means for Microsoft BI
…These are some very interesting thoughts about the differences in capabilities between SSRS and Power View. Continue reading →
I really enjoy this topic. One of the reasons, I suppose, it’s such a hot topic is that a lot of people believe that there is a perfect ad hoc reporting solution out there, just barely beyond the current set of capabilities in all of the available tools. Continue reading →
Sometimes you need to process parameter business rules that extend beyond the capabilities of a query language (like T-SQL or MDX) and it may just be easier to use a custom programming solution to manage a report dataset query and related report behavior.
In our recent book "Professional SQL Server 2012 Reporting Services", I approached this topic in Chapter 21, beginning in Page 692. This post is an extension to that topic and here, I’ll explore the technique at greater depth.
T-SQL and MDX are great query languages and have some support for handling conditional logic but sometime you may just need to use a true programming tool to handle parameter exclusions and conditional logic. To handle the real hard conditions, often the best approach is to programmatically assemble parts of the query as conditions dictate. The example that I will use in this post is not all that complex and much of it could easily be handled using more conventional methods. The problem with creating demonstrations is that it can be very difficult to simulate, in concise and simple form, some of the real vexing problems that we encounter on real live projects. And I’ve got some nasty ones out there – it’s just tough to boil down some of the design challenges into simple examples. So… trust me. The technique I’m demonstrating can be quite valuable, and in some cases; necessary.
The design pattern: Use an external custom method to assemble a T-SQL statement based on several parameterized arguments.
The assembly will be developed in Visual Studio, with the full suite of coding and debugging tools, in a class library project that can optionally be part of the same solution as the report project. After testing, the assembly will be deployed to trusted folders where it can be used in the SSDT/BIDS report designer and on the report server. I’ll use a Visual Basic.NET project but you could just as easily use C#.
Add a class library project to the solution
1. With the SSRS Student Labs project open in SSDT, drop-down the File menu.
2. Select Add > New Project…
3. In the Add New Project dialog, under the Installed Templates pane, select Visual Basic.
4. In the center pane, select Class Library and name the project Report_Class_Lib.
5. In the Solution Explorer, change the name of the default class file to ReportLib.cls.
In the following example code, the line continuation characters are optional and are provided only so this code fits on the printed page in these instructions. If you prefer, you can omit the underscore and the carriage return on reach line that ends with an underscore.
6. After the Public Class ReportLib declaration, enter the follow code on a new line.
Visual Studio’s Visual Basic code editor will assist you with code completion and debugging. Use these features to complete the code and save yourself some work. When a list of objects is presented, use the arrow keys to select the appropriate object and then press Tab to add it to your code. Press the Enter key to select an object and add a new line to the code.
Public Function SalesQuery(ByVal YearFrom As Integer, ByVal YearTo As Integer, ByVal Country As String, ByVal MaxRows As Integer, ByVal IncludeFrieght As Boolean) As String Dim sSelect As String = "SELECT TOP " & MaxRows.ToString() & _ " CalendarYear, CalendarQuarter, MonthNumberOfYear, MonthName, " & "SalesTerritoryCountry, SUM(SalesAmt) AS SalesAmt, SUM(OrderQty) AS OrderQty" Dim sFrom As String = "FROM vSalesByTimeAndTerritory " If IncludeFrieght Then sSelect &= ", SUM(Freight) AS Freight " Dim sWhere As String = "" If YearFrom <> -1 Then sWhere = "(CalendarYear BETWEEN " & YearFrom.ToString() _ & " AND " & YearTo.ToString() & ") " If Country <> "All" Then If sWhere = "" Then sWhere &= "SalesTerritoryCountry = ‘" & Country & "’ " Else sWhere &= " AND SalesTerritoryCountry = ‘" & Country & "’ " End If End If If sWhere <> "" Then sWhere = "WHERE " & sWhere Dim sGroupBy As String = "GROUP BY CalendarYear, CalendarQuarter, MonthNumberOfYear, " _ & "MonthName, SalesTerritoryCountry " Return sSelect & vbCrLf & sFrom & vbCrLf & sWhere & vbCrLf & sGroupBy End Function
7. Check the code for errors and warnings that will have red or green squiggly lines under keywords.
You are encouraged to write this code yourself using the code completion and debugging tools in Visual Studio. If you need some help or would like to speed up this lab, you can open the SSRS Completed Labs solution and copy the code from the completed version of the Report_Class_Lib project.
8. In the Solution Explorer, right-click the Report_Class_Lib project and select Build from the menu.
Deploy the class library
1. Open Windows Explorer and navigate to the project folder, which should be the following path (if it’s different, that’s OK. Just be mindful of your changes):
C:\Users\Administrator\Documents\Visual Studio 2010\Projects\Report_Class_Lib
2. Click in the address box and copy this path to the clipboard.
If you are an experienced Visual Studio developer, you can replace steps 3 through 11 with another approach to simplify deployment… Create a build event in the class library project properties to copy the assembly file at the end of the build process. Define a post-build event (on successful build). Instead of creating a CMD file, enter the two COPY commands below into the post build event script.
3. Create a new text file in this folder and name it Deploy_Library.cmd.
4. Right-click the file and open it in Notepad.
5. Enter the following command line text into the file. You can start by pasting the path you copied from Windows Explorer in place of the first path in each of the two statements.
Note that there are no carriage returns in the two COPY commands. Enter all of the text for each COPY command into one line with to file paths in double quotes.
COPY /Y "C:\Users\Administrator\Documents\Visual Studio 2010\Projects\Report_Class_Lib\bin\Debug\Report_Class_Lib.dll" "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies"
COPY /Y "C:\Users\Administrator\Documents\Visual Studio 2010\Projects\Report_Class_Lib\bin\Debug\Report_Class_Lib.dll" "C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin"
If you are running in SharePoint Integrated mode, you must also copy the assembly to the shared folder for web server extensions. If so, add this line:
COPY /Y "C:\Users\Administrator\Documents\Visual Studio 2010\Projects\Report_Class_Lib\bin\Debug\Report_Class_Lib.dll" "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\bin"
6. Close Notepad and save changes to the command file.
7. Add this file to the class library project using Add > Existing Item… from the Solution Explorer project right-click menu.
8. In the Solution Explorer, right-click the Deploy_Library.cmd file and choose Open With…
9. Click the Add… button and in the Add Program dialog, use the ellipsis button (…) to browse to the Deploy_Library.cmd file (you’ll need to change the Files of type drop-down option to All Files *.*).
10. Accept the default Friendly Name and click OK to accept the file addition.
11. Click the Set as Default button on the Open With dialog. Click the OK button to save this setting. A command window should open momentarily and then close.
You can double-click the Deploy_Library.cmd file in the Solution Explorer any time to ensure that the latest library is deployed.
12. Use Windows Explorer to verify that the Report_Class_Lib.dll file has been copied to the following locations and has a recent modification date:
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies
I am proud to announce that our Professional SQL Server 2012 Reporting Services book is finally in print and generally available. This 865 page book took about 14 months to write and was completed about six weeks ago. Paperback and electronic editions are available from Wrox.com, Amazon & other resellers.
My esteemed co-authors were Robert Bruckner from the SSRS product team, Grant Paisley, Thiago Silva and Ken Withee; who all did fantastic work.
Thanks go to Joe Salvatore, Chris Albrektson and Nigel Sammy who did a great job as a technical reviewers.
In addition to covering every significant capability of the new professional reporting platform and the integrated SharePoint experience; chapters are dedicated to new BI capabilities introduced in SQL Server 2012 including BISM semantic tabular models, PowerPivot for Excel 2012 and SharePoint 2012, and Power View visualizations.
Table of Contents:
PART I: GETTING STARTED
CHAPTER 1: INTRODUCING REPORTING SERVICES 3
CHAPTER 2: REPORTING SERVICES INSTALLATION AND ARCHITECTURE 23