Skip to Content
avatar image
deba nayak

How to create an input parameter to pass another corresponding value?

Jun 03, 2017 at 10:32 AM | 231 Views

Hi Everyone,

I want to create an Input parameter in a way which will prompt the user to enter the value in some predefined description, but in back end it should pass the ID of that filed. I have a field call ERP System; the ERP system can be identified from ID or the name. Currently the users are entering the value using the ERP name. But to restrict the data from table used in view, based on ERP, I am joining using the ERP ID (as the fact and dimension tables doesn't have ERP name).

So my question is, if it is possible to accept ERP name in the front end and filter the table data based on ERP ID by passing the ERP ID internally?

I have WebI reports running on this view, so if I can achieve this from WebI also a fesible solution for me.

Note: I have one ERP Table, which stores the ERP Name and ERP ID column.

SAP HANA, enterprise extended edition
SAP BusinessObjects - Web Intelligence (WebI) | webi | business objects | input paramater | modelling | saphana
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Florian Pfeffer
    Jun 04, 2017 at 04:25 AM

    One option to reach this is to use a second (hidden) parameter which parameter type is "Derived from procedure/scalar function".

    So you have two paramters:
    • Your original parameter in which the user enters the ERP name.
    • A second (hidden) parameter which uses a stored procedure or scalar function to determine the ERP ID from the entered ERP name.

    Following a dummy example which uses a stored procedure which returns an "id" dependent on a specific text (in that example the values are hard coded but can of course be determined out of a table).

    Procedure (consider that the output parameter must also be a string, if another type is required, then the conversion has to be done in the view):

    PROCEDURE "MISC"."test.misc.procedures::P_DERIVED_P_MAP_VALUE" ( in i_text nvarchar(100), out e_id nvarchar(4) ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA <default_schema_name>
	READS SQL DATA AS
BEGIN
	if :i_text = 'test01' then
	  e_id := '1';
	elseif :i_text = 'test02' then
	  e_id := '2';
	elseif :i_text = 'test03' then
	  e_id := '3';
	else
	  e_id := '9999';
	end if;
END;

    ID Parameter:
    This is the (hidden) parameter which receives the ID determined by the procedure. You have to map your first "name" parameter to the input parameter of the procedure.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

    • deba nayak Florian Pfeffer
      Jul 22, 2017 at 02:34 PM

      Hi Florian,

      Sorry for replying late. The issue was really weird, when I created the same table in sys_bic schema then also it did not work. So I created a new table in its own schema and loaded the records from the existing schema, it worked. Though I could not figure out the reason of this.

      Regards,

      Deba