Adding Radio Buttons to a Cognos 8 report that sets off a Stored Procedure

[gidster]

Hi there,

We are trying to set up a report which will bring back a reasonable number of rows to an end user (say 100 - 300) that the User needs to look at, and flag any that they notice as being wrong as an error.

Clearly Cognos 8 is not really designed as a data entry tool.  However, I was thinking about adding a radio-button or tick-box option to each row that the User would click as required.  This would then set off a Stored Procedure which would mark the selected record(s) as errors in the underlying database.

Doing this line by line would (I think) be tedious.  So I was thinking about adding some further code to allow the User to click as many items as they like and then click a button to "Commit" all of the selected changes to the database (via the Stored Procedure).

I think this would give the User a reasonable interface and means that they are able to (practically) instantly re-run the report and see all of their items selected with the new error flags.

The only problem is that I am not exactly sure as to how to go about adding this customisation... and that is where I am hoping you guys can help!

a.) Do you have any examples of similar coding that does this

b.) Is there any documentation that can help (I haven't been able to find any yet!) and,

c.) Is there anything we should watch out for? Any 'gotchas' etc.?

Thanks in advance for your help!

[MFGF]

Hi,

Cognos 8 does give you the ability to call a data modification stored procedure, but unfortunately you can only call them from within Event Studio, not from within a report.

You might find a way to achieve the desired result using the SDK and your favourite programming tool, but otherwise the C8 reporting interface will not support what you're trying to achieve.

Regards,

MF.

[gidster]

Thanks MF...but I am not quite sure that this is true.

What I can share with you is that we are currently using a Report Studio report that brings back records of significant variances to budget.  The Users see this report and are then able to add their own comments.  They do this by clicking a link to a drill-through report.  The drill-through link opens up a new report which has a prompt page.  The prompt page has already had all of the parameters passed through it from the first report (eg Cost Centre, Month etc.) and the Users enter their comments.

When they click the Finish button on the Prompt Page the Stored Procedure is called that then writes these comments to a table (again using all of the parameters of Cost Centre, Month etc.)  Clearly the Users do not realise it is a prompt page they are looking at (they think it is just a comments box).

Once they have entered their comments the original report can then be re-run and this time will also bring back the comment (I think this is achieved by using a Left Outer Join on the two tables).

So I am not sure this is what you meant by using a Data Modification Stored Procedure from within Report Studio, but it does work for us!

In terms of my original post, the reason I am asking for more information is because Cognos have actually suggested this as a solution to our issue.  In fact they emailed me a screenshot example of a Cognos 8 report with tick boxes available and they suggested we would do something similar.

They suggested adding a tick box to each of our records such that, when a Submit button was pressed would set off a piece of java script (or similar).  The script would then take each of the rows that have been ticked and pass them through to a prompt and thereby into a stored procedure.

They mentioned that they would anticipate the fact that the stored procedure would probably need more work as it would be a variable string of responses we'd be sending back rather than just one row at a time, eg: a CSV that says, for example, "Gidster, Finance Department, 1,7,10,50" where the numbers are the rows I'm rejecting.

This all seemed excellent in theory - and would give us what we need - but I am just not quite sure how to go about it.  Especially as I am a Finance guy and by no means an IT expert!

Once again, thanks for your assistance

[MFGF]

Hi,

Basially, you are achieving this by adding your own code into the web page of the report.  If I were you I'd be very cautious of doing this - for example, what mechanism do you have to prevent two users overwriting each other's comments?  What transaction isolation level does the report use (ie could users be looking at and adding comments to stale data that has been updated already since the initial report ran?)  If you increase the isolation level, what locking implications will this have within your database? etc etc

Cognos 8 allows two kinds of stored procedure to be called via the normal modelling/studio mechanisms.  Data Query stored procedures query the database and return a result set which can be used in normal reporting - these are usually modelled as query subjects where they occur within your packages. Data Modification stored procedures alter data in the database, and these are only made available in Event Studio - ie there are no tools or objects in any of the other studios that allow you to call them directly.

It seems that in your case someone has written their own code to achieve calling a data modification stored procedure from within the web page of one of your reports, but normally I'd strongly recommend against this approach for all the reasons above, plus it's usually way beyond the capabilities of a C8 report author to create, debug and maintain such a report.

I guess I'm just sticking my oar in (as usual!) - feel free to ignore the cautious grumblings of this old man if they are not applicable to your sutuation  :)

MF.

[johnpenna]

Adding comments to a Report using a stored proc.

Create a comments table in the database with columns for comment text, timestamp, and any identifiers that you need to pin the comment to (i.e. transaction number on your fact table).

Create two stored procedures that update and insert into the table accepting the input parameters of whatever your columns are.

Create Query Subjects in Framework manager that point to the stored procedures. Create a small report that has parameters for ?Comments?and ?Row-ID? (or whatever identifier you're using to pin your comment to) and tie your insert or update Query Subject to this report. Drill through from your dashboard to the small report passing through the Row-ID Parameter and prompting for comments on the way (value prompt looks like a text box).

The parameters passed by the drill-through will pass into the insert stored proc and add the row to the comment table.

Hook the comments table back into your query structure either in FM or straight in the report and requery. You can use the security CAMIDs as well to add private comments and creative things like that.

[newtheorizer]

Hi Gidster,

Can you give me the details of the document that cognos sent you about creating radio buttons and calling stored procedure? I think I saw the document some time ago online but now that I need it, I am not able to find it. Thanks in advance!

[Sunchaser]

Hi everybody,

In my case, for the moment (it meens in 8.2, report studio), I'm able to update tables in the same times the report runs.
The point is that I'm using Oracle, and that the SQL used to generate the values for prompt page as well as for the report himself comes from functions in packages.
So (using things like "pragma autonomous" in the body of the function, etc) in the functions, we're able to update some tables before the "classical" SQL for the report is run.
At the same time, I used HTML Items linked to some fields of a "Cognos Query object". These fields gives me the datas + html/javascript needed in order to have a list with radio-buttons + values + "the onclick_event", etc .... I don't even need to use a "cognos prompt object" as Value-Prompt.
So i can feed correctly my variables correctly, do (for ex) a RePrompt() and pass parameters and variables to the Oracle's function.

I don't mean It's a very good thing, but It works correctly (for the moment...)