Integrate Tableau and SSRS – A complete tutorial

One of the most difficult part of Business Intelligence is finding the right tool for the right situation. Too often folks will discover Tableau and use it for every single bit of reporting they need, including data that used to be in Excel. The problem is, they will utilize Tableau as if it was a glorified Excel replacement and try to mimic Excels features. Unfortunately if there is one thing Tableau does horrible, it is display large amounts of raw data. Tableau is great at visualizations, which is the wheelhouse it should stay in.

However – there is often a legitimate need to not only get at the raw data, but display it in a “prettier” format. Viewing data within Tableau isn’t a great experience. Often it’s littered with calculations you don’t want to see, and users want you to organize the raw data (maybe add some nice headers). None of this is really possible or practical within Tableau. But – did you know that large amounts of raw data are perfect for SQL Server Reporting Services (SSRS) – AND – these two tools can interact with each other.

There are upsides and downsides – but it is in fact possible to create a visualization within Tableau and offer the user a link to SSRS to download the raw data – OR – auto download the raw from Tableau to make it *look* like you never even left. I discovered how to do this through trial and error, so I thought I would post it our for others in case it’s ever needed.

The first thing you’ll need to understand is that this integration works via URL Action Filters in Tableau. SSRS just so happens to accept values for its parameters via URL, meaning that Tableau can build these URL’s dynamically and pass the values along. SSRS can then pickup these values and run the report. In the most simple examples, SSRS can run the report as-is without any extra coding. However – often a Stored Procedure is required behind the scenes to receive the URL Parameters and make them usable for the report. So when I say integration between the two tools works, it’s really just a happy coincidence and not intentional.

A quirk with SSRS that held this process up for me for a few days is the fact that SSRS may not accept URL parameters through it’s normal path to the report. Generally this is in the default format of:

<server name>:<port>/Reports

Here you can try a simple example to see the result (utilizing my PC). I have a quite boring report utilizing AdventureWorks2017 that shows a listing of persons in a company. Notice I have a parameter with a drop down for Last Name.

This particular report lives at:

localhost:8080/Reports/report/Test%Report

URL Parameters for SSRS work by appending the following to the URL:

?<parameter name>=<value>

Here this created the final link:

localhost:8080/Reports/report/Test%Report?LastName=Duffy

Utilizing SSRS version 2019, this works perfectly:

However – this actually came as a surprise to me. I had been using versions 2012-2016 and URL parameters did NOT work as expected. For reasons I am not fully sure of – my experience taught me that you need to send the links through the report server URL instead. This link defaults to:

<server name>:<port>/ReportServer/Reports

Here you will see a listing of all reports, and you can simply take the link of yours, which in my case becomes:

localhost:8080/ReportServer/Reports?%2fTest+Report&rs:Command=Render

Now – adding my URL parameters between right before the “&rs:Command=Render” becomes:

localhost:8080/ReportServer/Reports?%2fTest+Report&LastName=Duffy&rs:Command=Render

Notice that the question mark has become an ampersand when utilizing the Report Server link. Either way – this results in the report rendering for the last name of Duffy only. I’m not sure if the reasoning for this may be my organizations setup of SSRS – but just in case you also hit this problem you’ll know the solution and save yourself a giant headache. There IS a situation in which utilizing the Report Server link IS necessary, and that’s when including optional arguments to export directly to Excel. I’ll discuss this at the end of this write-up.

Linking this within Tableau is incredibly simple. Here we have a Tableau dashboard touching that same database and showing total Sick Hours (I’m really bad at coming up with example reporting and visualizations apparently). Notice I have Last Name on Detail – the dimension your passing (or measure) has to be included in the visualization.

Now – create a URL Action from your dashboard and add the complete URL discussed earlier, minus the value. You’ll see on the right hand side of the entry box that you can add the placeholder value from Tableau to pass along. This appears as <Last Name> when added in place of Duffy. This means that whatever you click on within Tableau gets transferred to that placeholder.

For ease of use I set this to “Menu” so you can see it appear in the tooltip. Go ahead and select a value and try it out.

This is probably the most simplistic example. Here’s the problem… Now you’re going to want to either enable users to select multiple values – or – have them never select multiple values. You’ll notice that selecting multiple bars will allow you to still pass them – if you enable it in the Action Filter.

However – it won’t work.

This is because Tableau will generate (depending on the delimiter you set):

?LastName=Vande%20Velde&Ralls&Patten

And SSRS expects the format:

?LastName=Vande%20Velde&LastName=Ralls&LastName=Patten

Which will work if you manually type it in the URL bar. Unfortunately this means that you either disable the ability to pass multiple – or – you do some fancy bit of magic to enable it… but I warn you it’s a bit of work.

