3 Replies Latest reply on Jan 27, 2015 11:35 AM by Gianni Ceresa

    OBIEE 11g: Presentation variable syntax in direct database request

    srini9148

      HI,

       

      How to pass "all column values" from prompt to direct database request in obiee 11.1.1.7.0.

       

      I mean how to represent presentation variable in direct database request, default selection in prompt is "All Column Values" ?

       

      Regards,

      Srinivas.

        • 1. Re: OBIEE 11g: Presentation variable syntax in direct database request
          Gianni Ceresa

          Hi Srinivas,

          In your direct database request you can reference a presentation variable with the standard syntax of @{variable_name}{default value} .

          If the content is a varchar it's up to you to insert it between ' to not produce an invalid query.

          When you select the option "(All Column Values)" the variable will contains that value ( @{variable_name} = "(All Column Values)" ), so you can manage it in your query directly:

          SELECT ... WHERE (your_column = '@{variable_name}{xxxxxxx}' OR '(All Column Values)' = '@{variable_name}{xxxxxxx}')

          With that kind of query the All Column Values = <variable> will behave as a 1=1 when you select All Column Values in the prompt.

          • 2. Re: OBIEE 11g: Presentation variable syntax in direct database request
            srini9148

            HI,

             

            Please provide correct syntax.

             

            Regards,

            Srinivas.

            • 3. Re: OBIEE 11g: Presentation variable syntax in direct database request
              Gianni Ceresa

              SELECT ... WHERE (your_column = '@{variable_name}{xxxxxxx}' OR '(All Column Values)' = '@{variable_name}{xxxxxxx}')

              Of course you need to replace all the parts in bold based on your query ...

              In the Direct DB request you can reference the value of the presentation variable by @{variable_name} , you can also define a default value (required in case the variable is a number to not produce an invalid query).

              As said earlier the "All Column Values" pass that value in the variable, so in the example you see how to manage it to get all the possible values.

              Anything else?

              (just close the topic by marking it as answered if it's the case and by flagging the correct answer to help other users looking for the same)