Filter Tableau PDF and CSV downloads like a boss using the REST API
Вставка
- Опубліковано 8 сер 2021
- Let's skip the lecture about how Tableau is an interactive visualization tool and how it's a tragedy that so many business users want to download PDF and CSV data dumps.
Instead, let's dive right into how you can pull all the levers to automate the process of downloading PDF and CSV files from your existing visuals using the REST API.
In a previous video we did a deep dive covering a common pitfall when filtering on date parameters using the REST API. If you're struggling with that topic specifically, check out that video:
• Querying Tableau views...
Here's a related blog post showing more examples of querying view data using tableau-api-lib:
/ query-your-tableau-vie...
For more written tutorials, check out the Medium blog posts!
/ elliottstam
Python version used: 3.8
Coding environment: Jupyter Lab
Tableau Server REST API endpoint focus:
query view pdf
To make sure you have all the latest features, update tableau-api-lib:
pip install -U tableau-api-lib
Getting started with tableau-api-lib: • tableau-api-lib (blitz...
Join the Tableau Developer Program to get involved. It coms with a free Tableau Online developer site!
www.tableau.com/developer
Thank so much for amazing videos .. is it possible to get all the filter values populated in the view , something like the getfiltersync() JavaScript API.
I am web developer and in my app there is one requirement to integrate tableau excel crosstab download.
I am able to apply filters and download tableau reports in excel format but I want to apply some advance search filters like date range.
For single date it will give me the expected report but for date range I am not able to get expected filtered excel. Do you have any idea how to pass date range?
Heyyy Elliot, thanks a lot for this amazing video!!
Quick question about the output and I wonder if you would like to help advise: when i tried to download img/pdf, the output files were always with all the filter boxes stacked on the right-hand side; however they didn't show up in your results.
Did you set something special here? Thanks in advance!
Hey Cher, thanks! I took a quick look and I don't see any special settings I put in place. There is a chance there could be some defaults in the server itself (Tableau Server), but that's just a guess on my part. Sorry I'm no help here -- I'm not sure why you would be seeing the filters when running the same code.
@@devyx No problems at all! Thank you so much for looking into it and getting back to me! Will try to figure it out as well & let you know if I found something!
Thank you so very much again!
can you customize the pdf download? E.g adding page numbers or ensure that the pdf shows the whole table and is not "cut off"? Printing tables in Tableau using the default button option or the server can be a nightmare
Hey Daniel, you can try "unspecified" instead of the typical PDF varieties (A3, A4, etc) but if you have a visual/table that is long enough to have scroll bars then I think the basic PDF output available currently through the REST API may be limited for certain use cases.
These are very useful videos, thank you! Do you have any tips on how to troubleshoot when you aren't getting any results from your filters/parameters? I have a rather complex dashboard with a lot of filters and parameters but no matter what I put in I get the pdf back, but with no data.
This sounds like a problem with how your filter/parameter names or values are translating to the URL representation.
For example, if you have a parameter named "movie" and want to filter to "The Matrix", the correct value to filter on would be "The%20Matrix". Both the param/filter names and values must be represented in their URL-friendly form.
When the name is the issue, the filter or parameter is simply ignored. This makes me think you have the name correct, but then the value perhaps is not registering.
If you share a sample name and value combo that should work but isn't working, I can adapt it to what I think should work.
Also are you trying to specify date filters? Something you can do to troubleshoot is open the .twb file in a text editor and see how the values appear there. Dates have to be in the same form they appear in that raw file to work, in my experience.
@@devyx Thanks for the tips. I'm still not getting results, and from what I can see the formatting of dates and everything is correct, but I'll keep digging into it. I'm also seeing filters that have federated.GUID and calculation.GUID when I open the workbook in a text editor. Do you know how those filters should be handled in the api?
@@loadymcupload if you toss an example of a filter/param name and value that you think should work but isn't then I'll offer an example of how that should be handled.
@@devyx Here is one that isn't coming through: and in the URL it's passing: vf_Document%20Number%20Level%2001%20tech%20key=120015545
@@loadymcupload Looks like a mistake has been made in not including the underscore characters from the name. In the version the URL has, there are no underscores in the '01_tech_key' portion. Try changing it such that your parameter dict has this as one of the filter values:
"vf_Document%20Number%20Level%2001_tech_key"
The %20 replacement is for spaces only. A good thing to know about here is that urllib can help you here. Run this code:
import urllib
urllib.parse.quote("Document Number Level 01_tech_key")
And you'll get this result:
'Document%20Number%20Level%2001_tech_key'
Hope that helps!
Thank you so much, very informative tutorial.
I’m using tableau desktop and regularly publish the dashboard into internal Tableau server of the whole organization. I’m trying to follow your steps from the beginning to the end, however once I get pdf file back it seems like it is returned a blank pdf file or I’m not sure it is a bytecode maybe. So the problem I have here, do I need further authentication from Tableau admin who is taking care the server? Thank you.
Are you passing any filters in the request? Also if you share the code you're using to write the PDF file I might be able to spot any issues.
@@devyx Thank you for the responses, my bad I have checked my python code. The reason the pdf file did not show up is because I specify the wrong column, it is supposed to be id not workbook id. :)
@@jermritdeeprom6370 Ah, nice that you spotted it! Good luck 🙌
Hey Elliot, thank you so much for building the library and making these videos. Huge life saver!
I was able to follow the instructions but I got stuck on setting the filters. I wonder have you come across this issue before and do you have any advice on how to handle it?
The filters of my views are somewhat dependent on each other: for example: my view only have 5 filters by default, one of the filter is "View report by" which has 2 values: "Quarter" and "Month".
If I select "View report by"="Quarter" , then there are 5 filters for the view. If I select "View report by"="Month" , then there is an extra filter pop up to allow me filter which quarter I want: filter "Quarter" which has 4 values: "Q1", "Q2","Q3", "Q4".
What I am after is to select: "View report by"="Month", "Quarter"= "Q2", but when I tried to build custom_url_params with 6 filters (4 other filters +"View report by"="Month", "Quarter"= "Q2"), it gets no data and the pdf download only showed 5 filters were selected.
Thank you!!!!
Hey Lily, I have seen this many times! A few key things to know about translating filters and parameters as seen in a workbook to URL parameters in the API queries:
1) You must reference the actual underlying name of the field and values. For example, if your filter is using a field from the datasource which is "country_name" and you have aliased this as "Country" in your workbook for better useability, you need to use the "country_name" value in your queries. Same goes for values. If you aliased "The Netherlands" as "NL", you must use the underlying name rather than the alias.
2) Your filters must be URL-friendly, which means processing the values to be compatible in a URL. For example, "The Netherlands" becomes "The%20Netherlands" as a blank space is not compatible with URLs and the space must be translated to the URL-friendly equivalent, in this case "%20". You can run your values through the urllib.parse.quote function to convert text to URL-friendly form.
Hope that helps!
@@devyx Thanks so much for getting back to me so quickly! I have used the urllib to parse the filter names and values. I thought the first point might be the issue, I will test it out today! 😊
@@devyx Hey Elliot, I tested out and referenced the actual underlying name of the field and values, and also found out some of my fields are parameters not filters, I finally was able to query the pdf of my view (there are data in the pdf). However, there is still shows no data when I query the csv of the same view. Have you come across this issue before?
Another question is about the library, does it support download view/workbook as excel? I can see there are methods for downloading as pdf but didn't find as excel.
Thanks again for this amazing library!!!!
@@lilyzhang446 Hey Lily, one thing to check is if you are querying the view ID for a dashboard versus an actual sheet. You will not get the expected results if you use the dashboard's view ID. Instead, identify the view ID for the actual sheet whose data you would like.
I'll follow up later on the endpoint question.
@@devyx Thanks for getting back to me! Yeah I double checked that I was using the view ID of the sheet I was querying the data from.
When I ran "view_pdf_response = conn.query_view_pdf(view_id=dashboard_id, parameter_dict=custom_url_params)
view_pdf_response", I was able to get pdf with all the data I needed.
However, when I ran "view_data_response = conn.query_view_data(view_id=dashboard_id, parameter_dict=custom_url_params)
view_data_response", there is no data in the csv. So my guessing is view_id and parameters are fine, which allowed me to query the pdf. Not sure what's wrong with querying the data as csv...
Hi, I can't seem to find the project_name when I am flattening out the second data frame. Could you please help?
Hey Pavan, you aren't doing anything wrong -- it seems that Tableau has made a change internally and even though our API calls are specifying that we want "_all_" fields returned when querying the views on site (the function you are using is using the 'Query Views for Site' endpoint behind the scenes), Tableau is not returning all of the fields.
We are supposed to get back the project id, name, and description. Instead we are only getting the project id. At the time I made the tutorial, the endpoint was working as advertised by Tableau's documentation. It is not working as advertised currently, which is unfortunate.
However, if you need that project name you can make use of the `querying.get_projects_dataframe` method in the library and then merge the dataframes by project id to get your project names into your views dataframe.
Here is some code you can try out, run it after you have flattened your `projects` column that results in having the `project_id` column in your `views_df`:
projects_df = querying.get_projects_dataframe(conn)[["name", "id"]]
projects_df = projects_df.rename(columns={"name": "project_name", "id": "project_id"})
views_df_joined = views_df.merge(projects_df, how="left", on="project_id")
Hey I am getting SSL certificate verification error upon sign in tableau server.... Any solution
First check if your server uses SSL, is the URL beginning with http or https? If you don't have SSL you can try setting the 'verify_ssl' flag to False when initializing your TableauServerConnection.
Also worth confirming with your infrastructure team:
* Is the REST API disabled for Tableau Server?
* Is the server air-gapped (completely isolated from internet traffic)?
* Are the SSL certs configured correctly?
Beyond that it's a shot in the dark for me because SSL certs are more about how your infra team approaches networking, this isn't something related to the Tableau APIs or to this Python library.