Category Archives: SQL Server Reporting Services

Mobile Report Publisher–Dashboards Everywhere

My latest article on RedGate’s SimpleTalk site has just come out: Mobile Report Publisher – Dashboards Everywhere.

In it, I give a quick overview of using SQL Server 2016’s Mobile Report Publisher to create a simple but useful dashboard. Mobile Report dashboards can be assembled easily, and quickly, from SSRS (SQL Server Reporting Services) Shared Datasets.

Reports generated with Mobile Report Publisher are great, as they can be used on, as the name suggests, mobile devices such as phones and tablets, but on websites as well. They fill the need between a traditional report and those analytic reports created by self service tools such PowerBI.

Using the instructions in my SimpleTalk article, you should be able to create your first, simple report. If that wets your appetite for more, I can offer two additional learning paths.

First, there’s my book SQL Server 2016 Report Services Cookbook. In it I have an entire chapter on the Mobile Report Publisher. Being an entire chapter I had more space to go deeper, and provide instructions on pulling data from multiple sources. I also go into the use of other components in the dashboard. You can find the book on both Amazon and my publisher, PactPub’s website.

The second resource is my Pluralsight course What’s New in SQL Server 2016 Reporting Services. The report gives a great overview of all the new features of SSRS 2016 (and still applicable to 2017). The bulk of the course focuses on the Mobile Report Publisher, but I also cover other new features such as the new Report Portal, and the ability to host KPIs right on the portal, among other features.

Don’t have a Pluralsight subscription? No worries, just email me, free@arcanetc.com and I can send you a code good for 30 days during which you can watch my courses, or any of the great courses at Pluralsight!

SQL Server 2016 Reporting Services Cookbook–On Sale Until 2018!

SQL Server 2016 Reporting Services CookbookHappy  Holidays! As a gift to everyone, PACKT Publishing has put the e-book version of my SQL Server 2016 Reporting Services Cookbook on sale for just $5.

Yes, that’s right, for the same price as a pumpkin spice chai tea latte with extra whip cream, and a quick trip to http://bit.ly/ssrscook, you could be reading this magnificent tome of wisdom.

Now, I can year some of you going “but Robert, SQL Server 2017 is out now, is this still relevant?” Yes, absolutely! Very few changes were made to the 2017 version of SSRS, so everything in this book is just as valid.

And it’s not just my book that is on sale, all the e-books at PACKT are on sale for the same low low price. So after you get done adding the SQL Server 2016 Reporting Services Cookbook to your shopping cart, be sure to check out the other awesome titles on PACKT Publishing.

This book, by the way, makes an excellent companion to two of my Pluralsight courses.

The SQL Server Reporting Services Playbook, https://www.pluralsight.com/courses/sql-server-reporting-playbook, is designed for people who are totally new to SSRS and need to get up to speed FAST.

Already know SSRS, and just want to learn what’s new with SSRS 2016? Then my appropriately named What’s New in SQL Server 2016 Reporting Services course, https://www.pluralsight.com/courses/sql-server-2016-reporting-services, is just the thing for you. It goes over just the new features in SSRS 2016 (and still applicable to 2017) and does it fast. With this short course you can be up and running with 2016/2017.

What? You don’t have a Pluralsight subscription? Well I’m shocked, but OK I can help you out. Just send me an email to

free@pluralsight.com

