Press "Enter" to skip to content

Category: Reporting Services

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

Reporting Services and SPNs

Published 2019-09-09 by Kevin Feasel

Greg Dodd shares a couple tips on creating SPNs for SQL Server Reporting Services:

Reporting Services often requires an SPN assigned to the account running the Reporting Services Service. You’ll know that you need to set this up when you try connecting to your Reporting Services instance from within the same domain and you are prompted for credentials. If SPN’s are setup correctly then your browser will work out the authentication for you and your users won’t need to login again.

Read on for an example, but also a pitfall and how to avoid it.

Comments closed

Power BI and SSRS Drillthrough

Published 2019-07-08 by Kevin Feasel

Paul Turley continues a series on drillthrough. First, from a paginated report into Power BI:

In the recipe titled “Drillthrough from Power BI to Paginated Report” I demonstrate how to use report parameters and a dynamic URL address to navigate between reports and pass parameter values in the URL from a Power BI report to detailed data in an SSRS paginated report. Using a similar technique, we can navigate from a paginated report to a Power BI report.

Power BI is very flexible and does not require you to define parameters like you would in a paginated report. You can literally filter a report using any field in the dataset.

After that, Paul posted a follow-up on the wherefore:

I recently published two blog posts to share some of my work-in-progress for the recipe book: Drillthrough from Power BI to an SSRS Paginated Report, and: Drillthrough from Paginated Report to Power BI. Both of these posts demonstrate navigation from one of the report tools to the other, essentially allowing users to work with these two capable tools in a full circle.  As the newer and more modern data analysis tool, Power BI fills and important gap but it is not a replacement for the type of multi-page grouped and repeated style of reporting that SSRS is optimized for. Likewise, Power BI excels as an interactive tool for data discovery and self-service analysis. SSRS and Power BI were borne from the same platform and and have common architectures but also many differences. Used together, the realm of possibilities is expansive.

It is important to understand that the techniques used to navigate and pass filter context between these report tools is limited to the capabilities of web query string parameterization. In my experience, this is rarely a show-stopper but I do occasionally encounter folks pushing the limits – both practically and technically.

It’s good to see interoperability between these two tools.

Comments closed

Drillthrough from Power BI to SSRS

Published 2019-07-05 by Kevin Feasel

Paul Turley shows how you can drill through from a Power BI dashboard into an SSRS report:

This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.

The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).

These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.

You can’t get the files just yet, but you can see what Paul does to get this working.

Comments closed

Sort Descending in SSRS

Published 2019-05-02 by Kevin Feasel

Nate Johnson fixes an annoying problem with SSRS:

But you know what’s kinda annoying? You can’t dictate a “first sort direction” — it just assumes that the first time you click the sort-arrows, you want ‘Ascending’ (lowest first). Then you can switch to ‘DEscending’ (highest first). This makes perfect sense for alpha values (strings), but not always for numeric values — at least not when you’re dealing with money, when generally the highest dollar amount is the most important!

So let’s make it real simple for the end-user to get the ‘best’ behavior by default. Let’s try to make it sort by our ‘Revenue’ column in DEscending order first. Ready?

It’s an unintuitive and easy solution to the problem.

Comments closed

SSIS Catalog Dashboard

Published 2019-03-08 by Kevin Feasel

Tim Mitchell has a new GitHub repo:

The SSIS Catalog Dashboard is a simple collection of reports that provide insight into the activity within the SSIS catalog. The first of these is the Dashboard report. This report shows a summary of the number of packages that are running or have run in the recent past.

The dashboard repo, a Reporting Services project, is available on GitHub and is licensed under GPL version 3.

Comments closed