Press "Enter" to skip to content

Category: Reporting Services

Refreshing Power BI Report Server Reports

Published 2021-01-29 by Kevin Feasel

Aaron Nelson has some new cmdlets for us:

I just wanted to give everyone a heads-up that a new version of the ReportingServicesTools module went out last week, and it includes 3 new PowerShell functions for working with Power BI reports on a Power BI Report Server (PBIRS) instance.

You can now Create (New), Get, & Start a CacheRefreshPlan of a Power BI report deployed to a PBIRS instance.  For clarity, these only apply to reports using an Imported model, not those using Direct Query.

Click through for more detail.

Comments closed

Displaying Always Encrypted Data in SSRS with a gMSA

Published 2020-11-18 by Kevin Feasel

Ryan Adams shows how we can view Always Encrypted data in SQL Server Reporting Services when SSRS uses a Group Managed Service Account:

Always Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.

Click through for the solution.

Comments closed

Deploy Reporting Services Projects with Powershell

Published 2020-11-12 by Kevin Feasel

Aaron Nelson has a pair of new Powershell cmdlets:

I built two new PowerShell commands to deploy SSRS projects, and they have finally been merged into the ReportingServicesTools module. The commands are Get-RsDeploymentConfig & Publish-RsProject. While the Write-RsFolderContent command did already exist, and is very useful, it does not support deploying the objects in your SSRS Project to multiple different folders in your report server. These two new commands can handle deployment to multiple folders.

Click through for details on each.

Comments closed

Overriding SSRS Authentication

Published 2020-08-14 by Kevin Feasel

Eitan Blumin doesn’t like the SSRS authentication prompt:

In this post, I hope to summarize the various methods that we have, in order to get rid of that annoying authentication prompt. Each method has its own advantages and disadvantages in terms of complexity of implementation, versatility, and the level of security that it provides. More specifically: the more secure and versatile a method is – the more complicated it is to implement.

Read on for four such techniques, as well as a bonus technique.

Comments closed

Lookup Functions in SSRS

Published 2020-07-28 by Kevin Feasel

Slava Murygin has made a mistake:

There is a lot of blog posts in the Internet about how to use Lookup and other functions of this type in SSRS.
It is very straight forward and easy to use.
However, I’ve managed to make a mistake and I assume have the same problem if you got to that page.

Read on to see the error as well as the wrong way and the right way to solve the problem.

Comments closed

SQL Server Assessment Reports

Published 2020-07-10 by Kevin Feasel

Dave Bland shares the results of a lot of effort:

When you click the SQL Server 2012 or newer you will be taken to a report that will return a great deal of information that will be useful when doing an assessment.  This is very similar to the report you will be taken to if you click the 2008r2 or older button.  Since many of these data points are not options in an Azure SQL DB, that button will take you to the Report Library.

Below are the 21 data points that will be returned in just a few seconds.  A number of these will be helpful when doing an assessment for performance reasons.  The boxes will also change color to yellow or red if issues are found, just like the image above.

Click through for samples as well as the download link.

Comments closed

Licensing for SQL Server Reporting Services

Published 2020-07-08 by Kevin Feasel

Denny Cherry explains licensing scenarios for SQL Server Reporting Services:

When you license SQL Server (of which the SQL Server Reporting Services engine is a part of) you license what is called the OSE or Operating System Environment. This is basically the OS that has SQL Server installed on it. Now, this can be the virtualization host (VMware or Hyper-V) or it can be the Windows Server (SSRS isn’t available on Linux, so we don’t have to deal with that, but if SSRS was available on Linux the rules would be the same as Windows). You can install SQL Server (or SSRS) as many times inside that OSE as you want to, but you can’t install SQL Server (or SSRS) on any other machines.

SSRS licensing isn’t too difficult to understand, relatively speaking.

Comments closed

Analysis versus Reporting and Power BI

Published 2020-06-12 by Kevin Feasel

Rob Collie thinks about industry movement between analysis and reporting. Part one gives us some backstory:

Excel was about to make a large investment in BI-related capabilities, and the powers that be had selected me to lead our part in it. I was excited, but now I needed a crash course in “what the hell is BI?” I was given multiple tutors, and they all were quick to introduce the concept of Analysis versus Reporting. The “versus” seemed to be pretty important. It wasn’t an “and” – no, the “versus” was chosen deliberately in these sermons. You see, these were Two Very Different Things.

I struggled mightily to grasp this difference. I was told that interactive things like PivotTables were Analysis tools – NOT Reporting tools! Reports were something completely different. “But,” I pointed out, “they’re called ‘Insert PivotTable Report’ on the Excel menu today!” (This was Excel 2003). “Yeah,” said the mentors, “…we might want to fix that.”

Part two explains why analysis and reporting are both important:

Another “meta characteristic” of paginated reports is that they TEND to display details rather than aggregations. EX: specific transactions rather than emergent trends. In paginated reports, you’re MORE likely (but not guaranteed!) to be looking at “raw” rows of data from the original database, whereas in a Power BI report, you’re more likely (but again, not guaranteed!) to NOT be seeing raw individual rows, but rather intelligent aggregations of MANY rows. But either way, more detail means you’re more likely to need multiple pages.

Rob’s right on the money. And I’m looking forward to part three of the series.

Comments closed

Switching Between Solution and Folders Views with SSRS Projects

Published 2020-05-29 by Kevin Feasel

Elizabeth Noble deals with a head-scratcher:


This past week, I made the goal of automating the deployment of our first SSRS report at work. I created the report and after adding the report to source control, my Object Explorer looked like the image below.

I added my solution to source control and synced the project up to Github. However, when my colleague tried to clone the repo and open the Report Project, they saw an image like the one below.

Click through for the images (which really tell the story) and how you can fix this problem.

Comments closed

Tips for a Smooth Power BI Report Server Experience

Published 2020-05-13 by Kevin Feasel

Josh Crittenden has a few tips for people installing Power BI Report Server:

As is the case with any enterprise BI product, careful planning and consideration of how the technology will be leveraged should be made up-front. This will help avoid speed bumps during implementation and ensure a smooth ride as you roll out your BI and analytics solutions. Whether you’ve been working with PBIRS from day one, or are starting to use the platform for the first time, the goal of this post is to showcase tips and techniques that you can turn around and apply in your own environment.

Additionally, for those of you considering a hybrid Power BI implementation (Power BI service and Power BI Report Server), we will compare specific features of each platform below.  

Read on for the tips.

Comments closed