6 Replies Latest reply on Dec 21, 2016 12:00 PM by K P

    Condition by Formula

    K P

      Hello, I have a field called ID. and a Parameter called PARAMETER1.

      I have ID field on filter shelf with following condition by formula

       

      [ID] = TRIM(SPLIT([PARAMETER1],"$",1))

      OR

      [ID] = TRIM(SPLIT([PARAMETER1],"$",2))

      OR

      [ID] = TRIM(SPLIT([PARAMETER1],"$",3))

       

      I am filtering the ID field through URL using parameter.

      as parameter value, I am passing

      "1"  ---- that will keep only ID value 1 in report

        OR

      "1$2" --- that will keep only ID value 1 and 2 in report

      OR

      "1$2$3" --- that will keep only ID value 1, 2 and 3 in report.

       

      THIS IS WORKING COMPLETELY FINE..

      NOW,

      The solution above doesn't support to select all values of ID (that might be in thousands and keep increasing everyday)

      So, to select all values in ID field, (in another words not to apply any filter on ID filed)

      I have change my formula as below.

      I have added  new parameter called PARAMETER2

       

      CASE [PARAMETER2]

      WHEN "MULTIPLE" THEN

      (

           [ID] = TRIM(SPLIT([PARAMETER1],"$",1))

           OR

           [ID] = TRIM(SPLIT([PARAMETER1],"$",2))

           OR

           [ID] = TRIM(SPLIT([PARAMETER1],"$",3))

      )

      WHEN "ALL" THEN [ID]

      END

       

      I get following error.

      "Expected type Boolean, found string. Comparison in 'CASE' expression must be Boolean type"

       

      How do I fix this?

       

      OR is there any other way I can select all values of ID as well?

       

      Thanks in advance.

        • 1. Re: Condition by Formula
          Maciek La

          You are comparing Boolean and string in the case statement

          This part:

               [ID] = TRIM(SPLIT([PARAMETER1],"$",1))

               OR

               [ID] = TRIM(SPLIT([PARAMETER1],"$",2))

               OR

               [ID] = TRIM(SPLIT([PARAMETER1],"$",3))

           

          will most likely result in true/false result

           

          while this one:

          WHEN "ALL" THEN [ID]

          will give certain id.

           

          try this:

          CASE [PARAMETER2]

          WHEN "MULTIPLE" THEN

          (

          if  [ID] = TRIM(SPLIT([PARAMETER1],"$",1))

               OR

               [ID] = TRIM(SPLIT([PARAMETER1],"$",2))

               OR

               [ID] = TRIM(SPLIT([PARAMETER1],"$",3))

          then [ID] end

          )

          WHEN "ALL" THEN [ID]

          END

          • 2. Re: Condition by Formula
            K P

            Maciek La

            Thank you for your input.

            Let me try it and will let you know if it worked for me..

            Again, I appreciate your time to help me.

            • 3. Re: Condition by Formula
              K P

              Hello,

              Still getting the same error...

              "Expected type Boolean, found string. Comparison in 'CASE' expression must be Boolean type"

              • 4. Re: Condition by Formula
                Sarah Ebreo

                Hi KP,

                 

                If an "ALL" option is added to PARAMETER1 does the following calculation work to filter the view?

                 

                [ID] = TRIM(SPLIT([PARAMETER1],"$",1))

                OR

                [ID] = TRIM(SPLIT([PARAMETER1],"$",2))

                OR

                [ID] = TRIM(SPLIT([PARAMETER1],"$",3))

                OR

                [PARAMETER1] = "ALL"

                 

                If not, can you post a packaged workbook with sample data so that we can see how the view and data is set up?

                 

                Best,
                Sarah

                1 of 1 people found this helpful
                • 5. Re: Condition by Formula
                  K P

                  Hello Sarah Ebreo,

                  Yes but then list will be fixed.. that why I needed another approach.

                  • 6. Re: Condition by Formula
                    K P

                    I figured it out.

                     

                    I used Maciek La's approch but not inside the filter formula.

                    I created a new field with it and put the new field in filter shelf and excluded nulls.

                     

                    WORKED.

                    THANK YOU ALL