How to use SSRS reports as Data Source in Excel

As we all know SSRS is a server-based reporting platform that allows to create and manage a wide variety of reports, and deliver them in a range of formats, but many of them are still addicted to the excel for reporting as it is more convenient and easy to use.

In this post, let us learn how to use SSRS report as Data Source for Excel Reporting.
First of all, we need to enable Data Analysis in Excel and then continue importing data from SSRS into Excel.

Go to "Data" tab and Click "manage Data Model".

On the pop-up click "Enable" to enable the data analysis add-ins.

In the Power Pivot for excel pop-up, click on "Get External Data" and choose "From Other Sources".

Choose "Report" in the "Connect to a Data Source" pop up.

Next, Choose the Report Path which you want to use as the source.

In the Name box, type in the ReportServer URL and choose "Open".

Then Navigate to the rdl file.

Choose the rdl file and click "Open".

Once the rdl file is chosen, you can preview the report and click next.

Then choose the data table/view under the report and click "Finish".

Click Close on the successful completion of the import.

This is now import the data into the excel for further reporting.

Now go back to the excel and choose "Pivot Table" from the "Insert" tab.

And choose the Report connection that we previously imported.

Then choose where to create the new Pivot table.

Now you can create your own reports out of this data using the excel which is more convenient for you.

Embed SSRS Report into PowerPoint Slides

Presentations are one major activity in each and everyone's career. There are different ways of presenting your information, some does it just by showing the static data and some wants to do it with the Live data. We are the people who are of type 2, presenting the live data. But when it comes to presenting live data, it is through reports.
How beautiful it will be if we have the option to interact with the Live data from a PowerPoint Presentation?
In this post, we shall learn how to embed SSRS report into PowerPoint Slides.

Before we begin, we need

1. A Web Viewer add-in for PowerPoint
2. Report URL to embed in the PowerPoint

Open a PowerPoint Presentation, go to "Insert" Tab and Click on "Store"

Now search for "web viewer" add-in and Click "Add". This is an add-in from Microsoft.

Once you click "Add", this is add the add-in into your slide and it looks like this

Now Insert your SSRS Report Server URL into the URL box and hit "Preview". 

This was my URL https://sql01.sanssql.com/ReportServer/Pages/ReportViewer.aspx?%2fAdventureWorks+Sample+Reports%2fCompany+Sales&rs:Command=Render&rc:Toolbar=False 

And that's it, Now your Slide will Load the data from SSRS Report.

Now you can interact (Drill Down or Drill Through, etc...) with the data depending on the report type. 

If you want to show a static image, then click on the small left arrow on the top right of the add-in and choose "Show as Saved Image" and this is display the data in the image format.

Now your presentations will be more fun with the Live Data.

How to Enable Remote Errors in SSRS

Enabling Remote Errors includes external error information (for example, error information about report data sources) with the error messages that are returned for users who request reports from remote computers. By default the remote errors in SSRS is set to false, which means error messages in a multi-server environment are not detailed.

Enable Remote Errors by using the Report Server Properties

1. Open SQL Server Management Studio (SSMS) and connect to the Report Server
2. Right-Click on Report Server and Choose "Properties"
   
3. Navigate to the "Advanced" Page and set the "EnableRemoteErrors" to "True" and Click "OK"
   

Enable Remote Errors by Modifying the ConfigurationInfo table

USE ReportServer
GO
UPDATE ConfigurationInfo
SET [Value] = 'True'
WHERE Name = 'EnableRemoteErrors'

Note: If you modify the setting in the database, you need to restart the Reporting Services service before the changes take effect.

List all Shared Data Sources

Here is a handy script to list all the Shared Data Sources from the ReportServer Database.

WITH BasicData AS 
(
SELECT CONVERT(VARBINARY(MAX),Content) AS Content,
[Catalog].Name,[Path],ConnectionString FROM [Catalog] INNER JOIN DataSource
ON [Catalog].ItemID=DataSource.ItemID
WHERE [Type]=5 -- Type=5, Filters Shared Data Sources
)
,DataWithXML AS
(
SELECT 
Name, [Path], CONVERT(XML,Content) AS ContentXML 
FROM BasicData
)
SELECT Name, [Path], ConnectionString.value('(text())[1]','nvarchar(max)') AS ConnectionString
FROM DataWithXML 
CROSS APPLY DataWithXML.ContentXML.nodes('//*:ConnectString') AS ConnectionStrings(ConnectionString)

SSRS reports not visible on Google Chrome or Safari

Have you developed a new report in SSRS 2012?
Yes,
Is it working on IE?
Yes.
Okay, is it working on Chrome?
No.
Okay, is it working on Safari?
No.
What's the issue?
Is the new report developed with proper logic?
Yes, No, Maybe?

Don't worry, if the report is working on IE then it should show up on other browsers.
There is an issue with the SSRS 2012. Chrome and Safari render "overflow: auto" option in different way than Internet Explorer.

To Fix this issue,

1. Open the server where SSRS is installed
2. Navigate to the path <SSRS Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\
3. Backup the file ReportingServices.js
4. Edit the file ReportingServices.js and append the below code into it
   

   function pageLoad() {   var element = document.getElementById("ctl31_ctl09");   if (element) {     element.style.overflow = "visible";   } }

5. Save the file

Now you should be able to view the reports on Chrome and Safari.

