Topic
  • 19 replies
  • Latest Post - ‏2014-01-27T22:02:28Z by venkat_Jey
igorM
igorM
302 Posts

Pinned topic How to pass value to stored procedure from Report Studio and display value

‏2012-02-15T13:42:02Z |
Hi,
In Cognos v8.4.1 Report Studio report I would like to insert some value in input box and pass this argument to DB2 SQL stored procedure that will insert the value to the DB2 database. Then I would like to display this inserted value in List report.

in db2 v9.7 on Windows I have created stored procedure

CREATE TABLE ADMIN.TAB (ID INTEGER)@

CREATE PROCEDURE ADMIN.SP
(
IN INPUT_PARAMETER INTEGER
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT ID FROM ADMIN.TAB;
INSERT INTO ADMIN.TAB (ID) VALUES (INPUT_PARAMETER);
OPEN CURSOR1;
END @

In Cognos BI v8.4.1 Framework Manager I did: 1. File | New and enter project name e.g. sp 2. 
"Select Language" dialog 
"English" and OK button. 3. 
"Data Source" and Next. 4. 
"SAMPLE_DB2" (my db2 database connection) and Next. 5. Clicked on plus at ADMIN schema and plus at Procedures and checked the 
"SP" stored procedure, Next button, Import button, Finish button. 6. In 
"Project Viewer" click on plus at SAMPLE_DB2 namespace. 7. 
"SP" stored procedure is listed. Right mouse click on it and 
"Edit Definition". 8. 
"Query Subject Definition - SP" window opens. On Definition tab at bottom click on three dots at INPUT_PARAMETER argument in Value column. 9. 
"Edit Argument" window opens. Click on 
"Insert Macro" button. 10. 
"Macro Editor" opens in Macro definition type in:

#prompt('INPUT_PARAMETER')#
{code}
11. Click OK button two times to close two sub-dialogs. Now in "Query Subject Definition - SP" window click on Test tab.
12. "Prompt Values" window opens in first row in Value column type in e.g. 1 and click on OK button.
13. Go to DB2 Command Window and type in "select * from admin.tab" and number 1 is displayed, so stored procedure is working fine in Framework Manager.
14. Click on OK button (in Framework Manager) to close "Query Subject Definition - SP" window.
15. In "Project Viewer" window click on plus at "Data Sources" and click on displayed data source "SAMPLE_DB2" in my case.
16. In Properties window (at the middle bottom window) in Properties tab change Query Processing setting from Database Only to Limited Local.
17. In Project Viewer window right click on Packages and Create | Package.
18. In "Create Package" window in Name field type in package name e.g. sp and click Next button, Next button and Finish button.
20. Package is successfully created. On Question 'open the Publish Package wizard' click on Yes button.
21. Uncheck "Enable model versioning" and Next button.
22. User Access, just leave default and Next button and Publish button.
23. Package successfully published click on Finish.

Now open browser (e.g. Firefox):
1. Type in URL of Cognos BI and login.
2. In Cognos Connection select Launch (top right) and select Report Studio.
3. Select the SP package.
4. Create new report and select Blank.
5. Blank report is started.
6. From Insertable Objects window select Toolbox (at the bottom of window).
7. Scroll-down and drag Text Box Prompt to the canvas.
8. Prompt Wizard window opens. In "Create a new parameter" field type in INPUT_PARAMETER and Finish button.
9. Drag Prompt Button to the canvas.
10. Click on Button on canvas and from Properties window change General | Type from Next to Finish.
11. From menu select Run | Run Report - HTML.
12. Report is displayed. In input-box type in 2 and click on Finish button.
13. Finish button is displayed as clicked. No info or error returned.
14. Go to DB2 Command Window and type in "select * from admin.tab" only value 1 is displayed (from Framework test), but I expected to see also number 2.

For test I have also added List report and tried to display the inserted value, but no data is displayed.

Can you help me out. What am I doing wrong?
Regards
Updated on 2012-10-11T23:08:06Z at 2012-10-11T23:08:06Z by venkat_Jey
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-15T21:00:03Z  
    Are you trying to write a record in a database when you run the report in report studio?
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-15T21:36:19Z  
    I don't know much about DB2 stored procedures but there are a few things that you should check for the procedure to work in your report:
    • Is the proc actually returning a record set as a result? If it does not then the proc would show up as a "Data Modification" stored procedure in FM and it wouldn't be available for reporting (i.e. the stored procedure object would not be visible in the package metadata when you are in Report Studio or Query Studio).
    • Is the database connection isolation level set to create a transaction which will allow writes to be performed? Cognos reports are typically read-only requests so the transaction being set for the read may interfere with the transaction level required to write to the table.
    • In the steps to create your report I did not see any action to include any reference to the stored procedure query subject on the report layout. In order to actually call the procedure you will have to have a query container on the layout which is going to retrieve data from the stored procedure result set. This could be as simple as a list which has a single column containing one of the columns returned from the stored procedure.
  • igorM
    igorM
    302 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-16T07:04:05Z  
    I don't know much about DB2 stored procedures but there are a few things that you should check for the procedure to work in your report:
    • Is the proc actually returning a record set as a result? If it does not then the proc would show up as a "Data Modification" stored procedure in FM and it wouldn't be available for reporting (i.e. the stored procedure object would not be visible in the package metadata when you are in Report Studio or Query Studio).
    • Is the database connection isolation level set to create a transaction which will allow writes to be performed? Cognos reports are typically read-only requests so the transaction being set for the read may interfere with the transaction level required to write to the table.
    • In the steps to create your report I did not see any action to include any reference to the stored procedure query subject on the report layout. In order to actually call the procedure you will have to have a query container on the layout which is going to retrieve data from the stored procedure result set. This could be as simple as a list which has a single column containing one of the columns returned from the stored procedure.
    @CAP10:
    Short answer: yes. Longer answer: I created Text Box Prompt in Report Studio. End-user should enter some value in input box and click on Finish button. This action should run a stored procedure with typed-in parameter that becomes an argument in stored procedure. Stored procedure inserts the value in DB2 database and then executes 'select' statement to retrieve all of the data from database.
    @Phil.W:
    at 1) Yes. Stored procedure returns result set. I tried to write a stored procedure without result set and Framework Manager returns an error that result set is missing. I can see SP object in Report Studio.

    at 2) Yes. I default setting is "cursor stability" and it should be fine. I can run a stored procedure from DB2 Command Window and I can also write within Framework Manager.

    at 3) Yes. I did this. I added list report to the canvas and added ID from ADMIN.TAB table. I get the same result: no data is inserted into table and some random data displayed in List (can't figure out what is this value in List displayed - some nonsense - definitively not a value from ADMIN.TAB table).
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-16T10:49:37Z  
    • igorM
    • ‏2012-02-16T07:04:05Z
    @CAP10:
    Short answer: yes. Longer answer: I created Text Box Prompt in Report Studio. End-user should enter some value in input box and click on Finish button. This action should run a stored procedure with typed-in parameter that becomes an argument in stored procedure. Stored procedure inserts the value in DB2 database and then executes 'select' statement to retrieve all of the data from database.
    @Phil.W:
    at 1) Yes. Stored procedure returns result set. I tried to write a stored procedure without result set and Framework Manager returns an error that result set is missing. I can see SP object in Report Studio.

    at 2) Yes. I default setting is "cursor stability" and it should be fine. I can run a stored procedure from DB2 Command Window and I can also write within Framework Manager.

    at 3) Yes. I did this. I added list report to the canvas and added ID from ADMIN.TAB table. I get the same result: no data is inserted into table and some random data displayed in List (can't figure out what is this value in List displayed - some nonsense - definitively not a value from ADMIN.TAB table).
    The list in the report must query the stored procedure result, not the separate table where the records are to be inserted. If you do not reference the stored procedure in the layout of the report then it will not be called. If you are putting the result set of the stored procedure in the report layout then it would be useful to enable a trace of the database requests to determine what is being passed to the database for the procedure call and what transaction level is actually being used for this call.
  • igorM
    igorM
    302 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-16T14:03:31Z  
    The list in the report must query the stored procedure result, not the separate table where the records are to be inserted. If you do not reference the stored procedure in the layout of the report then it will not be called. If you are putting the result set of the stored procedure in the report layout then it would be useful to enable a trace of the database requests to determine what is being passed to the database for the procedure call and what transaction level is actually being used for this call.
    Hi,
    Now I have created Input Box Prompt, Button Prompt and List report and drag-drop stored procedure member from Insertable Objects into list report. Run a report and type in a value and press Finish button.

    I started DB2 Command Window and executed:
    select * from admin.tab --> Returns no data and only displays committed data (uncommitted data not displayed)
    select * from admin.tab with ur --> Returns inserted value by Cognos report! Displays uncommitted data.

    I have checked data source isolation level and for DB2 there are the following levels:
    • Cursor stability,
    • Phantom read protection,
    • Read committed,
    • Read uncommitted,
    • Reproducible read,
    • Serializable.
    By default in Cognos data source isolation level settings is "Use the default object gateway" - which should be a "cursor stability", but just to make sure I have created new data source and selected 'cursor stability' option, but the same problem appears when I re-run a report (new report to this new data source).

    Any idea what else could be wrong?
    Regards
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-16T20:33:36Z  
    • igorM
    • ‏2012-02-16T14:03:31Z
    Hi,
    Now I have created Input Box Prompt, Button Prompt and List report and drag-drop stored procedure member from Insertable Objects into list report. Run a report and type in a value and press Finish button.

    I started DB2 Command Window and executed:
    select * from admin.tab --> Returns no data and only displays committed data (uncommitted data not displayed)
    select * from admin.tab with ur --> Returns inserted value by Cognos report! Displays uncommitted data.

    I have checked data source isolation level and for DB2 there are the following levels:
    • Cursor stability,
    • Phantom read protection,
    • Read committed,
    • Read uncommitted,
    • Reproducible read,
    • Serializable.
    By default in Cognos data source isolation level settings is "Use the default object gateway" - which should be a "cursor stability", but just to make sure I have created new data source and selected 'cursor stability' option, but the same problem appears when I re-run a report (new report to this new data source).

    Any idea what else could be wrong?
    Regards
    If you are putting the result set of the stored procedure in the report layout then it would be useful to enable a trace of the database requests to determine what is being passed to the database for the procedure call and what transaction level is actually being used for this call.
  • igorM
    igorM
    302 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-17T08:01:00Z  
    If you are putting the result set of the stored procedure in the report layout then it would be useful to enable a trace of the database requests to determine what is being passed to the database for the procedure call and what transaction level is actually being used for this call.
    Hi,
    I have solved the problem. There should be COMMIT explicitly written in stored procedure to commit 'insert' statement.

    CREATE PROCEDURE ADMIN.SP
    (
    IN INPUT_PARAMETER INTEGER
    )
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT ID FROM ADMIN.TAB;
    INSERT INTO ADMIN.TAB (ID) VALUES (INPUT_PARAMETER);
    COMMIT;
    OPEN CURSOR1;
    END @

    Regards
  • igorM
    igorM
    302 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-17T08:10:08Z  
    • igorM
    • ‏2012-02-17T08:01:00Z
    Hi,
    I have solved the problem. There should be COMMIT explicitly written in stored procedure to commit 'insert' statement.

    CREATE PROCEDURE ADMIN.SP
    (
    IN INPUT_PARAMETER INTEGER
    )
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT ID FROM ADMIN.TAB;
    INSERT INTO ADMIN.TAB (ID) VALUES (INPUT_PARAMETER);
    COMMIT;
    OPEN CURSOR1;
    END @

    Regards
    Hi,
    one more question. When I execute report a prompt page opens. I can enter in any value and it does not have any effect (no rows inserted into database). Then report is display where I enter in a value and press Finish button, this action has an effect to insert row into table. So in this case prompt page is redundant. How to get rid of this prompt page? See attachment.
    Regards
  • igorM
    igorM
    302 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-02-17T10:14:19Z  
    • igorM
    • ‏2012-02-17T08:10:08Z
    Hi,
    one more question. When I execute report a prompt page opens. I can enter in any value and it does not have any effect (no rows inserted into database). Then report is display where I enter in a value and press Finish button, this action has an effect to insert row into table. So in this case prompt page is redundant. How to get rid of this prompt page? See attachment.
    Regards
    Hi,
    I have also solve the problem with prompt page. I clicked on Input Box Prompt and from Properties windows set the Default Selection property to some value.

    @Phil.W, thanks a lot for helping me out.
    Regards
  • mightycpa
    mightycpa
    6 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-08-08T17:04:30Z  
    Hi Igor,

    I am trying to do the same thing that you did, but I'm having problems with the return value, and putting it into Cognos report. I don't really need it in Cognos report, but I guess that is what "binds" the variables to the SP. Otherwise, I can't imagine how Cognos knows where to pass the input.

    My SP appears below:

    
    USE [IMAPSStg] GO 
    /****** Object:  StoredProcedure [dbo].[XX_CERIS_LOAD_STEP1_SP]    Script Date: 08/07/2012 14:15:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   ALTER PROCEDURE [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] ( @in_COGNOS_01 varchar(50), @out_COGNOS_result INT OUTPUT )   AS   
    /************************************************************************************************ Procedure Name : XX_R22_APEX_LOAD_TRANSFORM_01 Created By              : GEORGE Description    : Loads one record into XX_R22_APEX_TRANSFORM_01 table, returns table count after load Date                     : 2012-08-06 Notes                      : Prerequisites : Parameter(s)  : Input         : @in_COGNOS_01, looks like this: '99:14:12345:JELLO' - the colon is a delimiter Output         : @out_COGNOS_result, it is a count Tables Updated      : Version               : 1.0 ************************************************************************************************ Date               Modified By                   Description of change ----------   -------------                ------------------------ 2012-06-26   KM                                        Created Initial Version ***********************************************************************************************/   BEGIN   DECLARE     @SQLServer_error_code Integer DECLARE   @LongSentence VARCHAR(MAX) DECLARE @FindSubString VARCHAR(2)   SET @FindSubString = 
    ':'   -- ****************************************** INSERT A RECORD *************************************************   INSERT INTO dbo.XX_R22_APEX_GL_TRANSFORM_01 (FIND,START,LENGTH,REPLACE) VALUES( SUBSTRING ( @in_COGNOS_01, 1, CHARINDEX ( 
    ':', @in_COGNOS_01 )-1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1, CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1) - CHARINDEX ( 
    ':', @in_COGNOS_01 ) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1)+1, CHARINDEX ( 
    ':', @in_COGNOS_01,CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1)+1 ) - CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01,CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1)+1 )+1, LEN(@in_COGNOS_01)) );   -- ************************************* CHECK FOR ERROR ************************************************   SELECT @SQLSERVER_ERROR_CODE = @@ERROR   -- ************************************ COMMIT THE INSERT ***********************************************   COMMIT   -- ******************************* COUNT THE RECORDS FOR OUTPUT   ******************************************   --  (FIND,START,LENGTH,REPLACE) SELECT @out_COGNOS_result = count(*) FROM dbo.XX_R22_APEX_GL_TRANSFORM_01     END
    

    in Cognos, I have created the package, and it inserts code correctly, even though it complains about record counts in a dialog box. However, the record count is not returned in the package. I must be missing something there, I've attached a screenshot of everything that I think is relevant. Do I need a macro for the output?

    In Report Studio, there is nothing to query for insert into the report. I have created my prompt, with the correct name, but when the report executes, nothing happens. Where you were able to display ID, I cannot display anything. I've included some screenshots in the attachment too.

    Igor, I wonder if you can shed some light... what am I missing, what do I need to do?

    Thanks

    George
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-08-08T20:59:11Z  
    • mightycpa
    • ‏2012-08-08T17:04:30Z
    Hi Igor,

    I am trying to do the same thing that you did, but I'm having problems with the return value, and putting it into Cognos report. I don't really need it in Cognos report, but I guess that is what "binds" the variables to the SP. Otherwise, I can't imagine how Cognos knows where to pass the input.

    My SP appears below:

    <pre class="jive-pre"> USE [IMAPSStg] GO /****** Object: StoredProcedure [dbo].[XX_CERIS_LOAD_STEP1_SP] Script Date: 08/07/2012 14:15:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] ( @in_COGNOS_01 varchar(50), @out_COGNOS_result INT OUTPUT ) AS /************************************************************************************************ Procedure Name : XX_R22_APEX_LOAD_TRANSFORM_01 Created By : GEORGE Description : Loads one record into XX_R22_APEX_TRANSFORM_01 table, returns table count after load Date : 2012-08-06 Notes : Prerequisites : Parameter(s) : Input : @in_COGNOS_01, looks like this: '99:14:12345:JELLO' - the colon is a delimiter Output : @out_COGNOS_result, it is a count Tables Updated : Version : 1.0 ************************************************************************************************ Date Modified By Description of change ---------- ------------- ------------------------ 2012-06-26 KM Created Initial Version ***********************************************************************************************/ BEGIN DECLARE @SQLServer_error_code Integer DECLARE @LongSentence VARCHAR(MAX) DECLARE @FindSubString VARCHAR(2) SET @FindSubString = ':' -- ****************************************** INSERT A RECORD ************************************************* INSERT INTO dbo.XX_R22_APEX_GL_TRANSFORM_01 (FIND,START,LENGTH,REPLACE) VALUES( SUBSTRING ( @in_COGNOS_01, 1, CHARINDEX ( ':', @in_COGNOS_01 )-1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1, CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1) - CHARINDEX ( ':', @in_COGNOS_01 ) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1)+1, CHARINDEX ( ':', @in_COGNOS_01,CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1)+1 ) - CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01,CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1)+1 )+1, LEN(@in_COGNOS_01)) ); -- ************************************* CHECK FOR ERROR ************************************************ SELECT @SQLSERVER_ERROR_CODE = @@ERROR -- ************************************ COMMIT THE INSERT *********************************************** COMMIT -- ******************************* COUNT THE RECORDS FOR OUTPUT ****************************************** -- (FIND,START,LENGTH,REPLACE) SELECT @out_COGNOS_result = count(*) FROM dbo.XX_R22_APEX_GL_TRANSFORM_01 END </pre>
    in Cognos, I have created the package, and it inserts code correctly, even though it complains about record counts in a dialog box. However, the record count is not returned in the package. I must be missing something there, I've attached a screenshot of everything that I think is relevant. Do I need a macro for the output?

    In Report Studio, there is nothing to query for insert into the report. I have created my prompt, with the correct name, but when the report executes, nothing happens. Where you were able to display ID, I cannot display anything. I've included some screenshots in the attachment too.

    Igor, I wonder if you can shed some light... what am I missing, what do I need to do?

    Thanks

    George
    mightycpa, your stored procedure is returning an INT in an output parameter. Cognos is looking for a record set to come back from a stored procedure (ie SELECT X FROM Y). If a record set isn't available then the stored procedure is classified as a "Data Modification" stored procedure and it will only be available in Event Studio. If you want the stored procedure count result to be available in Report Studio then you need to change the stored procedure to return a record set so that it is not classified as a "Data Modification" stored procedure. Additional information on these topics can be found in the FM User Guide.
  • mightycpa
    mightycpa
    6 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-08-09T01:47:07Z  
    mightycpa, your stored procedure is returning an INT in an output parameter. Cognos is looking for a record set to come back from a stored procedure (ie SELECT X FROM Y). If a record set isn't available then the stored procedure is classified as a "Data Modification" stored procedure and it will only be available in Event Studio. If you want the stored procedure count result to be available in Report Studio then you need to change the stored procedure to return a record set so that it is not classified as a "Data Modification" stored procedure. Additional information on these topics can be found in the FM User Guide.
    Hi Phil, thanks for your reply. I still have a problem, but I've made a lot of progress. I'm stymied at what seems to be the very end.

    I see that you've written the advice you gave me before, and believe me, I did read through the documentation pretty well. My problem ended up being not so much Cognos ignorance as my SQL Server ignorance. That said, the Cognos documentation could be a lot better, and the examples much better illustrated and explained.

    After I got some help from a co-worker, he explained to me that the "result set" that Cognos wants is only available as the first query in the SP. Move it to after the INSERT statement, and it won't return anything. This makes for an interesting problem. If I query first, then insert, it seems to me that my record set is going to be useless at best, misleading at worst. Nobody wants a report that shows "the data you had before you added another record just now." So, this is a curious choice of how to do things, by Microsoft, and a questionable choice of which functionality to choose by Cognos (record set vs. output parameter).

    The INT you mention took me a while to understand. The record set could contain only INT, but that's not the point. An INT result of

    14

    is different than a result set that looks like

    CNT
    ---
    14

    Duh. That's the sound of me catching on.

    Here is the new and improved Stored Procedure:

    
    USE [IMAPSStg] GO 
    /****** Object:  StoredProcedure [dbo].[XX_CERIS_LOAD_STEP1_SP]    Script Date: 08/07/2012 14:15:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   DROP PROCEDURE [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01]   GO     CREATE PROCEDURE [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] @in_COGNOS_01 varchar(50) --, @out_COGNOS_result INT OUTPUT   AS   
    /************************************************************************************************ Procedure Name      : XX_R22_APEX_LOAD_TRANSFORM_01 Created By              : GEORGE ALVAREZ Description    : Loads XX_R22_APEX_TRANSFORM_01 table, or disappears on failure Date                   : 2012-06-26 Notes                      : Prerequisites : Parameter(s)  : Input         : Output                : Error Code and Error Description Tables Updated       : Version               : 1.0 ************************************************************************************************ Date               Modified By                   Description of change ----------   -------------                ------------------------ 2012-08-06   GEA                                       Created Initial Version ***********************************************************************************************/   -- BEGIN   DECLARE  @SQLServer_error_code Integer DECLARE   @LongSentence VARCHAR(MAX) DECLARE @FindSubString VARCHAR(2)     SET @FindSubString = 
    ':'   -- ****************************  COGNOS NEEDS A RETURN DATASET *******************************   -- FIRST TYPE OF OUTPUT THAT SP CAN PROVIDE -- and the first query in the SP is where these have to be located.   --  (FIND,START,LENGTH,REPLACE) SELECT FIND, START, LENGTH, REPLACE FROM dbo.XX_R22_APEX_GL_TRANSFORM_01   -- *********************************  THEN THE INSERT  ****************************************   INSERT INTO dbo.XX_R22_APEX_GL_TRANSFORM_01 (FIND,START,LENGTH,REPLACE) VALUES( SUBSTRING ( @in_COGNOS_01, 1, CHARINDEX ( 
    ':', @in_COGNOS_01 )-1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1, CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1) - CHARINDEX ( 
    ':', @in_COGNOS_01 ) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1)+1, CHARINDEX ( 
    ':', @in_COGNOS_01,CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1)+1 ) - CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01,CHARINDEX ( 
    ':', @in_COGNOS_01, CHARINDEX ( 
    ':', @in_COGNOS_01 )+1)+1 )+1, LEN(@in_COGNOS_01)) )   -- SELECT @SQLSERVER_ERROR_CODE = @@ERROR   -- COMMIT   
    /* ******************     COGNOS DOES NOT SUPPORT OUTPUT PARAMETERS *********************** -- SECOND TYPE OF OUTPUT THAT SP CAN PROVIDE -- SO SOMETHING LIKE THIS DOESN'T WORK FOR COGNOS -- YOU CAN'T EVEN INCLUDE IT --  (FIND,START,LENGTH,REPLACE) SELECT @out_COGNOS_result = FIND FROM dbo.XX_R22_APEX_GL_TRANSFORM_01 *********************************************************************************************/     
    /* ******************    COGNOS DOES NOT SUPPORT RETURN VALUE *********************** -- THIRD TYPE OF OUTPUT THAT SP CAN PROVIDE -- SO SOMETHING LIKE THIS DOESN'T WORK FOR COGNOS (NOTICE THE PARENS ... RETURN ( SQL GOES HERE ) -- YOU CAN'T EVEN INCLUDE IT RETURN (SELECT MAX(START) FROM dbo.XX_R22_APEX_GL_TRANSFORM_01) *********************************************************************************************/ GO   -- END   GRANT SELECT, INSERT ON [dbo].[XX_R22_APEX_GL_TRANSFORM_01] TO rptuser22   GRANT EXECUTE ON [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] TO rptuser22   GO
    
  • mightycpa
    mightycpa
    6 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-08-09T01:51:44Z  
    • mightycpa
    • ‏2012-08-09T01:47:07Z
    Hi Phil, thanks for your reply. I still have a problem, but I've made a lot of progress. I'm stymied at what seems to be the very end.

    I see that you've written the advice you gave me before, and believe me, I did read through the documentation pretty well. My problem ended up being not so much Cognos ignorance as my SQL Server ignorance. That said, the Cognos documentation could be a lot better, and the examples much better illustrated and explained.

    After I got some help from a co-worker, he explained to me that the "result set" that Cognos wants is only available as the first query in the SP. Move it to after the INSERT statement, and it won't return anything. This makes for an interesting problem. If I query first, then insert, it seems to me that my record set is going to be useless at best, misleading at worst. Nobody wants a report that shows "the data you had before you added another record just now." So, this is a curious choice of how to do things, by Microsoft, and a questionable choice of which functionality to choose by Cognos (record set vs. output parameter).

    The INT you mention took me a while to understand. The record set could contain only INT, but that's not the point. An INT result of

    14

    is different than a result set that looks like

    CNT
    ---
    14

    Duh. That's the sound of me catching on.

    Here is the new and improved Stored Procedure:

    <pre class="jive-pre"> USE [IMAPSStg] GO /****** Object: StoredProcedure [dbo].[XX_CERIS_LOAD_STEP1_SP] Script Date: 08/07/2012 14:15:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP PROCEDURE [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] GO CREATE PROCEDURE [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] @in_COGNOS_01 varchar(50) --, @out_COGNOS_result INT OUTPUT AS /************************************************************************************************ Procedure Name : XX_R22_APEX_LOAD_TRANSFORM_01 Created By : GEORGE ALVAREZ Description : Loads XX_R22_APEX_TRANSFORM_01 table, or disappears on failure Date : 2012-06-26 Notes : Prerequisites : Parameter(s) : Input : Output : Error Code and Error Description Tables Updated : Version : 1.0 ************************************************************************************************ Date Modified By Description of change ---------- ------------- ------------------------ 2012-08-06 GEA Created Initial Version ***********************************************************************************************/ -- BEGIN DECLARE @SQLServer_error_code Integer DECLARE @LongSentence VARCHAR(MAX) DECLARE @FindSubString VARCHAR(2) SET @FindSubString = ':' -- **************************** COGNOS NEEDS A RETURN DATASET ******************************* -- FIRST TYPE OF OUTPUT THAT SP CAN PROVIDE -- and the first query in the SP is where these have to be located. -- (FIND,START,LENGTH,REPLACE) SELECT FIND, START, LENGTH, REPLACE FROM dbo.XX_R22_APEX_GL_TRANSFORM_01 -- ********************************* THEN THE INSERT **************************************** INSERT INTO dbo.XX_R22_APEX_GL_TRANSFORM_01 (FIND,START,LENGTH,REPLACE) VALUES( SUBSTRING ( @in_COGNOS_01, 1, CHARINDEX ( ':', @in_COGNOS_01 )-1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1, CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1) - CHARINDEX ( ':', @in_COGNOS_01 ) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1)+1, CHARINDEX ( ':', @in_COGNOS_01,CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1)+1 ) - CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1) - 1), SUBSTRING ( @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01,CHARINDEX ( ':', @in_COGNOS_01, CHARINDEX ( ':', @in_COGNOS_01 )+1)+1 )+1, LEN(@in_COGNOS_01)) ) -- SELECT @SQLSERVER_ERROR_CODE = @@ERROR -- COMMIT /* ****************** COGNOS DOES NOT SUPPORT OUTPUT PARAMETERS *********************** -- SECOND TYPE OF OUTPUT THAT SP CAN PROVIDE -- SO SOMETHING LIKE THIS DOESN'T WORK FOR COGNOS -- YOU CAN'T EVEN INCLUDE IT -- (FIND,START,LENGTH,REPLACE) SELECT @out_COGNOS_result = FIND FROM dbo.XX_R22_APEX_GL_TRANSFORM_01 *********************************************************************************************/ /* ****************** COGNOS DOES NOT SUPPORT RETURN VALUE *********************** -- THIRD TYPE OF OUTPUT THAT SP CAN PROVIDE -- SO SOMETHING LIKE THIS DOESN'T WORK FOR COGNOS (NOTICE THE PARENS ... RETURN ( SQL GOES HERE ) -- YOU CAN'T EVEN INCLUDE IT RETURN (SELECT MAX(START) FROM dbo.XX_R22_APEX_GL_TRANSFORM_01) *********************************************************************************************/ GO -- END GRANT SELECT, INSERT ON [dbo].[XX_R22_APEX_GL_TRANSFORM_01] TO rptuser22 GRANT EXECUTE ON [dbo].[XX_R22_APEX_LOAD_TRANSFORM_01] TO rptuser22 GO </pre>
    part 2

    This gets me so, so close. I run the report, and it gives me the error in the attached file.

    There is no SQL generated by the report. The name of the Cognos TextBox variable is identical to the input parameter, except for the @ sign. Both are varchar(50). The data fits when inserted using Framework.

    I'm so close. What am I missing?
  • mightycpa
    mightycpa
    6 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-08-09T01:52:43Z  
    • mightycpa
    • ‏2012-08-09T01:51:44Z
    part 2

    This gets me so, so close. I run the report, and it gives me the error in the attached file.

    There is no SQL generated by the report. The name of the Cognos TextBox variable is identical to the input parameter, except for the @ sign. Both are varchar(50). The data fits when inserted using Framework.

    I'm so close. What am I missing?
    Finally, this is what the framework and report studio package is supposed to look like. Everything looks right.
  • mightycpa
    mightycpa
    6 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-08-09T03:39:21Z  
    • mightycpa
    • ‏2012-08-09T01:52:43Z
    Finally, this is what the framework and report studio package is supposed to look like. Everything looks right.
    Hoo-hoo! It works!

    I had a problem in my model... I use three layers -

    The database layer (which I had tested)
    The transformation layer (which I had not tested)
    The presentation layer (shortcuts to transformation layer that cannot be tested directly)

    At some point, I found that I had both the input and output parameters defined in my database layer, and they prevented the SP from working. So I got rid of the output parameter.

    But I failed to do that in the transformation layer, which is in reality the thing that is used (via the shortcut included in the package) in Report Studio.

    Frustrated, I chucked my preconceptions and checked everything, and voila, I found my error in very little time.

    Thanks for putting up with all the verbosity... it helps me to think out loud.

    Thanks Phil, for responding.

    I hope my explanation of the SP helps somebody.
  • venkat_Jey
    venkat_Jey
    92 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2012-10-11T23:08:06Z  
    • mightycpa
    • ‏2012-08-09T03:39:21Z
    Hoo-hoo! It works!

    I had a problem in my model... I use three layers -

    The database layer (which I had tested)
    The transformation layer (which I had not tested)
    The presentation layer (shortcuts to transformation layer that cannot be tested directly)

    At some point, I found that I had both the input and output parameters defined in my database layer, and they prevented the SP from working. So I got rid of the output parameter.

    But I failed to do that in the transformation layer, which is in reality the thing that is used (via the shortcut included in the package) in Report Studio.

    Frustrated, I chucked my preconceptions and checked everything, and voila, I found my error in very little time.

    Thanks for putting up with all the verbosity... it helps me to think out loud.

    Thanks Phil, for responding.

    I hope my explanation of the SP helps somebody.
    HI Mighty CPA,
    ]
    I have a similar problem but Its about how to send the value back to the DB.

    I have a stored procedure in my model which accepts two input parameters. I already have a list report which has lot of filters and gives few rows of data . I need to use two of those columns from the list report , to send it as input parameter to the stored procedure which will insert those two column in the back end DB.

    Any help would be greatly appreciate
  • RomilShah
    RomilShah
    1 Post

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2014-01-23T15:47:31Z  
    • mightycpa
    • ‏2012-08-09T03:39:21Z
    Hoo-hoo! It works!

    I had a problem in my model... I use three layers -

    The database layer (which I had tested)
    The transformation layer (which I had not tested)
    The presentation layer (shortcuts to transformation layer that cannot be tested directly)

    At some point, I found that I had both the input and output parameters defined in my database layer, and they prevented the SP from working. So I got rid of the output parameter.

    But I failed to do that in the transformation layer, which is in reality the thing that is used (via the shortcut included in the package) in Report Studio.

    Frustrated, I chucked my preconceptions and checked everything, and voila, I found my error in very little time.

    Thanks for putting up with all the verbosity... it helps me to think out loud.

    Thanks Phil, for responding.

    I hope my explanation of the SP helps somebody.

    Hello , 

     

    Hello ,
    I tried following the steps for DB2 and using Cognos FM 10.2.1 
    The store procedure is :
    CREATE PROCEDURE Insert_Comment (IN Product_Key VARCHAR(255), IN Comments VARCHAR(255))
    LANGUAGE SQL
    BEGIN
    IF ((select count(*) from SPT1 where APPROVERNAME = Product_Key) = 0)
    THEN
    INSERT INTO SPT1 VALUES (Product_Key,Comments);
    ELSE
    UPDATE SPT1 SET COMMENT = Comments WHERE APPROVERNAME = Product_Key;
    END IF;

    END@


    On importing SP into framework manager and validating it . I get following error 

    RQP-DEF-0179 The procedure call ...... is unable to return a result set.

     

    Any pointers ? 

    ReplyDelete

  • cognosboy
    cognosboy
    1 Post

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2014-01-24T05:26:44Z  
    • RomilShah
    • ‏2014-01-23T15:47:31Z

    Hello , 

     

    Hello ,
    I tried following the steps for DB2 and using Cognos FM 10.2.1 
    The store procedure is :
    CREATE PROCEDURE Insert_Comment (IN Product_Key VARCHAR(255), IN Comments VARCHAR(255))
    LANGUAGE SQL
    BEGIN
    IF ((select count(*) from SPT1 where APPROVERNAME = Product_Key) = 0)
    THEN
    INSERT INTO SPT1 VALUES (Product_Key,Comments);
    ELSE
    UPDATE SPT1 SET COMMENT = Comments WHERE APPROVERNAME = Product_Key;
    END IF;

    END@


    On importing SP into framework manager and validating it . I get following error 

    RQP-DEF-0179 The procedure call ...... is unable to return a result set.

     

    Any pointers ? 

    ReplyDelete

    It has been a while since I've done this.  I do have a working copy around here somewhere, I'll dig it up and get back to you.

  • venkat_Jey
    venkat_Jey
    92 Posts

    Re: How to pass value to stored procedure from Report Studio and display value

    ‏2014-01-27T22:02:28Z  

    This is the following SP I created in the DB2 and then added it in the FM as a data query  instead of a data modification.

    Publish the package and then use master detail relationship to pass required parameters to the SP in the report studio.

    As you can see my SP inserts rows in to the feed back table which can be eventually reported on.

    This works pretty well.

    CREATE PROCEDURE Sample.StoredProcedure (IN Description VarChar(64), IN Person VarChar(128))
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT * FROM Sample.FEEDBACK;
    Insert into Sample.FEEDBACK (Date, Description, Manager) Values (CURRENT DATE,Description,Person);
    COMMIT;
    OPEN CURSOR1;
    END