One problem with this method is the fact that all dimensions needing to be used have to be in the visualization, at least in the details. Often this can cause a problem and break the viz. One way around this is create a calc for each dimension needed, and forcing an all or nothing value:

Bringing this into detail will produce the Last Name – OR – the word “All”. You will then need to utilize a stored procedure for your SSRS report instead of SQL in the report, and deal with the “All” value being passed. You can see similar work being done in the next section regarding passing multiple values. That work can be easily adapted for the “All” scenario. When I hit “All” I will transform part of the where clause into “1=1”. This is a big downside of the integration between the two products, it’s very easy to pass one thing that is in the visualization but complexity scales up as you add more filters.

Passing Multiple Values

First you need to create a stored procedure that your SSRS report can source from, rather than using SQL directly (if you were). SSRS is going to receive the “incorrectly formatted” list of multiple values in the URL from Tableau as shown earlier and transform that into a useful IN statement to put inside of Dynamic SQL. The stored procedure expects one of three different formatted values:

1. exec usp_MultipleValuesSSRS ‘All’
2. exec usp_MultipleValuesSSRS ‘LastName’
3. exec usp_MultipleValuesSSRS ‘LastName1, LastName2,…LastNameN’

The reason we receive ‘All’ is to avoid needing to pass every single value for a complete list.

USE [AdventureWorks2017]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].usp_MultipleValuesSSRS
    @LastName varchar(8000)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @LastNameFinal varchar(8000) = ''
	DECLARE @LastNameFinalIN varchar(8000) = ''
	DECLARE @numOfCommas INT
	DECLARE @counter INT
	DECLARE @pos INT
	DECLARE @len INT
	DECLARE @value varchar(8000)
	DECLARE @dsql varchar(8000)

	set @numOfCommas = len(@LastName) - len(replace(@LastName, ',', ''))
	set @pos = 0
	set @len = 0
	set @counter = 0

	--Loop CSV and format into IN statement
	WHILE @counter <= @numOfCommas and @LastName <> 'All'
	BEGIN
			set @counter = @counter + 1
			set @len = CHARINDEX(',', @LastName, @pos+1) - @pos

			--No Commas Left
			if @len < 0 set @len = 2000 
				set @value = SUBSTRING(@LastName, @pos, @len) --Pull single value

			--No Commas to begin with (single value)
			if @numOfCommas = 0 set @value = @LastName 

			--Put into formatted string
			if @counter = @numOfCommas + 1
				set @LastNameFinalIN += '''' + @value + '''' 
			else
				set @LastNameFinalIN += '''' + @value + ''','

			--Find position of comma
			set @pos = CHARINDEX(',', @LastName, @pos+@len) +1

	END

	/* Format IN Statement, include this variable in the where clause of your dynamic SQL */
	if @LastName = 'All'
		set @LastNameFinal = '1=1'
	else
		set @LastNameFinal = 'LastName in (' + @LastNameFinalIN + ')'

	--Buld and execute final SQL statement
	set @dsql = 'SELECT * from Person.person where ' + @LastNameFinal
	print @dsql
	exec(@dsql)
END;
GO

Utilizing this code we can now change our SSRS report to source from the stored procedure.

And now go to Parameters within your dataset and change your Parameter to NOT accept multiple values, and remove any available/default values so it becomes free-text. Unfortunately I have yet to be able to mix the ability to use drop-downs WITH Tableau integration. We essentially need to turn this into a report that only accepts values from Tableau, and is only used with Tableau.

Next – you will need to prep Tableau to pass multiple values. We’ve already seen that we must enable Multiple values within the Action Filter (be sure to set the delimiter to a comma).

Now – when we select multiple bar values and click the link – it will pass these values as a comma delimited list, which is then parsed by the stored procedure above.

A warning with this method, a URL has a limit of approximately 2000 characters. If I wanted to pass multiple large strings, this can add up quickly. In those circumstances I will simply pass keys instead of the actual text. Hopefully most of your tables will have an ID to work with. It’s still possible to hit the limit even if you do this, so it’s not full-proof. However – I believe it’ll work for 99% of use cases.

One last trick is to consider obfuscating the fact that you’re even going to SSRS. Often users will just want to export data out to excel, and you can accomplish an auto export to .XLSX by adding the following to the end of a URL:

&rs:Format=EXCELOPENXML

The full path REQUIRES the use of the Report Server link – so make sure your URL is in the format as:

localhost:8080/ReportServer/Reports?%2fTest+Report&LastName=Duffy&rs:Format=EXCELOPENXML

Notice that the rs:Format has replaced the rs:Command keyword.