Stored Procedures in Impromptu 2

[badhri]

Hi,
I was evaluating Cognos Impromptu 5.0. I learnt that we can implement Stored Procedures in Impromptu 6.0 version. Can anyone help me in explaining the actual process involved in implementing this. Is there any other new features available in Cognos Impromptu 6.0 compared to Impromptu 5.0. Please notify me of the same too.

 

[CognosProfessional]

Product Version: 6.0 Operating System: PC
Description:How do Stored Procedures work in Impromptu 6.0?
Stored procedures can be called from within Impromptu 6.0.
The call would have he following syntax:
call <stored procedure name> (?prompt? IN, ?prompt2? OUT)
Stored procedures can return multiple records against select databases.
For additional information about stored procedures consult the Impromptu 6.0 Administrator's Guide. p. 135
Note: Oracle can only return one record at a time. (it does not permit datasets)

 

[badhri]

Can U just help me with in depth of implementing the stored procedures. I have only Imp V5.0 and tried to execute SP. Can I write some valuesx into DB using the SP and retrive some values at the same time

Thanx in Advance
Badhri

 

[rosibee]

Hello,

My name is Rosi, I was wondering if anyone could help me with Stored Procedures. I currently have a couple in MS-SQL7. The SP use dates to filter the information.

However, I cannot implement them because Impromptu does not read in SmallDatetime data types without the datetime-todate function.

Please help me as soon as poss -

thanks

rosi

 

[DGXGUY]

I am looking for an example of how to call an Oracle stored procedure that returns multiple rows. I also need a rough example of what that procedure code looks like. Is there any Cognos Documentation that gives an example of acessing Oracle stored procedures? Any help would be appreciated. I have several procedures written in packages that i would like to use in Cognos Web Reports. Thanks in advance.

 

[DGXGUY]

CognoProfessional -

I just want to clarify what you meant when you wrote &quot;Note: Oracle can only return one record at a time. (it does not permit datasets)&quot;. I have a package in Oracle with a Procedure in it that returns multiple rows. Ex. OUTPUT

Field 1 Field 2
Bob 17
Jim 18
Mary 21

I am looking to return these results to a Cognos report? Is this possible? If not, can you point me to some documentation confirming or denying this? I cannot find documentation on this anywhere and I need to know if it is possible to do something similiar to this. Any help would be greatly appreciated.

 

[griffindm]

I have worked with stored procedure calls from Impromptu on both Oracle and MS SQL for about three years. I have the stored procedures populate real reporting tables, so I have avoided the problems in the new release supporting a result set. Through Imp ver 5 the SP call [Execute] in the macro language worked relatively well (you sometimes had to add extra null values to passed variables i.e. FY+&quot;&quot;, lcInt+0). The &quot;new&quot; method (I used it occasionally in prior versions before it was fully supported) is to embed your SQL call in the SQL tab of Query|Profile on the menu. For SQL Server it works as advertised. For Oracle, I found I had to enclose the SP call in a SQL 'snippet' as it:

Call BEGIN SP_NAME('?Prompt1?','?Prompt2'); End;

This would work most of the time, but I found that the SP was not always getting the variable data correctly. On both platforms I wound up using an ODBC call via CognosScript.
i.e.:
Query1 = &quot;BEGIN USG_MOD_CLS('&quot;+sFyCd+&quot;',&quot;+Cstr(iPdNo)+&quot;,'&quot;+sLike+&quot;','&quot;+sAction+&quot;','&quot;+sModBy+&quot;'); END;&quot;
retcode = SQLExecQuery(connection,Query1)

This also does not handle returned OUTPUT parameters from the procedure, but I put the return data into a control table and read it via ODBC.

Hope this helps anyone else that has struggled with stored procedures in Impromptu.

Dave Griffin

 

[rahulpandey]

How to use Oracle Stored Procedures with Impromptu

To use Impromptu to pass a parameter to an Oracle Stored Procedure and have it pass a result set back to Impromptu for further reporting, the Stored Procedure must be part of an Oracle package that use objects as cursors to return the result set. Here is an example of an Oracle Package including a Stored Procedure. There are comments included to explain the structure.

create or replace package project_pk as /* Creates Package Header*/
type project_type is record( /* A record declaration is used to */
c1 projects.projectid %TYPE, /* provide a definition of a record */
c2 projects.projecttype %TYPE); /* that can be used by other variables*/
type project_type1 is ref cursor return project_type; /* Variable declaration */
procedure project_sp (tproj IN numeric, result1 in out project_type1); /* SP declaration */
end;
/
create or replace package body project_pk as /* Name of package body must be same as header */
procedure project_sp (tproj IN numeric, result1 in out project_type1) is /* SP Definition */
begin
open result1 for
select projects.projectid, projects.projecttype
from projects
where projects.projecttype=tproj;
end;
end;
/