The div name is not always ctl31_ctl09. So, if you are still unable to view the reports on chrome and safari after the change, make sure to replace the highlighted div name in the code to the right div name of yours. This could be found by looking at the HTML source of report URL from your browser.

Update:
Another workaround for this problem is through the CSS.

1. Open the server where SSRS is installed
2. Navigate to the path <SSRS Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Styles\
3. Backup the file ReportingServices.css
4. Edit the file ReportingServices.css and append the below code into it
   

   div { overflow: visible !important; }

5. Save the file

Reporting Services Error - The report server installation is not initialized. (rsReportServerNotActivated)

Recently, I had a task for restoring the Reporting Services database from a live environment to a test environment.
Right after the successful restore, I tried opening the Report Manager website and I was presented with the error "The report server installation is not initialized. (rsReportServerNotActivated)"

This is because of the mismatch in the encryption key.
To fix this, either restore the encryption key from the live environment or delete the encryption key from the restored reporting services database.
The restoration or deletion of encryption key can be done by using the Reporting Services Configuration Manager.

T-SQL query to find if a Report Server Database is configured in Native or Sharepoint Integration mode

Here is a T-SQL Query to find if a Report Server Database is configured in Native or Sharepoint Integration mode.

USE ReportServer$SQL2008R2 --Change the Database Name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'ConfigurationInfo')
BEGIN

 DECLARE @Result nvarchar(max)
 SELECT @Result = Value FROM ConfigurationInfo
 WHERE NAME = 'SharePointIntegrated'
 PRINT CASE WHEN @Result= 'False' THEN 'The specified Report Server Database "'+ DB_NAME() + '" is configured in "NATIVE" mode'
      WHEN @Result = 'True' THEN 'The specified Report Server Database "'+ DB_NAME() + '" is configured in "SHAREPOINT INTEGRATION" mode'
    END
END
ELSE
BEGIN
DECLARE @DBName nvarchar(100)
SELECT @DBName = DB_NAME()
RAISERROR('The database %s is not a report server database',16,1,@DBName)
END

Location of SSRS config files - Sharepoint integration mode

When SSRS is configured in Sharepoint Integration Mode then the config files reside in a different location when compared to the normal installation.

The location where these files can be found are
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

The below link provides a description of configuration files used for a SharePoint mode report server.
http://technet.microsoft.com/en-us/library/ms155866.aspx

Error while Provisioning Reporting Services Subscriptions in sharepoint

When trying to create subscriptions for SQL Server Reporting Services when hosted in SharePoint Integration mode , you might get an error which says

“The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.”

To Fix this issue,

1. Login to Sharepoint central administration and navigate to the “Manage Reporting Services Application” page
   
   
2. Click “Provision Subscriptions and Alerts” 
3. Click “Download Script” and this will download a file named “SSRS-GrantRights.sql”
4. Execute the script on your SharePoint database server

Configure e-Mail for a Reporting Services Service Application – Sharepoint Integration mode

Here are the steps to configure STMP/e-mail for SSRS Service application in SharePoint integration mode.

1. In SharePoint Central Administration, click the Application Management
   
2. In the Application Management page, under Service Applications group, click Manage service applications.
   
3. In the list of configured applications, click on the name of your Reporting Services service application (SSRS).
   
4. Click E-mail Settings on the Manage Reporting Services Application page.
   
5. Select Use SMTP server In the Outbound SMTP server box, type the name of an SMTP server.In the From address box, type an e-mail address. This e-Mail address will be used as the sender of the e-mail alert.
   
6. Click OK

Report Builder 3.0 Error - Unable to connect to the server that is specified in the URL

Consider you built a report using Report Builder 3.0 and now you are deploying the same to your report server. Suddenly the Report Builder presents you with a error which says the below

Connection failed. Unable to connect to the server that is specified in the URL,'http://<servername>/<ReportServer>'. Make sure the server is running, the URL is correct, and you have permission to use the server.

Solution:
Make sure that the SQL Server Version is SQL Server 2008 R2 as Report Builder 3.0 does not support SQL Server 2008 or below.

Performance Dashboard Reports - Microsoft SQL Server 2012

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:

• CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer (missing indexes)
• Blocking
• Latch contention

This is a downloadable available from Microsoft and can be downloaded from the link here.

This also works for SQL Server 2008 R2 and SQL Server 2008 as well

Reference: http://www.microsoft.com/download/en/details.aspx?id=29063

SSRS Error - An Unexpected Error occurred in Report Processing (rsInternalError)

I was working on a SQL Server Reporting Services (SSRS) Report just a few minutes back and i thought my deployment of the report went fine and i can now view my deployed reports on the Report Manager site.
Assuming that everything went fine, I opened the Report Manager and there was a surprise awaiting for me.
And the surprise was, the Report Manager which was working fine till yesterday is now giving an error.
The error is:
An Unexpected Error occurred in Report Processing (rsInternalError)
Could not find a part of the path 'C:\Documents and Settings\SANSLAPTOP\ASPNET.SANSLAPTOP.000\Local Settings\Temp\brhx7gi1.tmp

Fix that worked for me:
I manually went into each folder structure that is displayed as part of the error message and found that in my case, the folder "Temp"  was missing in the file system.
I created this folder and went back to my report manager and refreshed the page and the report manager started to work as before.