Tableau – Parse Parameters From URL


Well, file this under “just because you can doesn’t mean you should.” Search the forums on parsing strings with Tableau and you’ll find a lot of guidance which pretty much says “don’t do it!” and “better to do it in your source!” These people are undoubtedly right. But, what if your source is an existing Tableau extract which you can’t modify? In that case, parsing with Tableau may be your only option. I found myself in this very situation a few weeks ago. Being the wannabe hacker that I am, I came across the very nifty  REGEXP_EXTRACT_NTH tableau function. With it you can create a calculated field using REGEX patterns to pull your desired parameter from a URL string.

For my specific case I needed to parse date parameters from the URL in the SQL Server Reporting Services Execution Log (ReportServer.dbo.ExecutionLog3 to be precise). The URL was stored in the following format in my extract:

 

StartDate=09%2F14%2F2017%2000%3A00%3A00&EndDate=09%2F13%2F2017%2000%3A00%3A00&GroupId=242440B7-99C4-E211-AC15-E61F13277AAB&GroupID_Upper=242440B7-99C4-E211-AC15-E61F13277AAB

 

I needed to get the StartDate and End Date parameters and convert them to a format which could be used by Tableau.  One option which I considered was to perform some sort of substring looking for the parameter name (e.g. StartDate) and then taking the 29 characters following it (29 because all slashes, spaces, and colons had been converted to their URL encodings). This probably would have been fine as long as my parameters were always in the same format of “MM/DD/YYYY hh:mm:ss”. But what would happen if one of the date parameters was sent as just a date “MM/DD/YYYY”? Or perhaps we would like to visualize a parameter which was not a fixed format? An ID perhaps?

Luckily, REGEX and REGEXP_EXTRACT_NTH to the rescue! The following expression finds the parameter called “EndDate=” in the Dimension “Report Parameter Desc”. Once “EndDate=” has been found the Calculated field will return all data found between “EndDate=” and the first “|” (aka vertical pipe) or the end of the line. This same technique can be used with any URL parameter, not just dates. Just replace “Reprt Parameter Desc” with your dimension which contains the URL and replace “EndDate=” with the parameter you are looking for. Make sure to include the equal sign!

The calculated field then uses REPLACE to put the appropriate spaces, colons, and slashes in place. The final step is converting the value to a DATE using the Tableau DATE function. If you don’t need the value to be a DATE then remove the DATE function.

DATE(REPLACE(
REPLACE(
 REPLACE(
 REGEXP_EXTRACT_NTH ([Report Parameter Desc], '(EndDate=)(.*?)(&|$)', 2) 
 ,'%2F'
 ,'/'
 )
 , '%20'
 , ' '
)
,'%3A'
,':'
))

Let me know if this works for you!

Scarlet The Tegu loves parsing URL strings in Tableau. And eating worms.

 

, ,

Leave a Reply

Your email address will not be published.