Press "Enter" to skip to content

Category: Reporting Services

Adding Time Zone-Adjusted Report Execution Times

Published 2020-04-20 by Kevin Feasel

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

Published 2020-03-25 by Kevin Feasel

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

Published 2020-02-19 by Kevin Feasel

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

Published 2020-02-12 by Kevin Feasel

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

Published 2020-02-06 by Kevin Feasel

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

Finding SSRS Log Files

Published 2020-01-17 by Kevin Feasel

Rudy Rodarte explains where you can find SQL Server Reporting Services log files:

After many years working with SQL Server Reporting Services, I’ve gotten myself into a few situations where I had to examine the SSRS log to see what was happening with data sources, subscriptions, and other, unique SSRS particulars. For a time, I thought that the regular SQL Server Error Log was enough. But experience has taught me that there is much more information inside of the SSRS log file than the SQL Server Agent and Service logs. But, you must know where to look to find the SSRS Log file.

Those logs often contain good information.

Comments closed

Displaying SSRS Usage Stats Through Grafana

Published 2020-01-15 by Kevin Feasel

Alessandro Alpi takes queries to view SQL Server Reporting Services data and visualize it in Grafana:

One of the problems that often occur in our organization as well as some of our customers, is to get immediate feedback about usage statistics of reports. Usually, the request of creating reports is out of control and some of them are executed only “that time” and not anymore. In the worst-case scenario, many of them aren’t executed at all and some of them could become even overlapped or duplicated.

Therefore, it is important to know the usage statistics, user by user and report by report, to make the reader aware of them, let him interpreting the values of the same query in multiple ways and graphical layouts. While this is not possible with a tabular format (unless you export the values using any external tools such as Excel) it is simpler when it comes to a dashboard.

And that’s where Grafana excels.

Comments closed

The JOIN Function in SSRS

Published 2019-12-30 by Kevin Feasel

Tim Mitchell explains what the JOIN() function does in SQL Server Reporting Services:

The JOIN() function in SQL Server Reporting Services is a handy tool that allows you to turn a list into a delimited string value. This function accepts two parameters, a list and a delimiter with which to separate the output, and returns a string with that list separated by the specified delimiter character.

It’s not an inaccurate name, but I’m surprised it’s not named CONCAT_WS() or something like that given how the term “join” has such a strong connotation in the relational database world.

Comments closed

Checklist for an Azure VM Running SSRS

Published 2019-12-10 by Kevin Feasel

Kathi Kellenberger has a troubleshooting guide for setting up an Azure virtual machine to run SQL Server Reporting Services:

Recently I set up an Azure VM running SSRS for my students to host their database and report projects. My goal was to set up a custom domain name and use SSL. I ran into a few issues and a couple of wrong paths so I thought this would be helpful for others trying to accomplish the same. I’m not going to do a step-by-step walkthrough, but instead a checklist to help you troubleshoot

It’s not step-by-step, but it’s pretty close.

Comments closed

RSExecRole Already Exists

Published 2019-11-04 by Kevin Feasel

Dave Mason troubleshoots an annoying error:

When migrating an instance of SSRS, I performed a backup of the [ReportServer] and [ReportServerTemp] SSRS databases from a SQL Server 2008 R2 instance and restored them to a SQL Server 2017 instance. After installing SSRS 2017 on the target machine, I ran SSRS configuration and attempted to set the Current Report Server Database to the existing [ReportServer] database I had already restored:

Read on to see the error and Dave’s fix. As I get older and more cantankerous, I realize even further the benefit of rerunnable scripts and repeatable processes. They prevent so many errors of this sort.

Comments closed