and I can fix you up with a code good for 30 days during which you can watch my courses, or any of the fine courses at Pluralsight. (If you want to see all my courses, just click the About Me link at the top, or go to http://arcanecode.me).

If you have some free time during the holiday season, and want to avoid the in-laws, then curling up with a great book (like the SQL Server 2016 Reporting Services Cookbook) beside the yuletide fire can be a great way to spend your time!

SQL Server Reporting Services Playbook now live on Pluralsight!

My newest course, the SQL Server Reporting Services Playbook, is now live on Pluralsight! If you are looking to get up to speed fast writing reports in SQL Server Reporting Services, this is the course for you. In roughly two hours you will have enough information to start cranking out reports for your organization.

Playbooks though are a bit different than traditional Pluralsight courses. Playbooks are specifically designed so you can watch just one segment to gather that piece of information you need right now, and not have to sit through the entire course. For example, if you were comfortable with generating line item reports, but need a refresher on how to use charts in a report, you could watch just the 15 minute module on charts.

The playbook format is also great for times when, after watching a course, you need to come back and refresh yourself on one piece. The organization makes it easy to find the specific piece of information you need.

You’ll find the course at: https://www.pluralsight.com/courses/sql-server-reporting-playbook

After watching this course, you may wish to go watch my previous course, What’s New in SQL Server 2016 Reporting Services. ( https://www.pluralsight.com/courses/sql-server-2016-reporting-services ). This course is another brief one which assumes you are comfortable with Reporting Services and want to get up to speed quickly on the new features in 2016.

I for one really appreciate this style of course, as it doesn’t waste time teaching me what I already know about SSRS, and instead just focuses on the things that have changed since the previous version. And let me assure you, a lot has changed in SSRS 2016!

What’s that you say? You don’t have a Pluralsight subscription but really want to view this course? Well no problem my friend, just email me <free at arcanetc.com> and I’ll be more than happy to send you a code that will give you 30 days free at Pluralsight to watch mine or anyone’s courses.

By the way, this is my 13th Pluralsight course, you’ll find the whole list at https://www.pluralsight.com/authors/robert-cain . My courses cover a variety of topics, Reporting Services of course, but also PowerShell, SQL Server Integration Services, and a nifty course introducing you to the concepts of Data Warehousing and Business Intelligence.

What’s New in SQL Server 2016 Reporting Services–Now live at Pluralsight

So you want to see what’s new in SQL Server 2016 Reporting Services? Well I’ve got just the thing for you. My 12th course just went live on Pluralsight, named appropriately “What’s New in SQL Server 2016 Reporting Services”.

This course is designed specifically for those who are already familiar with SQL Server Reporting Services, and just need to come up to speed with the new features in the 2016 release. Even better you can do it during a long lunch (or two short ones), the course is just under an hour and a half so you can learn quickly.

The course starts with a look at the brand new Report Portal, the replacement for Report Manager. You’ll see how to create KPIs right in the portal, improvements to subscriptions, and how to custom brand the Report Portal so it can integrate right into your companies websites. After that you’ll learn about the new HTML 5 compatibility, something that makes SSRS integrate even better into your web apps and sites.

Some time is then spent on improvements to what Microsoft calls “Paginated Reports”, essentially the same reports you’ve grown to know and love. You’ll see improvements for embedded reports, as well as the two new chart types, sunburst and treemap. Finally, the long awaited ability to arrange parameters is covered.

Last, but certainly not least, the course covers what I consider to be the most exciting piece of the 2016 release, the Mobile Report Publisher. You’ll see how to use it to generate reports, and how they can be used on the web as well as mobile devices such as phones and tablets.

What’s that you say? You want to see it, but don’t have a Pluralsight subscription? Hey, no problem. Just shoot me an email, free at arcanetc.com. I can send you a code good for 30 days during which you can watch this and any of the over 5,000 (yes, FIVE THOUSAND) courses.

SQL Sever 2016 Reporting Services Cookbook has arrived!

 

SQL Server 2016 Reporting Services Cookbook by [Priyankara, Dinesh, Cain, Robert C.]I’m proud to announce my latest book, the SQL Server 2016 Reporting Services Cookbook, has been released! This was a real labor of love, it consumed most of my summer and well into the fall.

This book was published via Packt Press, and my coauthor was Dinesh Priyankara (blog | twitter).

In this book we cover recipes for almost all aspects of SQL Server Reporting Services. What’s inside? Just take a look:

Chapter 1 – Getting it Ready – Configuring Reporting Services.

Chapter 2 – Authoring Reports with SQL Server Data Tools

Chapter 3 – Advanced Report Authoring with SQL Server Data Tools

Chapter 4 – Authoring Reports with Report Builder

Chapter 5 – Improving User Experience – New Designing and Visualization Enhancements

Chapter 6 – Authoring Reports with the Mobile Report Publisher

Chapter 7 – Consuming Reports – Report Access Enhancements

Chapter 8 – Reporting Solutions for BI – Integration

Chapter 9 – SharePoint Integration

Chapter 10 – Administering and Managing Reporting Services

Chapter 11 – Securing Reports in Reporting Services

Chapter 12 – Custom Programming and Integration to .NET applications

That’s a lot of great material, over 500 pages of Reporting Services fun.

You can get the book through the publisher site:

https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-reporting-services-cookbook

or use this shortcut: http://bit.ly/ssrscook

You can also get it on Amazon.

https://www.amazon.com/Server-2016-Reporting-Services-Cookbook-ebook/dp/B01HY3TC68/ref=tmm_kin_swatch_0?_encoding=UTF8&qid=1480527666&sr=8-6

Or use the shortcut: http://bit.ly/ssrscookbook

Note as of this blog post Amazon has the Kindle version ready, the print version still shows as a preorder, but that will be out shortly. If you want the print version consider going to the publisher site as you can get both the print and e-book version for one low price.

I want to thank my coauthor, Dinesh, who did a great job on his half of the book, as well as in designing the overall contents. Also a shout out to our editor, Amrita, who kept us in line and on track.

Enjoy!

ArcaneCode–Headed your way!

I’ve not done much blogging, as I’ve been swamped with other activities. In addition I’ll be doing quite a bit of speaking, so let me catch you up.

Recently I did a webinar for Pluralsight, “Why you should invest in PowerShell”. If you missed it the recording is now up, take a look, it’s free!

http://go.pluralsight.com/C0010781

Next, I just completed the first draft of my fifth book, SQL Server 2016 Reporting Services Cookbook. I’m coauthoring with another MVP and great guy, Dinesh Priyankara. The book is available from PACKT Publishing in Alpha form.

https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-reporting-services-cookbook

I’ll make my debut appearance at IT/Dev Connections next week. I’ll be doing two sessions, the first is on October 11th, 2016: Zero to Hero with PowerShell and SQL Server. We’ll begin with a quick overview of PowerShell, then dive into using it with SQL Server. You’ll see examples of using it for both maintenance and development tasks.

The next day is my second session is “So You Think MDX is Hard?”. This is for people who are new to MDX, and want to learn. You’ll see how to start from no knowledge all the way to building calculated members and sets.

If you’ll be at IT/Dev Connections feel free to come by and say hi, would love to meet as many as possible.

As if that’s not enough, on Saturday October 15th 2016 I will be at the DevSpaces Conference in Huntsville AL. At 4pm I’ll be presenting “High Class PowerShell: Objects and Classes in PowerShell”. You’ll see how to create your own classes using PowerShell. We’ll cover techniques valid in PowerShell versions 3 and 4, as well as see how the new class types in PowerShell version 5 work.

As they say on TV, but wait! There’s more!

On November 1st 2016 I will be coming to Atlanta to the Atlanta SQL Server BI user group. My presentation “Shiny and New: SQL Server 2016 Reporting Services” should be a lot of fun, and introduce you to the new features in SQL Server 2016.

Whew! There’s two more Atlanta based events in Nov/Dec it looks like I’ll be at, once those are finalized I’ll let everyone know.

I’ve also established a GitHub repository for my various samples. You’ll find it at https://github.com/arcanecode. As I move forward I’ll keep this repository updated.

SSRS 2012 Report Manager can’t load Microsoft.ReportingServices.SharePoint.ObjectModel

So I did it again, I broke my SQL Server. Well, sort of. I have a Hyper-V VM of Windows Server 2012R2 I use for development. On it I had SQL Server 2012 Developer Edition with all the latest service packs. I recently needed to do some work with 2014 as well, so installed SQL Server 2014 Developer Edition side by side. Everything seemed happy, until I opened up the SQL Server 2012 Report Manager webpage. It looked OK at first, but when I started clicking on things I started getting this error:

System.Configuration.ConfigurationErrorsException: Could not load file or assembly ‘Microsoft.ReportingServices.SharePoint.ObjectModel’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference

Icky. So a web search turned up one hit, a connect item filed by Brian Judge:

https://connect.microsoft.com/SQLServer/feedback/details/1088671/sql-server-2012-reporting-services-errors-after-installing-sql-server-2014

At the bottom, Brian gives the clue on how to fix the issue when he says:

If I change the redirect to stay on 11.0.0.0 for the following policies then the problem appears to be resolved:

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint.ObjectModel

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint.Server

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint12.Server

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint14.Server

 

Alas, there are no specific instructions on just how to change the redirect. For those not familiar with the way these things work, I wanted to amplify his fix.

First, open a command window in administrator mode. I used the one that came with Visual Studio (the Developer Command Prompt for VS2012).

Next, change directory by using the “cd” command to the first item in the list above. (Click on the pic for a bigger image, should you have poor eyesight).

image

Using the DIR command, we can see one directory with a version number followed by what appears to be a hash value of some type. Issue another CD into that folder.

image

Using the DIR command again you will find two files in that folder:

image

Use notepad to edit the one with the .config extenstion.

image

When it appears, you will see something like:

image

Simply change the number in the newVersion from 12, to 11.

image

Repeat the steps for all four of the folders in the list above.

Next, and this is important kids, you need to stop and restart your SQL Server 2012 Reporting Services service, or simply reboot the computer. After that, your SSRS 2012 Report Manager should start to behave normally again. I’ve also tested the 2014 Report Manager, and it seems to work fine after the changes were applied. (In theory it shouldn’t have been affected, but you can never be too careful).

If you found this post useful, do us a favor. Go to the Microsoft Connect article linked at the top and give it an up vote, so Microsoft will begin to take notice. Also thanks again to Brian Judge (whom I do not know but hope to meet) for filing the original bug and giving the clue to fixing it.

SQL Saturday #111–Atlanta

Today I’m presenting not one but two sessions at the Atlanta SQL Saturday. I wanted to provide copies of my slide decks here.

Configuring SQL Server 2012 Reporting Services

The Decoder Ring to Data Warehousing / Business Intelligence

Hope you enjoyed the sessions, and thanks for coming.

SSRS Training Resources

I’ve been asked to provide links to some useful resources for learning about SQL Server Reporting Services. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.

Books

Microsoft SQL Server 2008 Reporting Services Step by Step – A great beginner book, loaded with good examples.

Pro SQL Server 2008 Reporting Services – This book goes much more in-depth with SSRS, delves into many advanced topics.

Microsoft SQL Server Reporting Services Recipes – 2008 or 2012 version of book. This is a great book, especially if you are doing Business Intelligence reporting. Note Amazon says the 2008 version is no longer available in the US, but I’m betting you can find it in your local bookstore or from other retailers. The 2012 version is available for pre-order.

Applied Microsoft SQL Server 2008 Reporting Services – Great book, like the book above covers many aspects of SSRS including BI reporting. Note Amazon only sells the paper version, you can also get it in PDF format direct from the publishers website.

Professional SQL Server 2012 Administration – I mention this book because I wrote the chapter on SQL Server Reporting Services. I don’t go deep into creating reports, although I briefly cover Report Builder. I do go into configuring SSRS and how to do scale out deployments, the total chapter is around 50 pages.

Blogs

Paul Turley – Paul is an active blogger and fellow Microsoft MVP. He is also co-author of the Reporting Services Recipes book I listed above.

Tep Lachev – An active blogger, Teo is not only a good resource for SSRS but for other BI tools such as PowerPivot. He is also author of the Applied Microsoft SQL Server 2008 Reporting Services book, listed above.

Videos

Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on SSRS.

Pluralsight – Pluralsight has an extensive catalog of courses, including some great SSRS content. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanessrs

Using TFS2010 with Visual Studio / BIDS 2008 and SQL Server Management Studio

When I come to a customer site, I often have to help them get setup with TFS (Team Foundation Server) 2010, Microsoft’s source code control / ALM (application lifecycle management) system. This is so they can work with their BIDS (Business Intelligence Developer Studio) projects as a team, giving the added benefit of source code control. I’ve had to do this often enough I wanted to record the steps for my own use, and hopefully others too.

Installing the TFS 2010 tools for Visual Studio / BIDS 2008

First off, thanks to Derek Miller for covering most of the steps involved in his blog post http://derekjmiller62.wordpress.com/2010/10/19/using-tfs-2010-with-bids-2008/. I won’t go into the detail he did, but will summarize into these basic steps.

1. If you haven’t installed Visual Studio 2008 Service Pack 1, do so by downloading it and installing.

2. Next, you will need to install the Visual Studio 2008 Team Explorer.

3. After installing Team Explorer, you will have to go back and reinstall VS SP1 (from step 1). Don’t skip this step! Team explorer has some older components that overwrite the SP1 components, and you will have reinstall them.

Now this next part I really haven’t seen anywhere else and was a real pain to find, and thus is the main reason for this post. During the SP1 install, we often see “Visual Studio SP1 Installation Failed”. Checking the error log, buried deep you will find “Returning IDOK. INSTALLMESSAGE_ERROR [Error 2902. An internal error has occurred. …”

When you see this, go to your Control Panel, and then to Add Remove Programs. Look for a program called “Microsoft Visual Studio Web Authoring Component” and uninstall it. This is actually installed as part of the Office suite, and you don’t really need it since you likely have much more powerful web authoring tools, or since you are doing BI development won’t be doing an web development in Microsoft Office.

After uninstalling it, SP1 should then install, and you are ready for step 4.

4. Install the Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010. That probably took you longer to read than it actually will to install. After installing, it may prompt you to reboot. Even if it doesn’t ask you should reboot anyway, we’ve seen a few times when we weren’t able to connect until we rebooted.

After that you should be able to go into Visual Studio and go to Tools, Connect to Team Foundation Server. If you still have problems connecting, I will refer you to Derek’s post where he describes some registry entries you can try. So far we haven’t found them necessary, but you may.

Installing the TFS 2010 Tools

Note that there is one big limitation to using TFS 2010 with VS2008. You can connect to a TFS site and upload your solutions and projects, but you can’t create a new team site with VS2008. To do so, you will need the VS2010 shell with the TFS components, a free download.

Installing TFS 2010 for SQL Server Management Studio (SSMS)

Now that you have BIDS all setup to work with TFS, it only makes sense to make your SQL Server Management Studio (SSMS) also work with TFS. Joseph Jun has a great blog post that goes into all the nitty gritty of how to do this. The short version though, is after you install the TFS 2010 tools in the step above (and they are a prerequisite) you need to install the Team Foundation Server MSSCCI Provider 2010.

After the install, you should see a new Source Control menu option under the File menu in SSMS. From here you can launch the TFS 2010 management shell or open an existing SSMS project / solution. If you have a solution you need to add, simply right click on the solution in the Solution Explorer window and pick Add to Source Control.

Visual Studio Database Projects

Note that if you are using Visual Studio Database Projects, any SQL Server 2008R2 development must be done in Visual Studio 2010. VS2010 is already setup to talk to TFS 2010. If you are using VS 2008 database projects to build a SQL Server 2008 (non-R2) database, then with the steps above you should be good to go for checking in your database project into TFS.

And away we go!

And with that you should be setup to manage your BI Development in Team Foundation Server 2010. It’s a lot of work, but well worth the effort. Using TFS will let your BI staff work as a team to develop projects. Additionally you have the benefit of source code control, something invaluable in the case of package corruptions or needing to track history.

BI Documenter

When I came to work for Pragmatic Works, I was naturally given the opportunity to use their (well our now) tools. Of all of them I think BI Documenter is my favorite. Boy is this thing complete.

Of course, like some of the other SQL Server documentation packages it will do a great job of reverse engineering an existing relational database. BI Documenter will output either HTML or a compiled help file (CHM) file. It places your database structure into a drill down tree, with all the expected bits and pieces. Tables, columns, stored procedures, functions, all with the code needed to create it.

It doesn’t stop there though. Got Analysis Services? Not a problem. It will generate the same drill down structure that you are used to seeing for a standard SQL Server database. Cubes, Measures, Dimensions, KPIs, Calculations, complete with all the meta data and MDX you could ever want.

Of all the features though, the absolute coolest is it’s support for Integration Services. You can point it at either your SSIS server, or to a file store, or just to the directory with your solution. Here’s a sample that uses the package from my Intro to SSIS presentation:

image

Not only does it provide detailed info (it appears below the pic) but it will also reproduce the graphical flow! And if that wasn’t cool enough, you can drill down into the other executable parts, such as the data flow.

image

 

Here’s a small sample of the details:

image

It doesn’t stop there either, providing complete support for SSRS as well. Just point it at your Reporting Services server and away it’ll go!

Now, I realize this sounds a lot like a commercial, and since I now work for Pragmatic Works probably more so. Bu I just think this is an awesomely cool product, I can think of a lot of uses for it too. Providing turn over documentation, running it weekly to create version snapshots so you can track changes to your SQL Server, providing reference material for developers, and probably a zillion other things I haven’t thought of. (If you have thought of some, by all means leave a comment, would love to hear how YOU are using this.)

I’m sure I’m not doing the product justification, so if you want to see some video demos head on over to http://pragmaticworks.com/ for more info. And again, I apologize if this sounds like a commercial but I am blown away by how cool this product is so I just had to share.

 

 

 

Disclaimer: I do work for Pragmatic Works, and received my copy of this really cool software as a result of my employment.

Data Warehousing / Business Intelligence at Pluralsight

It’s been another busy month, and I’m pleased to announce my two newest modules were published today on Pluralsight. If you’re not familiar with Pluralsight, they are a training company that has a vast library of training videos.

The description of my new course can be found here:

http://www.pluralsight-training.net/microsoft/olt/Course/Toc.aspx?n=intro-dwbi-course

There are two modules, the first is an introductory session for DW/BI. It’s primarily slides, by the end you’ll have a grasp on the arcane terms around Business Intelligence such as facts, dimensions, surrogate keys, OLAP, and more.

The second module is an overview of the Microsoft tools for doing DW/BI. It starts with the Adventure Works Lite database. From there a data warehouse is built, on top of which an analysis services cube is created. Finally a report will be generated off the cube that meets a specific business need.

Along the way attendees will get to see Visual Studio 2010 Database Projects, along with the SQL Server toolkit: SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services. Attendees will also get a look at SQL Server Management Studio.

Enjoy!

SSRS Quick Tip – An item with the same key has already been added

I was in the process of creating a new report in SQL Server Reporting Services today. I was loading my dataset from a stored procedure, and when I hit the “Refresh Fields” button I recieved the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

When I clicked the details button I got this further information:

“An item with the same key has already been added.” Here’s a screen shot of my error.

Well this had me scratching my head, as I had made sure to run the stored procedure, and it executed with no errors. After doing some considerable research I finally found a question in the Technet forums that was tangentially related to the error. This gave me the clue to figure out what I had done.

In my stored procedure, I had inadvertantly included the same column name from two different tables. My query looked something like:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99
FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.

As it took me a while to figure this out, tought I’d pass it along to anyone else who might be looking.

Linked Subreports in SQL Server 2008 Reporting Services

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

The previous lesson showed how to include a subreport into another report. This could be used to link independent reports together into a single report. It can also be useful to have a related subreport. A subreport whose data is driven by that of the main report. This can be accomplished by the use of paramenters.

For this lab we’ll create a subreport that returns category totals for the region passed in from the main report. Note that this is a greatly simplified example to illustrate the technique. Even though in this sample everything comes from the same database, each report could just as easily come from completely different data sources. Subreports would be a great technique for combining data from different systems.

Step 1. Create the subreport.

Use Contoso as the shared datasource. For the query, enter:

SELECT [Region]
     , [ProductCategoryName]
     , SUM([TotalAmount]) AS ProductTotal
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] = @pRegion
 GROUP BY [Region], [ProductCategoryName]
 ORDER BY [Region], [ProductCategoryName]

Use a tabular report, move everything into the details area, Generic for the table style, and for the report name use “Subreport – Region Category Totals”.

Step 2. Cleanup the subreport.

Click the edge Region texbox in the header (so it’s selected instead of being edited), and press delete. Repeat with the [Region] textbox in the detail row. We won’t need it since Region will be displayed on the parent report.

Change the other headers to Category and Total. Make them wider, make what had been the Region column smaller but leave it, it will give a nice indent padding when included on the parent report. In the textbox properties for ProductTotal, make sure to set the Number to Currency, and in the Alignment area change the Horizontal to right align.

Remove the “Subreport – Region Category Totals” text box

Click on the main table grid, then move it to top of body. Collapse the body to fit the table.

Step 3. Add the parameter.

In the Report Data window, right click on Parameters and pick Add Parameter. Name the property Region. For the prompt, enter “Region – Hidden”. Since the prompt will never be visible, it really doesn’t matter, but making a habit of entering the name and the word Hidden will give a clear indicator that this parameter is a hidden one.

Leave the data type set to text, and check on “Allow blank value”. If you don’t, the report will error out when used as a subreport. Next, set the visibility to Hidden. This means it won’t appear if you run the report, but you can still pass in parameters, from another report or via a URL. Click OK to close the properties window.

Finally, we need to bind the parameter to the parameter the dataset needs. Right click on the dataset and go to properties. On the parameters area @pRegion should already be present (remember, it was part of the WHERE clause in the SQL query). Pick @Region in the drop down for Parameter Value.

Step 4. Create the main report.

Add a new report, using Contoso as the shared datasource. For the query, use:

SELECT [RegionCountryName]
  FROM [Report].[V_Regions]

Use a tabular report, move the RegionCountryName to the details area, and pick Corporate for the style. Finally, for the name use “Regional Report”.

Step 5. Layout the report.

Since there’s only one column, expand it to take up the width of the body.

Right click on the row selector (the gray box with the lines on the left of the table) and pick Insert Row->Inside Group Below.

Into that area, drag a Subreport control from the toolbox. Note in this case there is only one column, but if there were multiple cells you could highlight them, right click and pick Merge Cells.

Step 6. Setup the subreport.

Right click on the subreport control.

Under “Use this report as a subreport” select the “Subreport – Region Category Totals”. Under the parameters area, click Add. Select Region under Name, and for the Value select RegionCountryName.

Step 7. Preview the report

Preview the report to see your results:

clip_image002[4]

Notes

Just a few notes. In this report, we left the table headers in the subreport (Category and Total). Often these are removed, to make the subreport blend in more with the parent.

Here only one parameter was passed, however you can pass multiple parameters if you need to.

Adding Charts to SQL Server Reporting Services Reports

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

Often adding a chart can help give perspective mere numbers cannot. A quick glace can allow users to quickly focus on the important areas of data. Reporting Services supports many chart types, all of which are highly customizable. In this lab we’ll add a simple chart to the executive summary report we created in the previous lesson.

Step 1. Clone an existing report

Often we start new reports from an existing one. In solution explorer, right click on the “Subreport – Executive Summary” and pick Copy. Now go to the base of the project tree and pick Paste. In the Solution Explorer you’ll now see “Copy of Subreport – Executive Summary”. Rename it “Subreport – Executive Summary with Graph”. (Note, if you don’t have this you’ll need to do yesterday’s lesson, or download the code from the Getting Started post, mentioned at the top of this post).

Step 2. Make room for the chart.

Expand the body to double the width.

Step 3. Add the chart control.

In the toolbox, grab the Chart and drag it into the empty area in the body. You’re then asked what type of chart to create, pick a pie chart (it will be the top right one in the “Shape” category). Click OK.

Click inside the pie chart. You’ll now see small boxes slide out to the side and top/bottom of the chart control. Go to the Report Data tab. In the Contoso Dataset1 click on CategoryTotal and drag it above the chart to the area that reads “Drop data fields here”.

Next, click on the ProductCategoryName and drop it below the chart where it reads “Drop category fields here”.

Right click on the title, and pick “Title properties”, then for title text click the fx expression button. For the expression enter:

=Sum(Fields!CurrentYear.Value, "CurrentFiscalYear") & " Values"

(To see an explanation on the logic for this see the previous lesson).

Step 4. Preview the report

Click on the Preview tab. You should now see your chart, linked to the data.

clip_image002

There are many more customizations you can do to a chart, this lab demonstrated how quick and easy it is to add charts of all types to your reports.