Stored Procedure Universe: Issue with Multi values for a prompt

[Former Member]

Hi,

I have created a universe using BO4 UDT based on a Oracle stored procedure. The prompt is accepting a single value and working fine, but when I change the promt selection to "Multi", It's throwing an error "Multiple query filters contain a prompt with the same text, but the prompts use a different operand type or number of values. (WIS 00015)".

On the BO forums, I found a similar issue, but no solution.

So, is it possible to have a multi selection for a Prompt of a BO 4 stored procedure universe? If not, please let me know if there is any alternative to achieve this.

Appreciate any advice !

Thanks,
Leela

[former_member185138]

Hi Leela,

We have resolved the issue by adding the @prompt definition in the value text box keeping 'use this value' option selected.

@Prompt('Enter the Duration','A','Crwdbo Crw Tp\N Desc',multi,free)

Where Crwdbo Crw Tp\N Desc is class name and N Desc is object in the class.

Below is the screenshot.

[Former Member]

Hi,

I am facing the same problem and used the @prompt syntax as suggested by you. It parses fine and the universe has no integrity check problems. But when I try to run the query in the webi report the prompt window doesnt open up at all. It just gives a glance of the prompt window and then dissppears and the refresh query tab is greyed out.  All the other reports are working fine but the report using the Stored procedure universe has this problem.

Did you face any similar kind of problem?

Thanks,

Nisha

[former_member185138]

Hi Nisha,

No I didnt face such issue.

May be this is the issue in the client.

Is it reproducing in the Launch pad or rich client both?

[Former Member]

this shows up in launch pad as well as rich client.

[former_member185138]

We are on 4.0 SP5.

May be upgrading the BO server and client will help you.

[Former Member]

Hi leela,

Did you create the store procedure universe selecting "Prompt me for a value" option to create prompts at universe layer, If so you can try and check if the @prompt parameter is showing "Mono" or "Multi".Your @ prompt syntax should refer to MULTI to pass multiple values.

@Prompt('ABC','A',,MULTI,FREE,'0',Persistent,'0')

Also check if you are using equal to or Inlist for assigning these multiple values to store proc variable.

Thanks

Mallik

[Former Member]

Hi Mallikarjun,

Yes, I did it with "Prompt me for a value" option and created a promt with Mono selection initially, then it was working. But when I changed it to 'Multi', it's throwing an error.

Also, In the backend stored procedure, inlist is used for assigning values.

Thanks,

Leela

[Former Member]

Hi Leela,

I understand you are running an existing report, before and after the change of prompt and facing this error from webi layer. Can you verify and answer the below questions.

Are you able to parse the prompt in the universe without errors? to confirm no parsing issues on universe side

Are you able to run a fresh new simple webi report on this universe with the prompt that throwing error and couple of objects in results pane? to confirm if its not an issue with the existing report

How many prompts are there in existing report with the same "prompt text" as the prompt that's throwing error message?

Thanks

Mallik

[Former Member]

Hi Mallik,

Yes, the prompt is parsed with 'Multi' value without any issue in the universe.

In my report / Stored procedure, there are 8 prompts, but the prompt text is different for all of them. To explore more on this error, I even created a very simple stored procedure with just one paramter and created a BO report on this. Then also, the issue is same. It's working with 'Mono' and giving the same error when I change it to 'Multi' as below.

@Prompt('PName','A',,Multi,FREE,Persistent,,User:1).

Also, the issue is same when I create a new report. So, the issue is not with the existing report. And it's clearly with the Prompt parameter value 'Multi'.

Thanks,

Leela

[Former Member]

Hi leela,

Try the below, sometimes Multi and Free do not go well together. Designer guide says "Use free if the user can enter a value, or select one from the list of values."

@Prompt('PName','A',,Multi,primary_key,Persistent,,User:1)

or

@Prompt('PName','A',,Multi,constrained ,Persistent,,User:1)

Thanks

Mallik

[Former Member]

Hi Mallik,

I tried changing the prompt defintion as you suggested, but no luck.

It's giving the same error "Multiple query filters contain a prompt with the same text, but the prompts use a different operand type or number of values. (WIS 00015)".

FYI, I am on BO 4 SP4.

Regards,

Leela

[Former Member]

Hi Leela,

Can you post the complete syntax of the prompt including left hand side of prompt, at least by masking the table name.

Also the variable logic used in stored procedure for this prompt.

Thanks

Mallik

[Former Member]

Hi Mallik,

Below are the syntaxes I tried with 'Multi' value parameter combination:

@Prompt('PName','A',,Multi,constrained ,Persistent,,User:1).

@Prompt('PName','A',,Multi,free ,Persistent,,User:1).

@Prompt('PName','A',,Multi,primary_key ,Persistent,,User:1).

@Prompt('PName','A',,Multi,constrained ,Persistent), but nothng worked.

For the variable of a stored procedure universe, there is no need to define left side to complete the condition. The prompt value is given for the Next execution of a stored procedure as a input parameter.

This prompt is defined for the input variable of the below stored procedure used in the Universe:

1)

