6 Replies Latest reply on Aug 30, 2017 11:58 PM by eva.lesny

    Parameter filter

    Sameer Gujar

      Hi,

       

      I would like to filter rows based on wildcard search passed via parameter.

       

      For e.g. Filter all rows in report based on dimension "Title" by searching for "build" value.

                     "Title" dimension values: 1. Build certification path

                                                             2. Testing code

                                                             3. Building maintenance.

                     Result should display 1. and 3.

       

      Appreciate any help with this.

       

      Thanks,

        • 1. Re: Parameter filter
          Simon Runc

          hi Sameer,

           

          So here are a couple of ways...the first one just uses the WildCard filter, although this is case-sensitve

           

          or alternatively (with a parameter), which I set up as a string and "any" value allowed, we can make it case in-sensitive by wrapping everything in an upper() (I then bring this field onto the filter shelf and set to true)

           

          [Filter on Wild Card]

          CONTAINS(UPPER([Title]), UPPER([Wild Card Filter]))

           

          Hope, either of these, solve your problem

          1 of 1 people found this helpful
          • 2. Re: Parameter filter
            eva.lesny

            Simon Runc was faster

             

            Or use "Title" on the Filter shelf, then in "Condition by formula" use Simon's calculation

            CONTAINS(UPPER([Title]), UPPER([Wild Card Filter]))

             

            You don't need to create it as a calculated field then.

             

            I'm also not sure you actually need to use the upper / lower case. When I tried it out with my own data it didn't check against lower or upper case letters.

            I say this in case it's important for you to distinguish between upper and lower case letters.

            • 3. Re: Parameter filter
              Simon Runc

              hi Eva,

               

              Nice...always like a simplification!

               

              On the Upper/Lower

              I'm also not sure you actually need to use the upper / lower case. When I tried it out with my own data it didn't check against lower or upper case letters.

               

              It all depends on the datasource. Against some Live connections, like Excel (where the query is being run in the Live datasource, using it's native sql-driver) they can be case sensitive, but others (TDEs for example) aren't.

              1 of 1 people found this helpful
              • 4. Re: Parameter filter
                Sameer Gujar

                Thanks Simon /Eva. Solution worked!

                1 of 1 people found this helpful
                • 5. Re: Parameter filter
                  Ray Givler

                  Hey Guys,

                  Just a thought on this. If you are using the parameter, and you haven't entered a value yet, the filter approach will remove nulls (at least it did for me when I tried to recreate this). So if you want the full list of Title and if Title may include nulls, you may want your filter formula to be:

                  CONTAINS(UPPER([Title]), UPPER([Wild Card Filter])) or

                  (isnull([Title]) and [Wild Card Filter]="")

                   

                  I tried isnull([Wild Card Filter]) for the last part of the null check, but that did not work.

                  1 of 1 people found this helpful
                  • 6. Re: Parameter filter
                    eva.lesny

                    It all depends on the datasource. Against some Live connections, like Excel (where the query is being run in the Live datasource, using it's native sql-driver) they can be case sensitive, but others (TDEs for example) aren't.

                    Thanks for the explanation!