Category Archives: SSRS

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.

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!

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.

Task Factory–SharePoint Source

Microsoft SharePoint has taken the enterprise by storm. A staggering number of corporations today are using SharePoint as their collaborative and information backbone. A tremendous amount of information is being stored within SharePoint lists.

As such, it’s becoming more and more important to be able to quickly and easily extract the data in those lists and bring it into other platforms for analysis, reporting and storage. SQL Server Integration Services seems like the ideal tool for moving and transforming this type of data around, but accessing data in SharePoint lists is not a trivial task.

Unless of course you have Task Factory. Task Factory is a suite of SSIS components from Pragmatic Works. In this video, we’ll look at the SharePoint Source component.