Press "Enter" to skip to content

Category: Reporting Services

Drillthrough from Power BI to SSRS

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

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

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

Generating SSRS Subscription Agent Job Commands

Craig Porteous has a quick script to generate T-SQL commands to start and stop SQL Agent jobs tied to Reporting Services subscriptions:

This is a query I would run when I needed to quickly make bulk changes to Reporting Services subscriptions. It’s part of an “emergency fix” toolkit. 

Maybe a DB has went down and I have to quickly suspend specific subscriptions or locate Agent jobs for subscriptions. This was always a quick starting point.

I could take the generated StartEnable and Disable commands and record these in tickets or email threads to demonstrate actions taken. There are other ways to make bulk changes to SSRS subscriptions involving custom queries but this can be run immediately, I don’t have to tailor a WHERE clause first. I also wrote previously on managing subscription failures.

Click through for the script.

Comments closed

Reporting Services Scale-Out With Docker

Paul Stanton architects out a scenario using Windocks to create cloned Reporting Services containers in order to scale out Reporting Services:

Database cloning is a key aspect of the SSRS scale out architecture, with database clones providing each container a complete set of databases.  Two or more VMs operated behind a load balancer delivers a highly available and scalable reporting service.  This article focuses on Windows SQL Server containers and Windows Virtual Hard Drive (VHD) based cloning, but the same architecture can support SQL Server Linux containers or conventional instances (Windows or Linux).   Redgate SQL Clone, for example would support SQL Server instances.   Other options include the use of storage arrays instead of Windows VHD based clones.   The trade-offs between SQL containers and instances, and between VHDs and storage arrays are covered in separate sections below. 

The combination of SSRS containers with database cloning is appealing for simplicity and operational savings.  SSRS containers are also drawing interest as part of public cloud strategies, as SSRS containers can be integrated with AWS RDS or SQL Azure databases to provide a horizontally scalable reporting solution.

This is a bit more complex than Reporting Services scale-out with Enterprise Edition, but if you’re on Standard Edition and can’t use scale-out, it’s an interesting alternative.

Comments closed

Getting Reporting Services Installation Details With Powershell

Josh Smith wants to find every installation of SQL Server Reporting Services on a machine:

This is one of those posts so I never have to google this again (one hopes). Here is the PS code to pull back a set of details about every SSRS instance installed on a server including the SSRS instance name, & the first URL port it is running on, the service name and the name of the report server database etc.

Click through for the Powershell script.

Comments closed

When Using SSRS Makes Sense

Eugene Meidinger lays out the scenarios in which it makes sense to use SQL Server Reporting Services over Power BI, Excel, or other tooling:

SSRS makes it easy to control who has access to your reports and data. It is possible to specify permissions on the whole server, specific folders of reports or on a single report. Permissions inherit down, like a regular file system, unless you explicitly break inheritance to specify custom permissions.
In addition to permissions, you have a central server to house and control your reports. This is critical when you need an authoritative source of truth for your reporting. Users can trust that they are reading the latest version of any given report.
In addition to the administrative side of things, SSRS provides a powerful development environment with SSDT. SQL Server Data Tools (SSDT) is based on Visual Studio, a very popular Integrated Developer Environment or IDE. SSDT makes it incredibly easy to store your reports in source control since your reporting artefacts are just XML files. Source control makes it possible to collaborate on a team or rollback to earlier versions of a report. This is a capability that is not available with Excel or Power BI reports.  

Read the whole thing.

Comments closed

Python In SQL Server Reporting Services

Tomaz Kastrun shows how we can visualize results from Python models in SQL Server Reporting Services:

As we have created four different models, we would also like to have the accuary of the model visually represented using SSRS.

Showing plots created with Python might not be as straight forward, as with R Language.

Following procedure will extract the data from database and generate plot, that can be used and visualized in SSRS.

Tomaz shows us examples of displaying data as well as visuals generated in Python.

Comments closed

Keeping Headers Visible When Scrolling In SSRS

Ginger Keys shows us how to keep tablix headers visible when going through a SQL Server Reporting Services report:

When scrolling through the pages of a SQL Server Reporting Services (SSRS) report, it is very useful to be able to see the column headers throughout the report.  So let’s say you have successfully created an SSRS report using Visual Studio, and everything looks wonderful…except the headers on your columns disappear when you scroll down the page.  You have even set the properties of your Tablix to “Keep Headers Visible While Scrolling”, but it still doesn’t work!  Trying to keep the column headings visible while you scroll down the page of your SSRS report can be a frustrating endeavor.  The following steps will demonstrate how to make it work.

I always thought “Keep Headers Visible While Scrolling” should have been renamed to “Don’t Do Anything About Headers But Let Me Think You Did Something So I Can Look Like I Don’t Know What I’m Talking About When I Tell Customers That The Report Headers Should Stay Visible While Scrolling” but I guess that might have been too long of a property description.

Comments closed

Power BI Versus Power BI Report Server

Lars Bouwens contrasts the normal Power BI service with Power BI Report Server:

The differences between Power BI Service and Power BI Report Server are well documented in the Planning a Power BI Enterprise Deployment whitepaper and the online documentation. The whitepaper by Chris Webb and Melissa Coates also includes a comparison between Power BI Service and Power BI Report Server (Jun 2017 version). Because this is a lot of information, I’ve summarized the most important topics.

Power BI Report Server – two options
Power BI Report Server is the Power BI on-premises alternative to the cloud-based Power BI Service. There are two ways of acquiring Power BI Report Server: 1) Purchase a Power BI Premium Subscription or 2) by making use of your SQL Server Enterprise Software Assurance license.

I use Power BI Report Server.  It’s not perfect, but it does what I need it to do and it doesn’t cost the company anything extra (due to Enterprise Edition + Software Assurance).

Comments closed