create or replace
PROCEDURE PBO_test ( v_lname in varchar2, ResultSet IN OUT BO_TEST.catcurtyp) IS

BEGIN

prc_test(v_lname);  // which calls below stored proc.

OPEN resultset FOR SELECT lname from T_BO_TEST where lname in (v_lname);
 
END;

Which inturn calls another stored procedure to load the data into another table.

2)

create or replace
PROCEDURE prc_test (v_lname IN VARCHAR2)
AS
BEGIN

insert into T_BO_TEST select LNAME from T_BO_TEST WHERE LNAME in v_lname ;
   COMMIT;
  
END prc_test;

Actually this is a test stored procedure I have created to test the functionality with one paramter.

In real, we are calculating the forecast data using an algorithm in the stored procedure. The flow and the logic is working fine with single variable value selection(i.e, with 'Mono')

Please let me know if you need any other information.

Thanks,

Leela

[Former Member]

Leela

I think it might be something to do with store procedure, not being able to accept the mutiple values in the format passed from webi report, i.e value1;value2;value3 etc.

Try enabling free as option in prompt and then try manually entering multiple values with comma separator and see if it works.

Also, try executing store procedure from DB end with multiple values using semi colon ";" separator

Thanks

Mallik

[Former Member]

Hi Mallik,

With 'free' and 'multi' as values for parameters, it's not working and causing the same error as before.

@Prompt('PName','A',,Multi,FREE,Persistent,,User:1).

And when I changed the selection back to 'Mono', and given the values with comma separator while execution, it's treating the values as a single valued string, but not multiple values and hence no good result.

@Prompt('PName','A',,Mono,FREE,Persistent,,User:1).

Thanks,

Leela

[Former Member]

Hi leela,

No point of trying to pass multiple values with comma separator when its set to MONO as it treats that as one value.

How about store proc result on DB side when passed with values in format value1;value2;value3. Are you able to get the result or is it throwing error? If this throws error then your issue is with store proc variables receiving values in this format.

Thanks

Mallik

[Former Member]

If the store proc execution confirms the issue, then you can try and see if Replace(@Prompt('PName','A',,Multi,primary_key,Persistent,,User:1),';',',') works in assigning values to the stored proc variable in the compatible format.

Thanks

Mallik

[Former Member]

Hi Mallik,

Thank you somuch for spending your time on this!

I tried executing the stored procedure with multiple values separated by semi colon, then it didn't work. But the alternative we have from stored procedure is we can make it work if we can pass mutiple values as a single value sepatated by some delimiter from BO end.

Below is the procedure:

create or replace
PROCEDURE prc_test (v_lname IN VARCHAR2)
AS
BEGIN

execute immediate 'truncate table T_BO_TEST';

insert into T_BO_TEST(lname) values (v_lname);
   COMMIT;
  
END prc_test;

exec prc_test('India';'Germany') --- It didn't work

exec prc_test('India;Germany') - It inserted the value as a single string and when I checked with the database person, he said they will be able to handle the value separation logic with delimiter in the backend.

Now, we have to see if we can pass multiple values from BO end in the format ('India;Germany'), so that stored procedure treats it as a single value.

Please let me know if you have any better solution to acheive this.

Thanks,

Leela

[Former Member]

Hi,

Try to use different name for each prompt in BO. As per my analysis you are using same name for al lthe prompts. i.e sometimes as by passing single value and multi for some tohers .I think it might be the issue.

Thanks,

Suresh

[Former Member]

Hi Leela,

You can try create a dummy prompt at universe layer with same prompt text and static list of values (using {'a','b'} syntax ) as the one used for stored procedure and and enable it as multi value prompt to use it at report level.

The selected values for this prompt would be passed to both the prompts and stored proc would run for the expected values.

This is just an idea, try and see if it works.

Thanks

Mallik

[Former Member]

Hi,

I tried it with only a single value parameter/ Prompt in the BO universe. But it didn't work resulting the same error.

I understand that problem is from the stored procedure execution with multiple values. So, I think it's not directly acheivable with BO without SDK to pass multiple values as a single value to the backend stored procedure.

Please let me know if anyone has come across this or a similar issue.

Thanks,

Leela

[Former Member]

Hi Mallik,

Thanks for your idea! But, stored procedure universe doesn't allow to create any additional objects except the ones coming directly from the backend stored procedure.

Condition object icon is completely disabled and we can create objects only to make them use as LOVs, but not visible in the data provider while creating a query for the report.

Thanks,

Leela

[former_member185138]

Hi leela,

Any luck on this?

we are facing the same issue.

[Former Member]

Hi Leela,

Try to make your prompt to enter values manually (rather than selecting LOVs from an object) separated by comma and run the report.

Thanks,

Suresh Babu Aluri.

[Former Member]

Hi Suresh,

Thanks for the response! But the requirement demands to give an option for the user to select multiple values from LOV. Also, the user will not be able to enter the values manually without LOV selection as there are 'N' number of values for a parameter.

Is multiple selection not possible for a Stored procedure parameter? It is fine even if we get the values with ; as a delimiter. Then we will handle the values separation logic in the stored procedure itself.

Thanks,

Leela