Power BI – Accessing SSRS Datasets using a Web Connector

November 10, 2019

Let’s say you have spend a lot of time to build SSRS reports, and you want to re-use these data sets in Power BI.

Well this is possible even if you are running the older version of SSRS.

Even if this report is running in Native or Integrated SharePoint Mode, or is a Parametrized report

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.

SOLUTION :

Open Power BI and GET DATA  from the Web Connector

image

Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report

image

image

If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile

image

Enjoy !!


SSRS – Multi Select Drop down based on a Query Data Source

October 31, 2019

If you need a Multi Select Drop down box that fills up dynamically…

This is the way to do it.

1. Create a new data set

That holds the query to populate the drop down box.

image

Make sure you set the WHERE clause to point to new Multi Select Parameter

2. Create a new Parameter

Set the parameter settings to ALLOW MULTIPLE VALUES 

image

And next set the available values to retrieve the data from the above Query

image

Next in Advance use these settings

image

4. Main ‘DataSet1’ Query selection settings

In the main dataset make sure that you set the WHERE clause :

To point to the parameter using this format IN (@yourparameter)

image

IMPORTANT : Notice the brackets around the Parameter name !

If you forget them you will get errors !

5. Result

The result is that your drop down box will populate dynamically,

based on the previous parameter values

image

Really cool  Smile

Enjoy !


PowerShell – Retrieve a SSRS Report in a .NET GUI

May 5, 2019

Ever wanted to create you Offline Reporting Engine based on SSRS using PowerShell !

The objective is to run the RDL reports server side and display in .NET GUI client side.

PREREQUISITES :

You first need to download the .NET Assemblies using the SSRS redistributables.

Download here :

2008 / 2010 / 2012 or 2015 for a newer version see here

SOLUTION :

The solution is based on .Net Scripting in PowerShell using a GUI.

REMOTE processing mode :

CLS
# [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.ReportViewer.WinForms”)
Add-Type -Path "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\12.0.0.0__89845dcd8080cc91\Microsoft.ReportViewer.WinForms.dll"

#Windows.Forms for viewing dialog box
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
Add-Type -AssemblyName "System.Windows.Forms"

# for credentials, if needed
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
Add-Type -AssemblyName System.Net

$credential = Get-Credential

$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ServerReport.ReportServerCredentials.NetworkCredentials = $credential

$rv.ProcessingMode = "Remote"

$rv.ServerReport.ReportServerUrl = "http://YourServer/Reportserver"
$rv.ServerReport.ReportPath = "/YourReportName”

$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();

#---------------------
# Show as Dialog Using Windows Form
#---------------------
# Create a new form
$form = New-Object Windows.Forms.Form;

# Form Properties
$form.Height = 610;
$form.Width= 810;
$form.Controls.Add($rv);
$rv.Show();
$form.ShowDialog();

#---------------------
# Export to Excel
#---------------------
#now let’s try exporting to Excel
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;

$bytes = $rv.ServerReport.Render("Excel”, $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file = "C:\Temp\SampleReport.xls”;
$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();

#l Open up our excel application
$excel = New-Object -comObject Excel.Application
$excel.visible = $true;
$excel.Workbooks.Open($file) | Out-Null;

#---------------------
# Export to PDF
#---------------------
$bytes = $rv.ServerReport.Render("PDF”, $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file2 = "C:\Temp\SampleReport.pdf”;
$fileStream = New-Object System.IO.FileStream($file2,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
[System.Diagnostics.Process]::Start($file2)
image

The result is the report in your own GUI that runs locally and is processed centrally, when you use REMOTE

If the report has Parameters it will prompt you to enter them just like in the Web Form.

image

After the GUI is close the script will be EXPORT the content to EXCEL and PDF too.

You will have the options to EXPORT to all supported formats, or add your own extension if you are working server side.

image

LOCAL processing mode :

Most interesting is that you can run the Report Viewer Control in LOCAL processing mode !

Which means that you can create a Local Reporting Engine that runs the RDL reports from you client in a PowerShell GUI

image

And additionally you can add any kind of .Net support Data Source, like in this case an SQLite database !

Which is not supported by server side SSRS…

How cool is that ! Surprised smile

For more info see here

Happy Scripting Smile

Enjoy!


SSRS – How to export a csv without header and more…

April 24, 2019

1. Create an New EXPORT Device using CSV without HEADERS

By default it exports the HEADER info as well …

image

 

Solution :

Go to the Reporting server config file.

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

Make a copy for backup reason and add this to the RENDER section

image

 

<Extension Name="CSV (No Header)" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<OverrideNames>
   <Name Language="en-us">CSV No Header</Name>
</OverrideNames>
<Configuration>
   <DeviceInfo>
      <NoHeader>true</NoHeader>
      <FieldDelimiter>|</FieldDelimiter>
      <ExcelMode>False</ExcelMode>
   </DeviceInfo>
</Configuration>
</Extension>

 

Next restart the Reporting Service again and test it …

Connect to the Web Server to see the result.

image

 

2. Ever wanted to create a report where the EXPORT FIELDS would be different from the Report VIEW.

 

Solution :

Go to the field and set the Property DataElementOutput to NoOutput

 

image

This makes that these fields will NOT be EXPORTED  Smile

 

3. Print a label on the FOOTER page

 

Solution :

Select a label field you want to have printed on the last page only.

image

Enjoy !!


SSRS – SharePoint Lists Default VIEW

March 22, 2018

Once you connect to a SharePoint List you will always get connected to the list using the DEFAULT VIEW.

Which might not be what you want because it can be a FILTERED View. And therefore not showing you all the records you want.

SOLUTION :

1. Go to DataSet –> Query

image

2. Apply a Filter

image

Strangely enough if you DO NOT Apply a filter in the Query Designer. It will fetch the data using the DEFAULT VIEW set in SharePoint ???

So basically in order to get ALL the DATA, you need to SET a FILTER that covers the whole range in your data set.

Very contradictory approach Confused smile

Enjoy !


SSRS – SharePoint Lists HTML Tags

March 22, 2018

If you are working a lot using SSRS against SharePoint Lists.

You will see that some lists that contain Text are not displayed properly… Sad smile

See Example :

image

The first column shows the raw output with all the HTML Tags.

While the second column shows you the proper output you need for the report.

SOLUTION :

1. You first need to add a PLACEHOLDER in the column

image

Two times a LEFT click in the field and next a RIGHT click.

2. Secondly put in the field that has the HTML text, and choose HTML as MARKUP TYPE

image

 

Enjoy !


QlikView – Access Data from SSRS

March 3, 2015

Since QlikView can’t access certain data sources like MS Analysis Services (SSAS) or other exotic data sources (SAP NetWeaver BI, Hyperion, TERADATA) natively.

We can fall back on the perfect middleware for this being MS SQL Reporting services

image

The approach is a simple as can be. Setup an SSRS server (can even be the MS SQL Express (Free) Edition & SSRS add-on)

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.

Next develop your SSRS reports (which can handle multi data sources in 1 report Smile)

image

Like for example a SharePoint List combined with an Oracle database, or anything else.

Simular to PowerPivot that can access an SSRS Data Source. We can do the same with QlikView.

Use a Web File connection as Data Source

image

Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report

image

If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile

image

That’s it, now you are ready to build your QlikView GUI

Once you know this technique you can as well use this to access an SSRS in the MS Azure cloud.Winking smile

Enjoy!