Press "Enter" to skip to content

Category: Reporting Services

SQL Server Assessment Reports

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

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

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

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

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

Adding Time Zone-Adjusted Report Execution Times

Brett Powell shows how you can display a report’s execution time in a particular time zone:

For reports being viewed by users around the world, simply modifying the footer text box expression to note that this time is UTC may be a sufficient. However, for many paginated reports the users are all in one time zone and some of these users may ask to have the time zone conversion handled within the BI solution. The example in this post targets this scenario.

Even if the report serves users in multiple time zones, it’s technically feasible to leverage the UserID global field and a simple user to time zone mapping table to provide a local report execution time to all users. However, I tend to think most projects would not want to commit the time/resources for this logic – UTC date/time is what the users would get.

If you do need local report execution time, Brett has you covered.

Comments closed

Upgrading SQL Server Reporting Services to 2017

Alexandre Hamel walks us through the new process for upgrading SQL Server Reporting Services:

In the past, we could run the SQL installer to do an in-place upgrade of SQL server including the SSRS instance to a newer version. As of 2017, SSRS is a separate install from SQL server, so this is no longer possible. In fact, if you do an in-place upgrade of SQL 2014 to 2017 for example, you will see a warning that SSRS will be uninstalled. Before proceeding with the SQL upgrade, follow these steps to upgrade the SSRS instance.

It’s not as easy as it was before, but Alexandre takes us through the step-by-step process and even includes some notes on how to roll back your upgrade attempt if necessary.

Comments closed

Troubleshooting Chrome + Reporting Services Issues

Wayne Sheffield walks us through troubleshooting a few issues with using Reporting Services in Chrome:

I was recently working with a client with a SQL Server Reporting Services (SSRS) issue. Their company has standardized on using Google Chrome for the browser. However, they were running into issues when using Google Chrome with SSRS reports.

The first issue was that they were receiving a log in prompt to the SSRS server when browsing to it. The second issue was the infamous Kerberos Double-Hop issue. If you’re not familiar with the Kerberos Double-Hop architecture, check out this link: https://docs.microsoft.com/en-us/archive/blogs/askds/understanding-kerberos-double-hop.

I still have bad memories of trying to get Mozilla and (much earlier) Chrome working with Reporting Services. Ugh.

Comments closed

Uploading Multiple RDL Files to SSRS

Stuart Ainsworth shows us how to push several SSRS reports at a time:

My QA folks have a problem; development has been working on migrating a bunch of reports from a legacy 3rd-party application to SQL Server Reporting Services. Development has finished their “sprint” (*cough* waterfall), and handed over 52 reports to QA, and told the to load them into their QA server. The problem? The web interface only loads 1 report at a time.

Me, being the DevOps guy that I am, thought “well, that’s just silly; there has to be a way to upload all of the reports at once, rather than clicking on some silly buttons over and over again”.

Read on to see what Stuart did next.

Comments closed

Troubleshooting Slow Power BI Report Server Reports

Jamie Wick helps us figure out why that Power BI Report Server report is loading so slowly:

Troubleshooting “slow” reports in PowerBI Report Server (or SQL Server Reporting Services) can be an arduous task. End users are often unable to provide detailed (or reliable) data that a report took longer to load today than it did the last time it was run. Even if a user states that the report is now taking 10 seconds longer to load, that additional time needs to be attributed to a specific step in the report generation process before it can be improved/fixed.

In the report server database (ReportServer by default) there is a view (ExecutionLog) that can provide detailed statistics about each execution of a report. Note: ExecutionLog3 view is the newest/current version and the ExecutionLog and ExecutionLog2 views are for backwards compatibility. By default the execution log entries are retained for 60 days.

The view that Jamie shows also works for SQL Server Reporting Services reports, so it can help there as well.

Comments closed