3 Replies Latest reply on Nov 12, 2016 5:44 PM by John Croft

    Making My Parameter Filter more efficient

    Adam Ehrenworth

      I currently have a working dashboard that allows a users to filter by Leader ID by using a parameter. They can search by name of the leader or the Leader ID number.  As background the data being reviewed is 15 hierarchy columns (if the search value appears in any of the 15 columns that row is included in the data view). Right now I am noticing some performance issues for larger returns.

       

      Here is the code for the Calculated field that says whether to Hide or Show the row:

       

      IF([Level01]=Split([Find Leader],' - ',2)) OR [Level01] = [Find Leader] THEN "Show"

      ELSEIF ([Level02]=Split([Find Leader],' - ',2)) OR [Level02] = [Find Leader] THEN "Show"

      ELSEIF ([Level03]=Split([Find Leader],' - ',2)) OR [Level03] = [Find Leader] THEN "Show"

      ELSEIF ([Level04]=Split([Find Leader],' - ',2)) OR [Level04] = [Find Leader] THEN "Show"

      ELSEIF ([Level05]=Split([Find Leader],' - ',2)) OR [Level05] = [Find Leader] THEN "Show"

      ELSEIF ([Level06]=Split([Find Leader],' - ',2)) OR [Level06] = [Find Leader] THEN "Show"

      ELSEIF ([Level07]=Split([Find Leader],' - ',2)) OR [Level07] = [Find Leader]THEN "Show"

      ELSEIF ([Level08]=Split([Find Leader],' - ',2)) OR [Level08] = [Find Leader] THEN "Show"

      ELSEIF ([Level09]=Split([Find Leader],' - ',2)) OR [Level09] = [Find Leader] THEN "Show"

      ELSEIF ([Level010]=Split([Find Leader],' - ',2)) OR [Level010] = [Find Leader] THEN "Show"

      ELSEIF ([Level011]=Split([Find Leader],' - ',2)) OR [Level011] = [Find Leader] THEN "Show"

      ELSEIF ([Level012]=Split([Find Leader],' - ',2)) OR [Level012] = [Find Leader] THEN "Show"

      ELSEIF ([Level013]=Split([Find Leader],' - ',2)) OR [Level013] = [Find Leader] THEN "Show"

      ELSEIF ([Level014]=Split([Find Leader],' - ',2)) OR [Level014] = [Find Leader] THEN "Show"

      ELSEIF ([Level015]=Split([Find Leader],' - ',2)) OR [Level015] = [Find Leader] THEN "Show"

      ELSEIF ([Find Leader]="*") THEN "Show"

      ELSE "Hide" END

       

      I have been told using Cases potentially would work better but not understanding that easiest way to rewrite the code to support that.

       

      Any suggestions?

       

      Thanks!


      Adam

        • 1. Re: Making My Parameter Filter more efficient
          Sherzodbek Ibragimov

          Adam,

          Please post your sample data with with more data so we can look at it. Otherwise, it is tough to guess. Thanks\

          Shrezod

          • 2. Re: Making My Parameter Filter more efficient
            Jonathan Drummey

            Since sometime in version 9 CASE statements and IF statements have been equivalent in performance, I think the bigger issue here is that there are >30 comparisons being made for each row in the data.

             

            Here’s my suggestion:

             

            1) Pivot the data so instead of having 15 Level columns for each row there are 15 rows for each original row in the data.

            2) Use a wildcard filter on the new pivot field value.

             

            This gets rid of the requirement for a parameter and the associated calculations.

             

            Jonathan

            • 3. Re: Making My Parameter Filter more efficient
              John Croft

              I like Jonathan's suggestions. If that doesn't work for you, you may just want to try different approaches:

               

              IF([Level01]=Split([Find Leader],' - ',2)) OR [Level01] = [Find Leader]

              OR ([Level02]=Split([Find Leader],' - ',2)) OR [Level02] = [Find Leader]

              OR ([Level03]=Split([Find Leader],' - ',2)) OR [Level03] = [Find Leader]

              OR ([Level04]=Split([Find Leader],' - ',2)) OR [Level04] = [Find Leader]

              OR ([Level05]=Split([Find Leader],' - ',2)) OR [Level05] = [Find Leader]

              OR ([Level06]=Split([Find Leader],' - ',2)) OR [Level06] = [Find Leader]

              OR ([Level07]=Split([Find Leader],' - ',2)) OR [Level07] = [Find Leader]

              OR ([Level08]=Split([Find Leader],' - ',2)) OR [Level08] = [Find Leader]

              OR ([Level09]=Split([Find Leader],' - ',2)) OR [Level09] = [Find Leader]

              OR ([Level010]=Split([Find Leader],' - ',2)) OR [Level010] = [Find Leader]

              OR ([Level011]=Split([Find Leader],' - ',2)) OR [Level011] = [Find Leader]

              OR ([Level012]=Split([Find Leader],' - ',2)) OR [Level012] = [Find Leader]

              OR ([Level013]=Split([Find Leader],' - ',2)) OR [Level013] = [Find Leader]

              OR ([Level014]=Split([Find Leader],' - ',2)) OR [Level014] = [Find Leader]

              OR ([Level015]=Split([Find Leader],' - ',2)) OR [Level015] = [Find Leader]

              OR ([Find Leader]="*") THEN "Show"

              ELSE "Hide" END

               

              ----------

              This would give you a True / False indicator if the syntax works.

               

              [Level01]=Split([Find Leader],' - ',2)) OR [Level01] = [Find Leader]

              OR ([Level02]=Split([Find Leader],' - ',2)) OR [Level02] = [Find Leader]

              OR ([Level03]=Split([Find Leader],' - ',2)) OR [Level03] = [Find Leader]

              OR ([Level04]=Split([Find Leader],' - ',2)) OR [Level04] = [Find Leader]

              OR ([Level05]=Split([Find Leader],' - ',2)) OR [Level05] = [Find Leader]

              OR ([Level06]=Split([Find Leader],' - ',2)) OR [Level06] = [Find Leader]

              OR ([Level07]=Split([Find Leader],' - ',2)) OR [Level07] = [Find Leader]

              OR ([Level08]=Split([Find Leader],' - ',2)) OR [Level08] = [Find Leader]

              OR ([Level09]=Split([Find Leader],' - ',2)) OR [Level09] = [Find Leader]

              OR ([Level010]=Split([Find Leader],' - ',2)) OR [Level010] = [Find Leader]

              OR ([Level011]=Split([Find Leader],' - ',2)) OR [Level011] = [Find Leader]

              OR ([Level012]=Split([Find Leader],' - ',2)) OR [Level012] = [Find Leader]

              OR ([Level013]=Split([Find Leader],' - ',2)) OR [Level013] = [Find Leader]

              OR ([Level014]=Split([Find Leader],' - ',2)) OR [Level014] = [Find Leader]

              OR ([Level015]=Split([Find Leader],' - ',2)) OR [Level015] = [Find Leader]

              OR ([Find Leader]="*"