create Dashboard Prompt

Karim Aboul Kassem

HI

please i need your help i need create Dashboard Prompt return 2 values

Ex: i have create report based on query "Create Direct Database Request" and i have create static variable on RPD file and put this variable on my code, so this variable must have a customer number, i need to create Dashboard Prompt contains two columns one for customer name and the other one for customer number, when the our user chose the customer name automatic the  Dashboard Prompt return the customer number in my variable to run my code

thanks    

Gianni Ceresa

Hi,

The way you describe things all I get is that you are bypassing OBIEE completely but now expect OBIEE to solve your problem. Things doesn't happen magically ...

Do you want to use a direct database request bypassing everything OBIEE does? Fine, so adapt your query to work based on the name and use that variable instead of the number. Problem solved.

On the other hand if you use OBIEE for what it really is everything would be modelled right in the RPD and you would not need a direct database request at all. So your analysis based on a business model would works perfectly fine with the name as input because the model will do all the required joins and logic.

[Deleted User]

+1 to Gianni

If you are bypassing the tool and not using its functionality  - what's the point of asking it do work as intended for you? If you push your car by hand you also can't really complain about the engine not helping.

Karim Aboul Kassem

HI

this is my need

1- i have create report "Create Direct Database Request" and this is my code on it

---***********

declare @temp table

(

SORTID INT,

ROWORDER INT,

LABEL varchar(255),

VALUE INT,

DATE DATETIME,

groupid Nvarchar(50),

groupnamee Nvarchar(50),

CUSTOMERTYPE Nvarchar(50),

CUSTOMERTYPEDESCRIPTION Nvarchar(50),

DISPLAYORDER Nvarchar(50)

);

DECLARE @return_value int

INSERT @temp EXEC @return_value = [dbo].[SP_BI_DW_KPIReports_Daily]

@FromDay = N'@{SBUZZ_FROM_DATE_V}{Direct}',

@ToDay = N'@{SBUZZ_TO_DATE_V}{Direct}',

@FromSalesman = NULL,

@ToSalesman = NULL,

@FromSupervisor = NULL,

@ToSupervisor = NULL,

@FromSalesmanCategory = NULL,

@ToSalesmanCategory = NULL,

@FromRouteID = NULL,

@ToRouteID = NULL,

@RTM = NULL,

@FromCustomerCategory = NULL,

@ToCustomerCategory = NULL,

@FromBUID = N'1AXAX23',

@ToBUID = N'1AXAX23',

@BUID = N'@{SBUZZ_BUID_V}{Direct}',

@Grouping = N'@{SBUZZ_GROUPING_V}{Direct}', ----********* MY NEED

@ShowMoreFields1 = NULL

select

SORTID ,

ROWORDER ,

LABEL ,

(CASE WHEN VALUE IS NULL THEN 0 ELSE VALUE END) VALUE ,

DATE ,

groupid ,

groupnamee ,

CUSTOMERTYPE ,

CUSTOMERTYPEDESCRIPTION ,

(CASE WHEN DISPLAYORDER IS NULL THEN '0' ELSE DISPLAYORDER END)DISPLAYORDER

from @temp ORDER BY DISPLAYORDER ASC ;

-----------------*********************

and this code it's working fine, but if you looking on this line (---- MY NEED) this variable has been created on RPD, and must have an ID, So i need create dashboard Prompt have on two columns one for description and the other one for ID (Ex: if the user chose any description, by automatic the ID for this description return on this variable to return on my code ), and i need this ID return on this variable

thanks

[Deleted User]

@1753723 so contextless, reflectionless solution:

Variable prompts with presentation variables which you then capture in the direcr db request:

https://gerardnico.com/wiki/dat/obiee/obips/variable_prompt

https://gerardnico.com/wiki/dat/obiee/obips/presentation_variable

https://gerardnico.com/wiki/dat/obiee/obips/direct_database_request