0 Replies Latest reply on Jan 8, 2014 3:11 PM by Rossella Blatt Vital

    dynamic user filtering on 3 dimensions

    Rossella Blatt Vital

      Hi,

       

      I am trying to dynamically filter the data displayed in the workbook based on permissions assigned to the user on 3 fields.

      Each user can see none, one or a combination of the following fields:

       

      1) REGION: {NA, EMEA, APAC}

      2) BUSINESS_LINE: {PCS, C&C, FFO&E}

      3) SUPERGROUP_NAME: the name of the supergroup

       

      When one of the three dimension is Null, then it means that the user can see ALL values of that dimension (so no filtyer necessary for that dimension).

      When SUPERGROUP_NAME is populated with the name of the allowed supergroup, then REGION and BUSINESS_LINE are always empty (no need to filter by REGION and BUSINESS_LINE) and viceversa.

       

      In the image below some examples:

       

       

      For example the first row allows to see only supergroup DRW across ALL regions and ALL business line.

      The last row allows to see ALL supergroups, but only for the region EMEA and NA and for the BUSINESS_LINE = PCS.

      The workbook will be integrated into a portal, which service will pass the 3 fields to Tableau.

      In order to build the user filters I am currently using an xls sheet.

      I added to my workbook a second connection to the excel file with the permissions (header and 1 row only of data).

      Then I created 3 calculated fields that, after converting to continuos, I dropped to the filter shelf in order to filter only the rows = 1. Though, this is working only when I use 1 dimension at a tim

       

      // USER FILTER - Supergroup

      IF ATTR([SUPERGROUP_NAME]) = ATTR([Sheet1 (User_filter.xlsx)].[SUPERGROUP_NAME]) OR ISNULL(ATTR([Sheet1 (User_filter.xlsx)].[SUPERGROUP_NAME]))

      THEN 1

      ELSE 0

      END

       

      // USER FILTER - REGION

      IF [USER FILTER - Supergroup ] = 1

      THEN 1

      ELSE

          IF ATTR([REGION]) = ATTR([Sheet1 (User_filter.xlsx)].[REGION]) OR ISNULL(ATTR([Sheet1 (User_filter.xlsx)].[REGION]))

          THEN 1

          ELSE 0

          END

      END

       

       

      // USER FILTER - BL

      IF [USER FILTER - Supergroup ] = 1

      THEN 1

      ELSE

          IF ATTR([BUSINESS_LINE]) = ATTR([Sheet1 (User_filter.xlsx)].[BUSINESS_LINE]) OR ISNULL(ATTR([Sheet1 (User_filter.xlsx)].[BUSINESS_LINE]))

          THEN 1

          ELSE 0

          END

      END

       

      These calculated fields though don't work as expected. Infact, they are always = 1. when I remove the isnull condition in the formulas, then the output is correct ONLY if I link 1 field only...

       

      So I guess I am doing something wrong...

       

      Any suggestion?

      Many thanks for any help

      Rossella