Category Archives: SQL Server Reporting Services – SSRS

SSRS 2016 and Power BI

SSRS 2016 and Power BI

Without a doubt SQL Server Reporting Services (SSRS) is one of the most powerful reporting tools for several years. There are tons of features that you can use to make a report that suits your customers’ needs. Despite programmability and extensibility are key strengths of Reporting Services platform when it comes to creating dashboards, SSRS has absolutely nothing to offer as SSRS is a report authoring tool. So it never supposed to offer dashboards. In old days we could create web parts in SharePoint or we could install Performance Point and include SSRS reports in Performance Point dashboards. But, setting up and implementing dashboards in SharePoint/Performance Point was always a painful job. Happily with the new version of SQL Server 2016 we are able to pin visuals from existing on-prem SSRS reports to a Power BI dashboard. In this article I explain how SSRS 2016 and Power BI integration works.

Requirements

When you meet the above requirements you can pin visuals from existing SSRS reports to Power BI or you can create brand new reports and pin the visuals to Power BI.

Note: You can only pin report visuals to Power BI that means you won’t be able to pin tables and matrix to Power BI.

Note: If you don’t want to install the developer edition of SQL Server 2016 OR for any reason you cannot use the developer edition, don’t worry, the functionality I’m going to explain is available in other editions of SQL Server 2016. Indeed, the only editions that doesn’t support SSRS integration with Power BI are “Express Edition” and ” Express with Tools” editions. Check this out for more information.

Register SSRS with Power BI

After installing SQL Server you need to configure Reporting Services. As configuring Reporting Services is out of scope I leave it to you.

Note: At the time of writing this article I was using SQL Server 2016 CTP3. The same principles apply to SQL Server 2016.

  • Open “Reporting Services Configuration Manager”
  • Enter the “Server Name” and “Reporting Services Instance” then click “Connect”

SSRS 2016 Continue reading SSRS 2016 and Power BI

Querying SSRS Report Definition Using T-SQL

Do you want to have all reports that used a table in their report definition?

Are you looking for a report that has a desired parameter name?

Have you written a new version of a SQL view or stored procedure and you need to modify all the reports working on top of the version of the object, but, you don’t know what those reports are?

Have you modified an SSAS object and you need to know which reports might be affected?

If you have any of the above questions or in general you need to retrieve all SSRS reports which have a specific string in their report definition, just connect to the SQL Server instance which holds your   REPORTSERVER database through SSMS and simply execute the SQL scripts below:

SELECT C.NAME

       , CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) AS REPORTXML

FROM  REPORTSERVER.DBO.CATALOG C

WHERE  C.CONTENT IS NOT NULL

            AND  C.TYPE = 2

          –AND  C.NAME LIKE ‘%REPORT_NAME%’

     AND CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) LIKE ‘%DESIRED_STRING%’

Enjoy!

Installing SQL Server Reporting Services (SSRS) 2012 integrated mode with SharePoint 2013

Reporting services 2012 integrated with SharePoint 2013

 

In this post I’m going to describe installing SSRS 2012 integrated mode with SharePoint 2013. As some guys are pretty new to SSRS 2012 or SharePoint or even SSRS integrated with SharePoint, I am explaining the situation from scratch.


Requirements:

Note: Reporting services will not install on standard or express editions

  • Windows Server 2008 or later

Note: You can download Windows Server 2008 R2 180 days trial from here: http://www.microsoft.com/en-us/download/details.aspx?id=11093 and Windows Server 2012 from here: http://www.microsoft.com/en-us/server-cloud/windows-server/trial.aspx

Note: SharePoint 2013 is available only in 64-bit version, so you need a 64-bit operating system.

Continue reading Installing SQL Server Reporting Services (SSRS) 2012 integrated mode with SharePoint 2013