Press "Enter" to skip to content

Category: Reporting Services

Python In SQL Server Reporting Services

Published 2018-11-19 by Kevin Feasel

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

Published 2018-09-24 by Kevin Feasel

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

Published 2018-08-29 by Kevin Feasel

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

Using DAX With Reporting Services

Published 2018-07-03 by Kevin Feasel

David Stelfox gives us an example of using DAX when connecting SQL Server Reporting Services to a SQL Server Analysis Services Tabular model:

Tools like Power BI have changed reporting allowing power users to leverage tabular cubes to present information quicker and without the (perceived) need for developers. However, experience tells us many users still want data in tables with a myriad of formatting and display rules. Power BI is not quite there yet in terms of providing all this functionality in the same way that SSRS is. For me, SSRS’s great value and, at the same time its curse, is the sheer amount of customisation a developer can do. I have found that almost anything a business user demands in terms of formatting and display is possible.

But you have invested your time and money in a tabular SSAS model which plays nicely with Power BI but your users want SSRS reports so how to get to your data – using DAX, of course. Using EVALUATE, SUMMARIZECOLUMNS and SELECTCOLUMNS you can return data from a tabular model in a tabular format ready to be read as a dataset in SSRS.

It’s a good post and a good example.  The only quibble I have is in the motivating paragraph; Power BI and SQL Server Reporting Services have different end goals—Power BI isn’t (and I think never will be) a pixel-perfect report building product; it’s meant to be a dashboarding technology.  That quibble aside, the example is well worth checking out.

Comments closed

Reporting Services And Availability Groups

Published 2018-07-03 by Kevin Feasel

Thomas Rushton has a good rant worked up:

Yes, you may have an availability group – well done – and you may have installed SSRS on both servers. But you’ve only set up the reporting application to point to one of those? And you’ve given the link https://<<Listener_Name>>/reports out to the users? Head/desk. I told you at the time that SSRS doesn’t play nicely with AGs. [Nearly misposted as SSRS doesn’t play nicely with SSRS, which, while valid, isn’t the point here…]

Here’s what you need to do to fix this / make sure it doesn’t happen:

Click through to learn what you need to do to make sure there are no problems.

Comments closed

Reporting Services Templates In Visual Studio 2017

Published 2018-06-12 by Kevin Feasel

Greg Low points out an oddity in where Reporting Services templates are located in SQL Server 2017:

Templates are just report files (ie: RDL files) but placed in a special folder on your system.

If you’re starting with a blank page to create a report, you’re doing it wrong.

But where do these templates come from? In the Visual Studio 2015 version of the tools, they were here:

C:\Program Files (x86)\Microsoft Visual Studio 14.0\
Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

I found after installing the VS2017 version of the tools though, that they weren’t there any more.

Greg has found them in two separate locations, so read on to learn more.

Comments closed

Securing Power BI Report Server

Published 2018-06-01 by Kevin Feasel

Steve Hughes gives some advice for securing a Power BI Report Server installation:

You have essentially three layers of access to the report file security in Power BI Report Server.

  1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
  2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
  3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server is a different animal from standard Power BI, so securing it will be a bit different as well.

Comments closed

Connecting To Power BI Report Server Using SSMS

Published 2018-05-08 by Kevin Feasel

Koen Verbeeck shows how to connect to a Power BI Report Server instance using SQL Server Management Studio:

Sometimes you want to connect to a report server instance using Management Studio, for example to create a new security role or modify an existing one. Recently I tried to log into our newly installed Power BI Report Server (March 2018 edition). I was greeted with the following error:

The Reporting Services instance could not be found.

Read on to see how to solve this problem.

Comments closed

Dynamically Showing Or Hiding Columns In SSRS With Parameters

Published 2018-04-10 by Kevin Feasel

Sander Stad shows how to show or hide columns at runtime in SQL Server Reporting Services reports using parameters:

Regularly I have reports that have an extensive amount of columns.
Because the amount of columns, reports tend to become inefficient and have too much information we don’t always need. The users may want to select certain columns and to make the report easier to read.

Hiding and showing columns in SSRS reports using parameters is a solution to make reports more dynamic and easier to use.

At the time of writing of this article, SQL Server Reporting Services did not yet have the possibility to use checkbox parameters. Instead we will be using a multi-value text parameter to show or hide our columns.

Click through to see how to do this.

Comments closed

Executing R Scripts In SSRS

Published 2018-03-06 by Kevin Feasel

Tomaz Kastrun shows how to include R scripts (and visuals) in SQL Server Reporting Services:

Using the privileges of R language to enrich your data, your statistical analysis or visualization is a simple task to get more out of your reports.

The best practice to embed R code into SSRS report is to create stored procedure and output the results to report. To demonstrate this, we will create two reports; one that will take two input parameters and the second one to demonstrate the usage of R visualization.

It’s nice to be able to use R to create nice visuals and then import them in your SSRS report, and Tomaz shows how.

Comments closed