Stored Proceudre Report

[gandhids]

Hi,
I have to prepare a few stored procedure reports.Could you please tell me that for making the catalog,
i will fetch the metadata of all tables that will be used.Do i need to perform the join between them as anyway i am using the stored procedures.
Please revert.
And do i need to inclued those procedures in my catalog.

 

[Firecat1970]

Hello to create a report off a Stored Procedure, here is the step.

YOu probably need a dummy Catalog with just 1 small table which can has nothing to do with your stored procedure. So just chooose a small table.

Then open a new report.

On the menu, choose "Report->Query", then choose the "Profile" Tab, then click on the radio button "SQL", then click "Edit". Here you can type your stored procedure and prompts.

e.g.

call sp_test (?p_customer_no?,?p_start_dt?,?p_end_date?)

Of course, you have to define those prompt yourself.

Have fun.

 

[gandhids]

When i do the same,
i get following error.
Error number -9:

DMS-E-GENERAL, A general exception has occurred during operation 'execute'.
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ORION_ODS_BALANCES_CONTROL_NEW'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

[Firecat1970]

O... Sorry I haven't encountered that error yet. I am not sure if Oracle and MSSQL would make a difference. I am using MSSQL.

Can you try creating a much simpler stored procedure just for testing? say it is select things from just 1 table, and it only take 1 parameters. This way, you know if it is your SP, or it is Cognos which has this problem. Are you using 7.1 Version 2?

 

[griffindm]

It sounds like you are passing the wrong number of parameters to the stored procedure. Can you call the procedure from SQLPlus? If so, check that you are using the same number of parameters in Impromptu (as prompts) and that they are of the same type. You may need to use the Advanced button to set the prompt type more accurately than the simple prompt types used for normal reports.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[gandhids]

Hi
This is my procedure
PROCEDURE ORION_ODS_BALANCES_CONTROL_NEW
(p_in_time_key IN Number,
p_in_portfolio_key IN Number,
cnt OUT NUMBER
) IS
p_out_err varchar2(200);
BEGIN
SELECT COUNT(d.investment_key )
INTO cnt
FROM cre_portfolio_dim a,
portfolio_investment_fact c,
investment_dim d,
time_dim b
WHERE a.cre_portfolio_key= c.cre_portfolio_key
AND d.investment_key = c.investment_key
AND b.time_key = c.time_key
AND b.time_key = p_in_time_key
AND a.cre_portfolio_key= p_in_portfolio_key;

p_out_err := NULL;

EXCEPTION
WHEN NO_DATA_FOUND THEN
p_out_err := 'GEN_NDF' ;
RETURN;

WHEN OTHERS THEN
p_out_err := 'DB ERROR OTHERS' ;

RETURN;

END ORION_ODS_BALANCES_CONTROL_NEW;

In my impromptu report, it should show me the value of count for the combination of p_in_time_key and
p_in_portfolio_key .

In impromptu,i have as follows
call ORION_ODS_BALANCES_CONTROL_NEW(?p_in_time_key? IN ,?p_in_portfolio_key? IN ,?count? OUT)

question here is , whatever i am doing,is it correct as third parameter which is count and which is out parameter,how do i specify it.
I am using three type-in prompts for the parameters of a stored procedure.
Now when i paste the above line in imr report, the report is running but with no data.....Help me resolve the issue..
thanks for your earlier replys...

 

[gandhids]

In Impromptu stored procedure report, how do i specify the out paramater.If i don't specify the out parameter, it gives me the error.

call ORION_ODS_BALANCES_CONTROL_NEW(?p_in_time_key? IN ,
?p_in_portfolio_key? IN ,???????)


I am using type-in prompts for IN parameter, but how do i write OUT parameter.

 

[griffindm]

See faq401-907. You have to wrap the procedure in an Oracle Package and return the data as record types. The FAQ is basically a reprint of a Cognos Knowledgebase article on the same topic.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[gandhids]

Hi when i did as u said. i am getting following error.
Error number -9:

