Ability to use Dashboard prompt column presentation variable in another column prompt.

Racharla Arun Kumar

Organization Name (Required - If you are an Oracle Partner, please provide the organization you are logging the idea on behalf of):

OPEX Corporation

Description (Required):

Need the ability to use one dashboard prompt column presentation variable in another column within the same dashboard prompt.

Use Case and Business Need (Required):

As per Business requirement, we are trying to get all the subordinates list under a Sales VP/ Sales Manager in Sales Rep(Name) in a column as a list of values and restrict the values in Sales Rep(Name) with respect to Sales VP/ Sales Manager We tried to use "limit by" option between Manager of the employee and Name, but it fetches only the persons reporting directly. We are trying to fetch the full resource hierarchy.

Enhancement Request / Service Request:

SR 3-27519240351

ALOK SH-Oracle

Hi

You can achieved this functionality .

Please follow below steps.

Define the presentation variable in first column

Then in second column write the sql query and use the presentation variable like below

SELECT "Dim - Columnname" FROM "Demand Planning" where  "Dim - Location"."Country" in  (@{pv_country}['@']{VALUEOF(NQ_SESSION.session_var_name)})

Racharla Arun Kumar

Hi Alok,

I have tried the above same format which you have given, I am facing an issue. The SQL query I used was:

SELECT "Employee"."Employee Name" saw_0

FROM "Sales - CRM Resource"

WHERE

   AND ("Employee"."Manager of Employee" IN (@{manager} ['@'] {VALUEOF(NQ_SESSION.session_var_name) }))

Could you please let me know, if I am doing it right?

Thanks,

Arun

ALOK SH-Oracle

Hi Arun,

2 thing in query.

first remove "AND" after where and if you are using any session variable then pass the correct session variable name

in place of session_var_name .

if you are not using any session variable then simply pass any default value in place of VALUEOF(NQ_SESSION.session_var_name)

the final query -----

SELECT "Employee"."Employee Name" saw_0

FROM "Sales - CRM Resource"

WHERE

   ("Employee"."Manager of Employee" IN (@{manager} ['@'] {VALUEOF(NQ_SESSION.session_var_name) }))

Or

ELECT "Employee"."Employee Name" saw_0

FROM "Sales - CRM Resource"

WHERE

   ("Employee"."Manager of Employee" IN (@{manager} ['@'] {default_value }))

Marcelo Finkielsztein

@ALOK SH-Oracle ,

Hello. I am testing it as well and it is not working for me either. Same error message reported by Racharla.

Please, clarify the syntax of your "final query" SQL.

1. Do we need to include quotes and square brackets?
2. Could you post a query that does not contain any default values for the presentation variable?
3. Do you have a test instance where you have tested this with positive results?

Marcelo

ALOK SH-Oracle

@ Marcelo Finkielsztein

final query is :

-----------------------------------------------------------------------------------------------------------

SELECT "Dim - Ent Type"."Ent Name" FROM "Commercial Demand Planning" where  

"Dim - Ent Location"."Country" in  (@{pv_country}['@']{VALUEOF(NQ_SESSION.OPCONAME_COMM)})

----------------------------------------------------------------------------------------------------------------------------

where OPCONAME_COMM is the session variable name. based on requirement you can modified your query but

no quote should be removed.

if you do not have session variable the you can place any default value coming from first column or else you can put any value but the second column would not be loading data initially until you clicked on first column value.

SELECT "Dim - MTN Ent Type"."Ent Name" FROM "MTN Commercial Demand Planning" where "Fact - MTN Ent Inventory Detail"."Frequency Flg"='M' and "Dim - MTN Ent Type"."Ent Id" in (1,2,4) and "Dim - MTN Ent Location"."Country" in  (@{pv_country}['@']{ABC)})

where ABC is any value

Yes i have implemented in my project.