Hey all, just wanted to update this for people with the same question.
Tableau can use a stored function as a custom sql statement. If your function returns xml like mine did then the 'value' [http://msdn.microsoft.com/en-us/library/bb384768.aspx] method for XQuery works like it should.
Here's an example custom sql statement that references xml using .value():
Store function name:
Select dbo.fn_Stored_Function_Example(4298,2).value('(/root/xpath/to/data)',VARCHAR(100)') as Column_ID;
I am wondering if this will work for a stored procedure in SQL Server? I am returning data and not an xml document, but it looks like if I get my syntax correct that I could use a stored procedure as a data connection.
I am wondering if this will work for a stored procedure in SQL Server? I am returning data and not an xml document, but it looks like if I get my syntax correct that I could use a stored procedure as a data connection. I am the anonymous user that just posted...
You can't use stored procedures, but you can use table-valued functions in SQL Server 2005+. They can accept parameters and can contain complex logic like stored procedures. Tableau can connect to those.
Hello Doug76. Have you used table-valued functions in dashboards? I have a dashboard that is using a filter on client - so a user selects the client and the rest of the worksheets filter to information about that client. How do I pass a parameter to the function from what is selected on the dashboard?
Thanks so much!
You can use stored procedures in a custom SQL connection as described in this thread:
Do note the comments about the efficiency, though. Tableau generated queries will be selecting from the results of evaluating the stored procedure, so you end up evaluating the result set multiple times behind the scenes. Often not a problem for fast stored procedures, but it can make opening worksheets and manipulating the view a bit painful if the stored procedure takes a long time or returns lots of results.
You (or your DBAs) also may not want to enable Ad-Hoc Distributed Queries.
Is there a way to have Tableau recognize a Web Intelligence query that I save to my favorites?
No idea, sorry - I don't even know what one of those is. ;-)
Matt, I will repost the question there just for reference. You are correct. Web Intelligence is a part of SAP BusinessObjects. Thanks for the response.
Richard, thanks for responding.
***but you can use table-valued functions in SQL Server 2005+. They can accept parameters and can contain complex logic like stored procedures. Tableau can connect to those.***
Can anyone elaborate on table-valued functions in SQL Server? I would like to create a function in SQL server that takes a date range and several other parameters (which would be selected using Tableau quick filters) and returns a single value.
The single value returned would be the ratio of the number of records returned by 2 queries based on the parameters passed from Tableau.
I would also like to know how to pass filter selections to parameters of a user defined table valued function. Thank you
I have a lot of stored procs that connect to reports - but what I did (you may or may not have the access rights to do this in a prod environment) but I use the stroed proc to dump the resulltant data back into a table object and then was able to connect to that data in the report table in Tableau just like it was coming from the stored proc.
I can do this mostly because my report data is refreshed on a 24 hour cycle - so each night the ETL process runs and the data in the report table is refreshed also.
You could develop this in you dev env - and then put in the request to have it moved to prod after testing.
Insert Into 'rpt_Table' From Exec 'storedProc'
just make sure the columns in the rpt_table match the output columns from the stored proc
Put all of that in a stored proc or script that runs each time the data is refreshed from the main source
just add code in the proc to drop and create the report table each time or truncate it after it is created the first time
You can't use (Insert Into 'rpt_Table' From Exec 'storedProc')
in a table Valued function