DMS-E-GENERAL, A general exception has occurred during operation 'execute'.
ORA-06550: line 1, column 7:
PLS-00201: identifier 'Pk_TEST.PR_GET_DTS_PORTFOLIO' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


here is my package.
CREATE OR REPLACE PACKAGE Pk_TEST AS
TYPE ref_pf1 is record(
c1 cre_portfolio_dim.CRE_PORTFOLIO_KEY%type,
c2 deal_dim.DEAL_KEY%type,
c3 investment_dim.INVESTMENT_KEY%type,
c4 property_dim.PROPERTY_KEY%type,
c5 time_dim.time_key%type);

TYPE ref_pf IS REF CURSOR return ref_pf1;

PROCEDURE pr_get_DTS_PORTFOLIO ( p_in_portfolio_id IN Number,
p_in_time_key IN Number,
p_out_err OUT VARCHAR2,
p_out_pf OUT ref_pf
);


END Pk_TEST;
/

CREATE OR REPLACE PACKAGE BODY Pk_TEST AS
--===============================================================================================================================
-- PROCEDURE: PR_GET_START_END_DATE
-- PURPOSE : THIS PROCEDURE RETURNS START AND END DATE OF THE PERIOD IN WHICH THE SUPPLIED P_IN_DT DATE FALLS. IF P_IN_DT IS NOT
-- WEEKEND DATE, P_OUT_DT RETURNS IMMEDIATE PRIOR WEEKEND DATE. START AND END DATE WILL BE BASED ON P_OUT_DT.
--===============================================================================================================================
PROCEDURE pr_get_DTS_PORTFOLIO(p_in_portfolio_id IN Number,
p_in_time_key IN Number,
p_out_err OUT VARCHAR2,
p_out_pf OUT ref_pf)
IS
BEGIN

OPEN p_out_pf FOR
SELECT CRE_PORTFOLIO_KEY,
DIF.DEAL_KEY,
PIF.INVESTMENT_KEY,
DPF.PROPERTY_KEY,
DIF.TIME_KEY
FROM PORTFOLIO_INVESTMENT_FACT PIF,
DEAL_INVESTMENT_FACT DIF,
PR_DEAL_PROP_FACT DPF,
INVESTMENT_PROPERTY_FACT IPF
WHERE PIF.CRE_PORTFOLIO_KEY = p_in_portfolio_id
AND PIF.TIME_KEY = p_in_time_key
AND PIF.INVESTMENT_KEY = DIF.INVESTMENT_KEY
AND DIF.DEAL_KEY = DPF.DEAL_KEY
AND DPF.PROPERTY_KEY = IPF.PROPERTY_KEY
AND DIF.TIME_KEY = PIF.TIME_KEY
AND DPF.TIME_KEY = PIF.TIME_KEY
AND IPF.TIME_KEY = PIF.TIME_KEY ;

p_out_err := NULL;

EXCEPTION
WHEN NO_DATA_FOUND THEN
p_out_err := 'GEN_NDF' ;
RETURN;

WHEN OTHERS THEN
p_out_err := 'DB ERROR OTHERS' ;

RETURN;

END pr_get_DTS_PORTFOLIO;

END Pk_TEST;
/


and here is what i have written in impromptu
call Pk_TEST.PR_GET_DTS_PORTFOLIO (?p_in_portfolio_id? , ?p_in_time_key? )



please tell me where i am doing wrong...
Thanks

 

[gandhids]

Hi,
I have 5 tables A,B,C,D,E
A
Investment_detail_ID

B
Investment_detail_ID
Investment_id
c

Investment_id
Deal_id

D
Deal_id

E
Deal_ID

on Joining the A,B,C,D I get some deal_ids.Now if I have to find out which ids of these result set are not there in table of e, how can join tables??Currently I have A and B joined on Investment_detail_id, B and C joined on Investment_id , c and d joined on deal_id, and now how shall i join d and e.If i join them on deal_id, i miss those deal_id which i get from the combination of A,B,C,D.Can outer join be the solution.
If so,How??which tables shall i perform outer joins on.My report will require the data fromtable A and E.
Thanks