SansSQL

Tuesday, August 30, 2016

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.
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Friday, July 8, 2016

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.

Wednesday, July 6, 2016

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'
NoteIf you modify the setting in the database, you need to restart the Reporting Services service before the changes take effect.

Monday, June 20, 2016

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)

Monday, March 21, 2016

An Introduction to Microsoft Power BI

Microsoft Power BI transforms your company's data into rich visuals for you to collect and organize so you can focus on what matters to you. Stay in the know, spot trends as they happen, and push your business further.

What is Power BI

Power BI is a suite of business analytics tools to analyse data and share insights. Monitor your business and get answers quickly with rich dashboards available on every device.
Power BI dashboards provide a 360-degree view for business users with their most important metrics in one place, updated in real time, and available on all of their devices. With one click, users can explore the data behind their dashboard using intuitive tools that make finding answers easy.
Dashboards can be created from over 50 plus types of sources like Microsoft Excel, SSAS, Access, Azure, Facebook, etc… and can access the data and reports from anywhere with the Power BI Mobile apps.
Using the Power BI gateways, you can connect to your organisations on premise SQL Server databases, Analysis Services models, and many other data sources to your same dashboards in Power BI.

Architecture of Power BI

The high level Power BI architecture look like below with the different components connecting with each other to give you the power of data visualisation.