OBIEE 12C - Request Variable issue with prompts

Question

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

OracleUser_MUC6U · Answer

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.

Gianni Ceresa · Answer

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.

OracleUser_MUC6U · Answer

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