6 Replies Latest reply on Jan 19, 2017 5:04 PM by 879721

    OBIEE 12C - Request Variable issue with prompts

    879721

      Hi,

      In OBIEE 12.2.1.2, I'm updating a session variable 'MCHT_CONTENT_LANG' using a dashboard prompt (Language) via Request variable, and adding this session variable in the where clause of the logical table in the RPD to filter the table by language.

      On the Dashboard the analysis with columns from the logical table is filter correctly as the language is  changed/selected by the Language dashboard prompt.

      However if I build a dashboard prompt using a column from the same logical table the prompt values are not refreshed when the Language prompt is changed. Below is the logical SQL from analysis and Prompt.

       

      Anyone know if this is a bug? and is there a work around?

      NB:  I know you could add a SET VARIABLE...<session variable> in the prompt using SQL Results but if I use the SQL Results I loose the 'Limit By Values' option which I want to utilize also to filter prompt by other prompts.

      Also I want to keep the Language prompt outside of main Filters section due to location on the dashboard.

      Thanks

      Adrian

       

      Logical SQL from analysis

      SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/shared/CCA/_portal/RequestVarDb',SAW_DASHBOARD_PG='page 1',SAW_SRC_PATH='/shared/CCA/Analysis/AK_prod_test',MCHT_CONTENT_LANG='fr';SELECT
         0 s_0,
         "Order Line Item Metrics"."Product Name"."Language key" s_1,
         "Order Line Item Metrics"."Product Name"."N product id" s_2,
         "Order Line Item Metrics"."Product Name"."Product name" s_3
      FROM "Order Line Item Metrics"
      ORDER BY 3 ASC NULLS LAST, 4 ASC NULLS LAST, 2 ASC NULLS LAST

       

      Prompt Logical SQL  ('MCHT_CONTENT_LANG' is not set )

      SET VARIABLE QUERY_SRC_CD='ValuePrompt';SELECT "Product Name"."Product name" saw_0 FROM "Order Line Item Metrics" ORDER BY saw_0

      FETCH FIRST 65001 ROWS ONLY

        • 1. Re: OBIEE 12C - Request Variable issue with prompts
          Gianni Ceresa

          Prompts have a kind of "stronger" cache, the query to populate a prompt once it's fired it keeps the results somewhere and avoid running the query again.

          You are probably hitting this case (the logs in your session clearly show if your prompts are firing a new query or not).

           

          If you confirm you are hitting this case, knowing you want to keep your prompts as normal "column prompts" and not SQL based to use the "limit by" you would ideally need to also have another (as you don't want to move the main one there) a language column prompt (you can set the value by the variable) and use limit by in other prompts on this one (so adapt your model to be able to filter on a language column), this is supposed to give you a result similar to the usage of language in the WHERE clause.

          1 person found this helpful
          • 2. Re: OBIEE 12C - Request Variable issue with prompts
            879721

            Thanks Gianni.

            I'm pretty sure I'm not hitting the cache as I've changed the default of the session variable so I can see the prompt fire a new query with the new default.

            I see what your saying.  This is my plan B :-) , where I model the RPD so that I can "limit" the other prompts by the language prompt, however means would have to re-locate the language prompt into the same prompt section as we would only want one language prompt.  Plan B means more joins in RPD which I wanted to avoid.

            Do you know if the Dashboard prompts should work for the request variables?  I don't see a bug listed for this issue so it may be working as designed

            Adrian

            1 person found this helpful
            • 3. Re: OBIEE 12C - Request Variable issue with prompts
              Gianni Ceresa

              Your request variable act as a sessions variable: it exists, it's there, it's set, so it as a value. For an analysis, for a prompt or for any other query you send to OBIEE in the same session.

              So yes it must work for a prompt as well.

              A prompt takes its values from the same exact RPD as everything else.

              So if your "WHERE clause" is set on the logical table source the prompt uses then the variable is used.

              Maybe you are not going on the LTS with the where clause for your prompt?

              1 person found this helpful
              • 4. Re: OBIEE 12C - Request Variable issue with prompts
                879721

                Thanks.

                I have the where clause on the correct LTS.  The analysis is built with 3 columns from the same LT (product id, product name, language key) and the analysis works fine.  I'm using the product name from same table for the prompt.

                 

                I see this definition below -

                 

                " We can define Request Variables in any request to override the session variable, using the SET VARIABLE code. When we define a request variable for any request, then the scope and availability of that variable is within the same request. Outside of this request, the session variable will be having the actual value, not the overridden value."

                 

                so it works for the Analysis as its within the same request.  But for prompts, once you select the dropdown and the list of values appears this must be a separate request, outside the scope of request variable with new value, therefore it picks up the session variable default and why it doesn't work for prompts. For prompts you would need to use the SQL based and add the SET VARIABLE MCHT_CONTENT_LANG..... so that the variable is set for the scope of the prompt.

                 

                Adrian

                • 5. Re: OBIEE 12C - Request Variable issue with prompts
                  Gianni Ceresa

                  I maybe missed a point (reading too quickly): isn't your prompt setting the value of the request variable you use in the WHERE condition?

                  Because if you use the prompt to set a session/presentation variable which then set another variable in the analysis (with the SET VARIABLE syntax) and it's this new variable used in the WHERE then yes, your variable doesn't have the right value anywhere else than your analysis, so the prompt can't see it.

                   

                  I thought your prompt was setting the value of the variable you use in the WHERE clause directly, in this case it will work.

                  • 6. Re: OBIEE 12C - Request Variable issue with prompts
                    879721

                    Yes, the request variable/session variable set in the prompt is the same one used in the WHERE condition of the LTS.  So it should work then?

                    eemmmmmm..... I might log it as an Oracle bug and see what they say.