This content has been marked as final. Show 11 replies
Have a look here:1 person found this helpful
Where do SITE_NAME and CARD_SLOT exist in your physical model? The way I would do this is by creating a view that exposes SITE_NAME and CARD_SLOT so that you can join it in a another view or the physical layer. It looks like this is a hierarchy table so I would suggest you prepopulate a hierarchy table to avoid performance issues.
This query is from Business Object. I am trying to convert BO Report to OBIEE solution. The SITE_NAME and CARD_SLOT exists in PORTS table but I can not join to other outer tables. What do you mean by prepopulate a hier. table? The hier. table CIRC_PATH_ELEMENT is already populated. The INNER table PORTS having parameter is used only for feeding START BY clause. Please clarify
Thanks. I looked at your URL. I do have package. Just using procedure. So my initialization string (For stored proc in Physical Layer of RPD) is
But when I run request in Answer I get the error
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 900, message: ORA-00900: invalid SQL statement at OCI call OCIStmtExecute: exec demo.add_employee(105); . [nQSError: 17011] SQL statement execution failed. (HY000)
What should I put in initialization string in RPD?
Use a Function that calls the SP and select the function value link this:
SELECT your_function(param) FROM DUAL
Thanks. But I have to use the procedure (Function is not a choice). So what is the correct Syntax for excecuting proocedure with Parms in RPD?
Thanks in advance!
I don't think you can populate an Init Block from a SP I am afraid. You will have to use a function or a pipelined function if there is more than 1 row.
How do I write PL/SQL function that calls the pl/sql procedure with Parms?
That's probably a question for the PL/SQL forum although Google should help you. But you basically pass in the parameter of the function to the SP. Functions must return a value so you will need an output parameter on the SP to get the data out of the SP and then you can return it at the end of the function.
Check out here to the end of the article for the pipelined function.
But are you sure that OBI is the good tool for what you want.
Have you a fact table ? Which kind of report do you want with a flat bill of material ?
If you only want to run a report based on two parameters, I will work with BI Publisher.
If I assume that you don't need all bill of material, an other work around will be to store all your data in a flat table by procedure.
Thanks for info. I am making progress. The following in Initialization String of RPD Physical Layer for Data Source as Stored Proc worked.
BEGIN demo.add_employee('VALUEOF(NQ_SESSION.empid1)'); END;
But my demo.add_employee SP need OUT parm as well. How do I RETURN OUT PARM back to OBIEE so that I get the result set in Dashboard?