3 Replies Latest reply on Dec 29, 2015 5:47 PM by Frog Toad

    OBI, get results using presentation variable

    Frog Toad

      Hello!

       

       

      I have text field with long text strings.

      I want get results, using filter "contains all" by this field

       

      Example:

      Values
      aaaa000
      aa000aa
      bbbaa

       

      I input to filter "a0" and i want get next results:

      Values
      aaaa000
      aa000aa

       

      But if filter field will be empty, i get error, but i want get all rows to result.

      How i can make it?

       

       

      I made the following:

      1. In analysis i maked new filter, type "CONSTAINS ALL". ":number" used as Presentation variable

      1 new analysis.jpg

       

       

      2. I made new Dashboard prompr, where i made variable prompt, named ":number", type "Presentation variable"

      2 parameter.jpg

       

       

      3. I made new dashboard, where I dragged my Dashboard prompr and Analysis

       

       

      Now, when filter-field "number" is empty, i get error (and i agree with OBI, because i did not specify rules for null value):

      3 bad with empty.jpg

       

       

      And when i write any values in filter field, everything is working

      4 good when not empty.jpg

       

       

      I tried fix it in analysis, used "Convert this filter to SQL" and wrote next sql-text:

      "presentation_tmp"."WB_NUMBER" LIKE
      CASE
        WHEN @{:number} IS NULL THEN "presentation_tmp"."WB_NUMBER"
        ELSE concat(concat('%',@{:number}),'%')
      END
      

       

      But it not helped me

       

       

      How i can get all rows, when filter-field "number" is empty?