1 Reply Latest reply on Apr 27, 2018 10:19 AM by Brian Lenahan

    SQL stored in a Table to generate visualizations ad hoc

    Brian Lenahan

      I'm new to Tableau and have a question.

       

      We have a system the pumps out reports, but only in text tables, and we want to start to use Tableau to visualize these reports. The system stores the SQL in a table and calls it when generating the report, but like I said are text tables. We want to visualize the SQL that is stored so that a person can select a report and have it change the data on the fly.

       

      One issue is that there are variables in most of the SQL. We do have a crosswalk for the variable definitions (Staff in Selected Department SQL). These are from filters in the source system. Having this drive parameters would be awesome.

       

      Another thing is to insure that only the authorized people see the particular report. The AccessProfile field controls this.

       

       

      Is all of this possible?

       

       

      Example of data sets:

       

      SQL table

      ReportTitle
      ReportCode
      ReportNotes
      AccessProfilesNotes
      Count of items in Inventory1244SELECT ID, COUNT(ID) FROM items WHERE ItemType = 'Hardware' GROUP BY ID|4|6|This report gets total number of items.
      Count of Staff in Selected Department1366SELECT ID, COUNT(ID) FROM items WHERE Department = {DeptID} AND Fyear = {Syear} GROUP BY ID|1|This report gets total number of staff in a department.

      question.png

      parameter crosswalk

      variableVcodecustomLabelcustomCode
      DeptID1Human Resources334
      DeptID1IT335
      Syear2201710
      Syear2201811

       

      AccessProfiles profiles

      StaffID
      Profile
      ProfileID
      58416545812Administrator1
      58416545812Report Viewer4
      58416545812Report Editor6