SSRS

ByHariharan Rajendran

PowerBI Reports in SQL Server Reporting Services

Recently, Microsoft released a Technical Preview of PowerBI in SQL Server Reporting services. It has opened a door to create and deploy PowerBI reports within the Reporting services.

This will be a great feature that who are all searching for great report representations in SQL Server Reporting services itself.

This article illustrates, the new look and feel of SQL Server Reporting Services portal and diverse approaches to publish the PowerBI reports.

As we have PowerBI Desktop tool from PowerBI Team, we need to use this tool to develop the PowerBI Reports. It means, we need to use two different tools for developing and publishing reports, i.e. regular SSRS reports from Visual Studio data tools and PowerBI reports from PowerBI Desktop.

Look and Feel

As you know, the look and feel of SQL Server Reporting Services Portal has changed from SQL Server 2016.

In this technical preview, there is a new section added at the bottom to hold all the PowerBI Reports but still we can manage inside our regular report folders.

PowerBi

 

Approaches to publish PowerBI Reports to the portal,

Approach 1:

Develop reports in PowerBI Desktop and perform “Save As” to directly publish (deploy) to portal.

Bi1

 

BI2

Approach 2:

Save the report in local folder and upload manually to the portal.

Bi3

ByDr. SubraMANI Paramasivam

Linked reports in SQL Server Reporting Services

Linked reports is a great feature and helps in easy maintenance. You can easily avoid any duplicity of the report just for the sake of security. For example if a report has got 5 regions like Europ, NA, Asia, China, SA. You don’t need 5 different versions of report and instead you can have one single report with report parameters pointing to region and easily grant access to relevant AD groups to access their own regional reports. Any single change made in the main RDL file will immediately reflect in other regions as well and no need to make the same change in each regions. Imagine if you have 50 departments and if you are in this situation, linked reports concept a real handy one.

A quick demo is available here.

LinkedReports

ByDr. SubraMANI Paramasivam

SSRS – Report Builder a closer look

Report builder is a browser based report authoring tool to enable any end users to start building / modifying existing reports (if they have enough permissions) without the need of any installation of BIDS or SSDT. This tool is available via local installation of Report manager. This is a light weight tool that will enable you to definitely build a decent report and you can deploy it directly to the report manager. You can start building this report against SQL Server database, SQL Azure, Oracle and others. Below image also explains more on Report builder tool and also try watching a quick video on this here.

 

ReportBuilder

ByDr. SubraMANI Paramasivam

Snapshots in Microsoft Reporting Services

Snapshots is a great feature within Microsoft SQL Server Reporting services, to see any of your reports at that point in time. The same report cosmetics might have changed recently as part of the change request and the data will be changing everyday. How did the report looked in terms of look and feel and the data that was latest same time last year? For this question a simple answer is the snapshots. Another simple example is your photo that was taken today and comparing the same with your own photo that was taken 5 years ago. Now do you see the difference? Yes, this is an amazing option we have within Microsoft SQL Server Reporting services for your reports.

By running these snapshots of your reports, it reduces the load on the databases directly, but any amazing feature may have its own negative side as well. It is always recommended to keep less than 8 to 10 snapshots for a report in the SSRS and download the rest as a PDF and delete the previous snapshots as the data and the layout are storing in the Report Server DB and this will unnecessarily bloat your SSRS system databases. Below is also a screenshot that explains more on Snapshots. You can also view a quick demo on snapshots in this link.

SSRS_Snapshots

 

ByDr. SubraMANI Paramasivam

Report server modes

There are 2 types of report server modes and given in below screen with available options in SQL 2012. The 2 modes are Native & SharePoint. With native mode you get all the features available to operate your reporting services. But with SharePoint mode you get to manage the SSRS environment using your local SharePoint. By operating via sharepoint mode, you can enable all your intranet users to get to access to SSRS environment within the same application but it does have its own downside like it does not support linked reports, my reports, my subscriptions.

ReportServerModes

ByDr. SubraMANI Paramasivam

SSRS Report Subscriptions

Subscription option with SSRS Reporting tool enables a user to schedule a report to deliver to a mail box as an attachment or to a network shared drive on a regular interval like everyday at 9am or every Monday at 10am or every month, every quarter, etc., based on the business requirements. This subscription is a great feature which enables a user to still see the report and data on that specific date and time no matter if the report date falls on a public holiday or a week end. So we don’t need a manual intervention to run the report like a robot, but instead the subscription option deliver the robotic service.

As this is alternative to running reports on demand, this also gives a great option to execute and deliver a report dynamically based on a condition derived with Dynamic Subscription option, like if the data exists or if the user exists. Below picture explains some of the options available in subscription option. A quick demo on subscription and dynamic subscription is available here.

SSRS_Subscriptions

 

ByDr. SubraMANI Paramasivam

Can I override Reportserver DB?

Has anyone of you ever wondered there are additional 2 system databases available once SSRS environment is added? These 2 database names are ReportserverDB & ReportserverTempDB. These databases stored all information related to your users, security, deployed reports, schedules, report history, etc.,

For those of you who are already aware of these databases, has anyone tried changing this? If yes, then you must have known the database a bit better. But have you ever wondered whether it is good to make changes to the system database? The answer is strictly NO, however at very rare situations, for those who are very well aware of what to change and if it is safe to do so have done some changes like me. Here is one of the scenarios that I have worked on, which can give you bit more understanding on this.

I had a consultant working for the company who used to create schedules to deliver a report to secured shared drive. After he left the company that job failed after 3 months. While analysing such issue, identified the company had policy to change the password of old employees for first 3 months and then remove them permanently after that. So after 3 months once the AD account no longer existed, the jobs started failing. During this time, I cannot raise a IT helpdesk to create the user again but raise the helpdesk ticket to have a system AD account for schedules and then went to the ReportServer DB and modified the schedules to totally move the owner name of the schedules to be the new system account. That’s it job done.

Below is also a screenshot for you to understand on Reportserver DB and what it can support.

Override_SSRS_DB

ByDr. SubraMANI Paramasivam

Securing your Report Manager easily with AD Groups

If you have already practised your SSRS environment locally, you might have played with less then 3 users and couple of AD groups, but what about a situation given like below image with more than 300 reports, 100+ users? How it will enable a DBA to maintain the security based on business requirements. You can add all users individually to the SSRS Report manager and grant them access to the relevant folder with all related hierarchy folders which is a big hassle. Here is where, the windows AD groups jumps in with magic wands to enable even non DBA’s like IT support guys to maintain the users list (of course with proper approval process) on who can access what reports across various departments or within the same department. You simply have to do the above exercise once with the AD group and SSRS Report manager automatically picks the relevant AD Group from Windows Active Directory. This intelligent tool will then enable a IT support guy to easily add / remove the users based on the requirements. You can also have a quick demo on security with AD here.

User_AD_Groups_Security