Press "Enter" to skip to content

Category: Reporting Services

Migrating SSRS between SQL Server Instances

Published 2022-07-27 by Kevin Feasel

Garry Bargsley performs a migration:

Good evening. Today’s episode is coming to you from my home office, where I feel motivated to write a blog in the comfort of my home.

Today we will discuss migrating SSRS from one instance of SQL Server to another. Several methods are available for you, but I will show you how to backup and restore the Reporting Services components for a full migration.

Read on for the process and pay special attention to Garry’s notes about encryption key handling.

Comments closed

Errors Sending Subscription E-mails in SSRS

Published 2022-07-21 by Kevin Feasel

Garry Bargsley sorts out an e-mail problem:

Recently, I was tasked with creating an email subscription to a new SSRS report in an environment that I was not familiar with.  I have created my fair share of subscriptions in my day, and this one was very straightforward.

I found the report, clicked on Manage, and went to the Subscription page.  Clicked on New Subscription and filled in all the information, easy peasy.

The subscription is ready to go when the schedule kicks in the next day, or so I thought.

Turns out that wasn’t quite the case. Read on to see what happened and how Garry fixed the problem.

Comments closed

Searching SSRS Reports for Keywords

Published 2022-05-20 by Kevin Feasel

Hannah Vernon does a search:

Microsoft’s SQL Server Reporting Services, colloquially known as SSRS, provides a great way to expose data from SQL Server in human-consumable form, typically via a web site. Generally, it’s a good idea to use a stored procedure as the source of data for SSRS Reports. In a large SSRS environment, it can be challenging to determine which reports use a specific stored procedure.

This code provides details about SSRS Reports where the definition of the report includes any specific keyword, such as the name of a stored procedure.

Click through for a query against ReportServer.

Comments closed

Reviewing the ReportServer Database

Published 2022-05-16 by Kevin Feasel

Ed Pollack opens the lid:

SQL Server Reporting Services is a convenient application for generating reports quickly and efficiently. Its back-end components are a bit more confusing to an unsuspecting administrator.

This article delves into the ReportServer database, revealing the tables and data that are used to power SSRS. In addition, the ability to alter data in these tables is presented as a way to avoid time-consuming migration or data modification processes.

Do be sure to check out Ed’s warning and then dive into the database.

Comments closed

Creating a Generic SSRS Report

Published 2022-04-04 by Kevin Feasel

Tim Mitchell breaks out the black print on white cardboard:

When a simple presentation of data is needed, SQL Server Reporting Services (SSRS) is a common tool of choice. Creating simple tabular, row-and-column type reports can be done relatively quickly and easily in SSRS. But what happens if you need a lot of these reports? If you need to present dozens or even hundreds of reports to end users, building even a simple report for each one can take time. Unfortunately there’s no Biml-like automation tool for SSRS, so this work must typically be done by hand.

However, it is possible to create a generic report in SSRS that can dynamically present information from tables or views. In this post, I’ll share my implementation of this.

Looking at Tim’s solution, I’d be a bit concerned about longer-term maintenance. In particular, my worry would be that what you gain in ease of development, you give away in making troubleshooting and report maintenance harder.

Comments closed

Page Allocation Reports in SSMS

Published 2021-10-26 by Kevin Feasel

Eitan Blumin has updated an open source project:

Back in April 2020, I created an open-source project called “SQL Server Page Allocation Reports“. It consisted of a set of SQL queries and some Power BI reports that can be used for visualizing the size and locations of your data and transaction log pages.

Well, recently I also added SSMS Custom Reports into the mix. So, it’s time to revisit this project and see what’s new!

Click through to see what’s new.

Comments closed

Paginated Reports in Power BI

Published 2021-08-27 by Kevin Feasel

Elayne Jones dives into paginated reports in Power BI:

Paginated Reports for Power BI offer pixel-perfect control over the format of each element of a report. They allow users to fine-tune each field of the report, such as text size, colors, spacing, and print layout, in a more precise way than using regular visuals in Power BI Desktop. Users can access Paginated Reports directly from workspaces in Power BI Service. Additionally, users can embed Paginated Reports directly onto a Power BI report page with the new visual option. This article will explain how to create a Paginated Report and how to configure the new Paginated Reports visual in Power BI Desktop. Please note that Paginated Reports require a Premium subscription. This tutorial is based on a fictional Sales Report.

If you’re familiar with SQL Server Reporting Services, you’ll find Power BI paginated reports simultaneously comfortable and confining—it’s much the same functionality as SSRS, but doesn’t feel as complete.

Comments closed

Conditional Visibility in Power BI Paginated Reports

Published 2021-07-20 by Kevin Feasel

Sabrina Jordan has a clever solution to a common customer request:

How many times have you struggled to find the happy medium between a report that looks phenomenal when printed, but has everything a user might need in an Excel export? I recently built a beautiful paginated report with groupings separated by white space for easy readability – but the user wanted to export the results to Excel, and the format prevented them from sorting or filtering the report contents. Power BI Report Builder has a couple features that can allow you the best of both worlds, with a few simple tricks. By the end of this tutorial, you will have created two Tablix, set up conditional visibility based on report render format, and set conditional sizing on the Excel Tablix (using hidden charts!) to prevent blank pages.

Click through for the solution.

Comments closed

Naming Worksheets in Power BI Paginated Report Excel Outputs

Published 2021-07-16 by Kevin Feasel

Paul Turley answers a question:

This question comes up every few years in SQL Server Reporting Services. Of course, in Power BI Paginated Reports, we have the same features. A couple of days ago, Karthik posted this question as a comment to my post titled Chapter 7 – Advanced Report Design:

I am working on a SSRS report where the grouping is done to group the records in to multiple tabs/worksheets. When the report is exported to excel, the worksheets has the default name (Sheet1, Sheet2, Sheet3,…). Here I want to override the default worksheet name with (Tab1, Tab2, Tab3, …). The number of tabs/worksheets varies each time depending on the parameter selection by the user. How to address this? any suggestions please.

Click through for the answer.

Comments closed