Category Archives: SSRS

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!

Advertisements

Opening Port 80 in Windows Firewall to Support Calling SSRS From Another Computer

Recently I was working on another article for RedGate’s SimpleTalk site. As part of it, I had SSRS installed on a Windows 10 computer, and needed to connect to it from another computer. I was having a lot of issues connecting, until I remembered SSRS connects using Port 80, and by default Windows 10 (and previous versions) block Port 80 for incoming traffic.

The solution was to, obviously, open Port 80 on the Windows 10 computer. Doing so was not difficult, but did require quite a few steps, and of course administrator rights on the computer.

First, open the Windows 10 Settings. Then, click on Network & Internet.

image

On the Status window, click on Windows Firewall.

image

From here, click on Advanced settings.

image

If prompted confirm you do wish to make changes. When the Windows Defender Firewall dialog appears, click on Inbound Rules.

SNAGHTMLef395d1

Now click on New Rule

image

In the New Inbound Rule Wizard window, change the type of rule to be Port. Then click next.

image

On the next window, leave the rule applying to the default of TCP. For the port, assuming you are using the default setup, enter 80 for the port number. If you setup SSRS on a different port then obviously use that port number instead.

image

On the action page we tell Windows what we want to do if it finds incoming traffic on this port. For this development environment we will take the default of Allow the connection. If you had setup https service on your report server, then you could take the second option of allow if secure.

image

Next, we need to specify what network type the rule should apply to. For the scenario, I am on a small network, such as you might have at home, and that network was setup as private. Thus I am leaving Private checked on, and unchecking Domain and Public.

Unchecking public is especially important if you plan to take your laptop out to a coffee shop, you don’t want someone trying to hack into your machine via port 80. When done just click next.

image

On the last screen we’ll give the firewall rule a name, and a description. When done, click finish.

image

As you can see, the new rule now appears in our Inbound Rules area.

SNAGHTMLf01e748

Once you have completed working with your SSRS server, I’d suggest you return here, right click on the rule, and either disable it, or if you know it will no longer be needed, delete it.

And with that you should now be able to connect to the computer running SSRS from another computer on your network.

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.

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.