-
1. Re: how to join table when using Sharepoint list via OData connection ?
2 of 2 people found this helpfulHi Francois -
In Tableau, one cannot JOIN OData feeds as if they are relational database tables.
To put it another way, we see an OData feed as a single table inside a distinct database, not one of many tables (lists) inside the same database (SharePoint)
That being said, you still have a couple options:
- Create a single OData feed which is based on a list which itself is the product of joining multiple lists. Then, consume that "master feed" with Tableau - I believe you can create the "master list" using SharePoint Designer and/or Visual Studio. I'm a little bit hazy on exactly where to point you, but I recall doing this myself a few years ago for a different reason.
- Connect to each OData feed via a different Tableau Data Source, and then blend the data sources together. You can read more about Data Blending here and here
Have fun!
FYI, here's a link which shows you how to "join lists" in SharePoint: http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx
Adding reference to "how to join lists" in SharePoint
-
2. Re: how to join table when using Sharepoint list via OData connection ?
Hi Russell,
Thanks for the insight. Following my post, I realize that my issue was more that I was trying to "join" the value that are set as lookup table in my SharePoint list: the OData call returns the ID of the lookup item instead of the selected field. I have bee searching & trying to use the "&expand" parameter of the OData call with no success (I always get the ID not the item value) - any help on what I am missing ?
These are links to examples:
http://leowu70.blogspot.ca/2010/11/rest-using-listdatasvc.html
-
3. Re: how to join table when using Sharepoint list via OData connection ?
Afraid I can't help you much on this question - I'd try a SharePoint-related MSDN forum for this problem - not really related to Tableau
-
4. Re: how to join table when using Sharepoint list via OData connection ?
Well, it can be a Tableau question / issue if the "$expand" parameter seem to work and return the expected data when I run the OData query outside of Tableau - do you know if the "$expand" parameter is supported in Tableau ?
-
5. Re: how to join table when using Sharepoint list via OData connection ?
$expand tells the OData service what data to send, but (I don't think) informs the client (Tableau) that additional columns (based on expansion) are going to be in play.
For example:
//Shows categories of products in an internet browser
http://services.odata.org/OData/OData.svc/Categories
//Shows categories of products AND nests the products themselves under each category
http://services.odata.org/OData/OData.svc/Categories?$expand=Products
Tableau treats these queries the same from what I can see. No extra fields related to the nested products get added to the resultset
But PowerPivot does the same thing:
...and OData Explorer:
Are you seeing a real OData consumer (not just the output in a browser) work with your $expand?
-
6. Re: how to join table when using Sharepoint list via OData connection ?
Hi Russell,
I did use PowerPivot as a OData Consumer other then Tableau and, when I set the "Include Expanded Entities" Advanced option to "True", I do get the extra fields that I am looking for like expected (unlike in Tableau):
-
7. Re: how to join table when using Sharepoint list via OData connection ?
Interesting! It may be a client limitation, then. I see you've opened up a case with support - Rick will take good care of you
-
8. Re: how to join table when using Sharepoint list via OData connection ?
Just wondering what's the solution for this? Thanks.
