BO Report Using Stored Procedure: Multi Value Prompt Params?

building reports desktopintelligence
1

Hi All,

I have tried searching every where for an answer to this but I just can’t find it. So please be forgiving if I missed some obvious posting here that explains this all.

First my info:
BO Version: 6.5 SP2
Client: Developing on Full-Client, but need to report through InfoView Enhanced Doc View
Database: Oracle

Background:
A VERY LONG SQL statement, so long that BO can’t handle it as a Derived table, or as Freehand SQL. Believe me it is long and BO chokes on it. A DB view won’t give me the ability to parameterize the SQL as I need, so I turned to the stored procedure.

Requirement:
I need to be able to submit multi-value parameters to the stored procedure. Please understand that I do not mean multiple parameters, but rather multiple values for the same parameter; much the same way that regular prompts in BO allow you to select multiple values for a single prompt from an List of Values (LOV).

What I did:
I’ve been able to trick BO into letting me create a prompt with an LOV. I did this by adding a new data provider based on a universe that had a condition prompt with the same wording and case as the procedure parameter.

Problem:
When I select multiple values for the prompt in the LOV and submit them as usual, the stored procedure seems to only receive the first value in the parameter box. Is there anyway to get BO to send multiple values using an LOV to a stored procedure?

Smaller Requirement:
I also want to have nice looking prompts that are worded the same as others in our data mart. What I mean is that they can’t say “INPUT_DATE”, but that they should read more user friendly “Input Date (use format MMDDYYYY)”. How can I do this? Can I use free-hand SQL to collect the prompts and then pass them to the stored procedure? How does this work? Can the stored procedure still be a REF CURSOR stored procedure?

Any help or advice will be greatly appreciated. :smiley:

dajabon (BOB member since 2003-09-09)

2

Hi Dajabon,

maybe you could use a DB view to implemant your large SQL statement and use predefinied conditions or objects to parameterize and refine the SQL. You may face performance problem if you deal with a large amount of data. If it’s not the case, this may work!

Hope this will help.

ombo (BOB member since 2005-11-16)

3

Hi Dajabon

I have done soemthing similar but used the sdk to create a new prompt form on the report event “beforerefresh”. That way you can dynamically create the prompt text.

You can also allow it to build a list of values based upon your second dp.

When the “run” button is clicked on the form replace the semicolons (standard bo list delimiter) with colons. BO will then pass the entire string to your stored procedure. In the stored procedure do a for loop to loop through the string extracted each value between the colons.

Its a bit long winded but it will deliver eactly what you want.

I can probably dig up some code if you need it.

Cheers

Jeff

jeffH :uk: (BOB member since 2006-03-31)

4

Thanks for the response. A view just won’t meet my needs, the parameterization required won’t work with a view. I went ahead and figured out the stored procedure route and I run this in freehand SQL so that I can get prompts that are worded in a user-firendly way.

BEGIN
VCI_BizObj_Over_X(
@Prompt('As of Aging Date MMDDYYYY','A',,mono,free),
@Prompt('Select Aging Category for $ Over Reporting','A',{'Total Amt USD','Current AR Balance USD','1-30 DPD AR Balance USD','31-60 DPD AR Balance USD','61-90 DPD AR Balance USD','Over 30 DPD AR USD','Over 60 DPD AR USD','Over 90 DPD AR USD','Over 180 DPD AR USD','Over 365 DPD AR USD'},MONO,CONSTRAINED),
@Prompt('Enter Amount Over Which to Report (USD)','N',,MONO,FREE),
@Variable('Select One or More Countries (* for all)'),
@Prompt('Select One or More Regions (* for all)','A',{'Americas','Europe','Asia Pacific','*'},mono,free),
@Prompt('Select Customer Type (* for all)','A',{'Direct','Indirect','*'},MONO,FREE),
:cbo
);
END;

One side benefit to the stored procedure is that I was able to increase performance dramatically. The SP performs much better than even half of the SQL (which is a UNION so it is easy to half). Once I saw this, there was no turning back to anything else.

dajabon (BOB member since 2003-09-09)

5

Hi Dajabon,

Were you able to pass multi values as a parameter to stored procedure?

I am having the same problem.

I have a stored procedure

eval(P1)
begin
…
…
end

When the procedure is called as eval(@prompt(‘EnterP1’,‘A’,‘classname/objname’,Multi,constrained) ), BO sends the Parameter as (‘AA’,‘BB’) .

Oracle wouldn’t accept this as an input parameter. :h

Thanks in advance.

Joshua :india: (BOB member since 2006-01-30)

6

Hi Joshua,

No I ended up re-checking with the user and found out that only one country selection was needed. So I gave up on this.

However, I think it is still possible, you just needed to find a way to parse the multi values you will get from the @Prompt. I think I saw some suggestion on how to do this somewhere, but I don’t recall for sure. Good luck!

dajabon (BOB member since 2003-09-09)

7

I did it successfully by adding a Global Temp Table just to handle the multi prompts.

EXECUTE IMMEDIATE ( ‘TRUNCATE TABLE BO_BU_PROMPTLIST’ )

Each prompt value is 3 character long in my case. So I have the following logic to see if indeed there is more than one value… and considering the comma separator I insert each value as a single row in to the global temp table.

if length(company)= 3 then tcompany:=company ;
tsqlstmnt := ‘INSERT INTO BO_BU_PROMPTLIST values (’ || tcompany || ‘)’;
DBMS_OUTPUT.PUT_LINE(tsqlstmnt);
EXECUTE IMMEDIATE tsqlstmnt ;
else tcompany := ‘’’’ || REPLACE(company, ‘,’,’’’,’’’) || ‘’’’ ;
end if;

WHILE LENGTH(tCOMPANY) > 3 LOOP
BEGIN
IF instr(tcompany,’,’) <> 0 then
commapos := instr(tcompany,’,’);
DBMS_OUTPUT.PUT_LINE(‘comma position:’ ||commapos);
tsingleco := substr(tcompany,0,5);
DBMS_OUTPUT.PUT_LINE(‘single record:’ ||tsingleco);
tsqlstmnt := ‘INSERT INTO BO_BU_PROMPTLIST values (’ || tsingleco || ‘)’;
DBMS_OUTPUT.PUT_LINE(tsqlstmnt);
EXECUTE IMMEDIATE tsqlstmnt ;
else tsqlstmnt := ‘INSERT INTO BO_BU_PROMPTLIST values (’ || tcompany || ‘)’;
DBMS_OUTPUT.PUT_LINE(tsqlstmnt);
EXECUTE IMMEDIATE tsqlstmnt ;

END IF;

END;
tcompany := substr(tcompany,commapos+1,length(tcompany) - (commapos-1));
DBMS_OUTPUT.PUT_LINE(tcompany);
END LOOP;

Then in my actual SQL where I need the multi prompt value applied, I say something like this below:

AND ( DA.JCJOB_TABLE.JOB_COMP_CODE IN ( select distinct co from BO_BU_PROMPTLIST) )

Hope this helps.

Best wishes.

Queenstar (BOB member since 2008-01-15)