A comparison of SQL Server Reporting Services and Power BI
Part two of a seven-part series of blogs

Power BI improves with each monthly update. Will it reach a point where SSRS (SQL Server Reporting Services) becomes redundant? This detailed blog compares the two products, and considers the future of SSRS.

  1. Reporting Services (SSRS) versus Power BI
  2. An overview of Power BI and SSRS (this blog)
  3. Loading data (SSRS and Power BI compared)
  4. Creating reports for viewing on-screen
  5. Exporting and printing reports
  6. Total Cost of Ownership (TCO)
  7. Conclusions

Posted by Andy Brown on 18 October 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

An overview of Power BI and SSRS

This page shows what a typical report might look like in Power BI and in SSRS.  I've chosen as an example a report which shows a list of films grouped by genre for a given choice of film certificate.

Power BI

Power BI Desktop is a standalone application which is free to download (although not always free to use - see part 6 of this blog for more on this).  It allows you to load tables from different data sources, and link them together to form a data model:

Data model

The data model for this example consists of these 3 tables.

You can use this as the basis for a report:

Report in Power BI

This is showing the number of films by genre for the 18 certificate.

Power BI has a fair number of built in visuals:

Visuals toolbar

In addition to these built-in visuals, you can also download a huge number of custom visuals created by third parties.

 

When you've finished creating your report, you can publish it to Power BI Service (or to your internal report server), or even view it on the Power BI mobile phone app:

Power BI Service

Viewing the same report online after publication.

Power BI's strengths include the ability to accept and massage data from numerous different types of source, and the support for drill-down and getting visuals to interact with each other.  Power BI is less good if you want to print reports, or export them to Excel.

Reporting Services (SSRS) 

Reporting Services is a free add-on to SQL Server (free, at least, if you use Visual Studio Community Edition as your editing tool).  For any report, you choose which tables and columns you want to include:

Choosing tables and columns

SSRS is far less flexible about its data sources: all of the tables must be in the same SQL Server, Oracle or other database.

You then design either a table, matrix or chart (I'm simplifying quite a bit):

A table of films

This table will list out each film's genre, title and run time in minutes.

The set of tools that you can use is more limited than in Power BI:

SSRS toolbox

This doesn't look quite as exciting as the visuals toolbar in Power BI!

 

You can now preview your report, perhaps adding a parameter as below: 

Report with dropdown parameter

This list of certificates is called a "dropdown parameter".

When you've created a report, you can deploy it to your report server for viewing (although it should look exactly the same as the one shown above).

SSRS is great if you want to print reports out based on data in a SQL Server database, export the reports to Excel or other formats or email them out automatically to subscribers.  However, SSRS can look its age (I first started using SSRS 2005), particularly if you want to create dynamic reports which keep changing depending on what you click on.

This blog has 0 threads Add post