1 Reply Latest reply on Feb 15, 2016 12:36 PM by DJ Petch

    Connecting to Sharepoint List View

    Derek Turner

      I have 3 SharePoint lists full of feedback data I need to aggregate and create reports on:

       

      1)  Instructor led courses

      2)  Virtual courses

      3)  eLearning courses

       

      I've followed this document: Connect to SharePoint Lists with OData

       

      I am able to successfully connect to #2 and #3 and create reports just fine.  However, I'm having trouble with getting Tableau to find the data in #1.  #1 is a SharePoint list that has multiple views.  When I look at the default view I see no data.  However, there is an admin view that I can switch to and see all the data.

       

      When I try to connect using the standard way indicated in the document, I get an error:

      'unable to connect to server.  check that the server is running and that you have access privileges to the requested database.'


      When I test the same URL I was using in the document, but this time in the browser (...site.../_vti_bin/ListData.svc/NLDLevel1ILTEvaluationSurvey) it works just fine and all the data is there.


      Any ideas on how I can get this to work?

        • 1. Re: Connecting to Sharepoint List View
          DJ Petch

          #1 should be identical to #2 and #3 so not sure what's going on there.

           

          Another option is to connect to SQL Server directly via Custom SQL. Just need to figure out the "generic" columns SharePoint uses in the tables. Here's a sample query where I match up the generic fields to the column names on the SharePoint list:

           

          SELECT

          d.tp_GUID AS "guid",

          d.datetime1 AS "assessment_date",

          d.datetime1 AS "assessment_datekey",

          d.nvarchar3 AS "shift",

          d.nvarchar14 AS "status",

          d.nvarchar1 AS "mrn",

          d.nvarchar4 AS "unit",

          d.nvarchar5 AS "room_bed",

          d.nvarchar6 AS "gender",

          d.datetime2 AS "start_date",

          d.sql_variant1 AS "hold_expectation",

          d.nvarchar7 AS "hold_reasons",

          d.nvarchar8 AS "hold_type",

          d.nvarchar9 AS "holds_behaviors_warranting_sa",

          d.nvarchar10 AS "safety_behaviors_warranting_sa",

          d.nvarchar16 AS "safety_reasons",

          d.nvarchar15 AS "one_to_one_assigned",

          d.nvarchar11 AS "interventions_attempted",

          d.nvarchar12 AS "admitting_diagnosis",

          d.nvarchar13 AS "ordering_physician",

          d.ntext2 AS "recommendations_comments",

          u1.tp_Title AS "created_by",

          d.tp_Created AS "created_date",

          u2.tp_Title AS "modified_by",

          d.tp_Modified AS "modified_date"

          FROM WSS_Content.dbo.AllUserData d

          LEFT OUTER JOIN WSS_Content.dbo.UserInfo u1

          ON d.tp_SiteId = u1.tp_SiteID

          AND d.tp_Author = u1.tp_ID

          LEFT OUTER JOIN WSS_Content.dbo.UserInfo u2

          ON d.tp_SiteId = u2.tp_SiteID

          AND d.tp_Editor = u2.tp_ID

          WHERE d.tp_SiteID = '123456789123456789123456789'

          AND d.tp_ListId = '987654321987654321987654321'

          AND d.tp_DeleteTransactionId = 0x

          ORDER BY d.tp_MODIFIED ASC

          ;