/* To test this (interactively) in Oracle type: */
/* var c1 refcursor; The use of the variable is not needed through Impromptu */
/* execute project_pk.project_sp(4,:c1); Note PackageName.ProcedureName */
/* print c1; this will display the result returned to Impromptu */



In the Stored Procedure template in Impromptu, enter:

call PROJECT_PK.PROJECT_SP(?tproj? IN)

In Oracle, the name of the stored procedure must be upper case. The ?tproj? can be defined as a type in or report etc. prompt and provides a project type numeric value.

Note: there is no prompt for the 'result1' IN OUT parameter of the Stored Procedure

 

[griffindm]

ralhulpandry,

This is a repost of faq401-907 by DGXGUY. You can reference it by just including the pointer higlighted in the previous sentence (i.e. 'faq401-xxx'). When reposting other's materials please give credit where due.

Dave Griffin :-(

 

[asmaghe]

Hello,

I need to insert some information like user name, report parameters in an Oracle table when a user launches an Impromptu(V7) report. For that I would like to call an Oracle stored procedure from Impromptu after the parameters were entered in the prompt window. What are the different ways to do that ? and eventually what is the best way to do that ?

Thanks for your help,
Arnaud

 

[griffindm]

Arnaud,

The best way I know of to do this is to create a macro that connects to the Oracle database via ODBC and runs INSERT SQL to add rows to the table. Then attach the macro to the report, and it will run just before the report is launched by the end user.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[asmaghe]

Dave,

Thank you for your quick answer but I forgot to say in my previous post that my impromptu report has to run with Impromptu Web Report too. I think that it is not possible to run a macro with IWR. Is it right ? If yes, do you have another solution ?

Thanks in advance,
Arnaud

 

[griffindm]

From the audit files created from IWR, it looks like it can run a macro, though the documentation does not say it is supported.

If you are using IWR, why not just use the built in auditing?

HTH,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[asmaghe]

With the built in auditing, It looks impossible to get parameters you typed in the prompt window. You can only get the user name.

I will try with a macro.

Thanks,
Arnaud

 

[dm21]

Hi There,

I am new to stored procedures. I would like to know how you have been using it. What I understand from it is, you have to run the database procedure and save it as hotfile and create another report from that hotfile if you have to do any kind of formatting.

Is this the most common ways stored procedures are used?

I would appreciate any suggestions on this matter.

(I am using Impromptu 7.0 with sybase)

Thank you.

 

[griffindm]

Hi dm21,

Actually, the two methods I mentioned above (2 years ago now) let you report from stored procedure data two different ways. Using the stored procedure template in Impromptu returns the data from a procedure created with an OUTPUT parameter as a result set. The returned rows can be manipulated in the report as you wish. The second method, which is the method I use, launches the stored procedure seperately via a macro. This stored procedure populates a true reporting table, and reports are written from that source exactly as with any other table source.

Let me know if you have further questions. If you intend to use the first method, perhaps another here with more recent experience with that method can jump in and clarify things.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[Prasad678]

Hi Guys,

I have worked a bit (nearly 2 months) in StoredProc reports in COGNOS 7.

call Package.Proc((?prompt? IN). My storedProc returns a reference cursor.

The cursor throws a set of records.

This is not enough.

U can't create conditional formats for a storedproc reports. So?? Save it as d:\MyDir\Test.IMR

Create another report. in Report > Query, select HotFile in the Datasource selectbox.
Point it to d:\MyDir.

U can see Test.IMR in left side. Select the needed columns to the report & now U'll be able to create conditional formats in the new report.

This is HotFile kind of approach but instead of hotfile its a normal IMR file which gets latest data everytime.

I am not sure if this works in 6 or 6.5 versions.

Hope this helps.

Thanx,
Prasad.

 

[dm21]

Hi Dave and Prasad

First of all thank you for quick answer.

Dave, for your second approch where you call macro to populate real table, is it transperent to user or they have to run that macro in order to run the main report?

Prasad, for hot files I have the same question. Do user have to create the hot files first and run the main report?
How is the performance in hotfiles?

Thank you.

 

[griffindm]

dm21,

You could theoretically work it either way. I usually embed the report call in the macro that runs the proc. The macro will not call the report until the procedure has finished running. You could simply attach the macro calling the proc to the report and let the user execute it by running the report, but there may be timing issues with this method as to whether the report waits until the proc is finished before retrieving data or (as I fear) reports no data to the user while the proc is still running. I haven't tried it this way as most of my procedures run a cyclical process (monthly) that populates data for a number of reports that are run by other users once the data is current.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[Prasad678]

dm21,
According to me, Performance of storedproc reports are much faster since there are not much (almost no) client side processing except the conditional formats if any.

Moreover, I used .IMR file as the hotfile (not .IMS file) & so we get latest data by connecting to the Databse unlike the Hotfile which is like a local snapshot.

Prasad.