pass values to the underlying Database table

[cognos8_1]

I have a requirement where in I need to pass values to the underlying Database table. I dont think thats possible in C8? Reports extract data from the tables. Is there any solution to this ?

Let me know!

[sir_jeroen]

you could try using stored procedures....

[cognos8_1]

Could u please mention the steps? I have a requirement ,

For example, I have 2 prompts, 'User Prompt',  " Group Prompt". ( Multi select)
I select the user in "user prompt" , choose some groups from 'group prompt' and submit. This information has to be fed into the tables. Could you please help me in this regard?
Thanks a lot.

Regards.

[workdan]

Are you talking about filtering the data from a select query to only contain the specified users and groups? You can do that with filters in Report/Query Studio.

Or are you talking about actually inserting data into the database?

[cognos8_1]

No, I am not talking abt filtering data, that can be done by applying query filters.

I want to store values in the database. Like I said in my last post, every user will be entitled to a user group. I have a report with 2 value prompts, "User" (Drop Down, single select) and "User Groups" (Check Box, multi select).

So when I select a USER 1, in 'user' prompt, and choose Group1, group2, group3 for 'user 1' in 'User groups' check box prompt and submit the report, I have to pass these values (User1, Groups he is entitled to) to my underlying database.

First of all, is this possible in Cognos 8 Report Studio? As in reporting we just fetch data from the underlying tables.

[cognos8_1]

please explain in detail.

Thanks,,

[cognosfreelancer]

Yes this is very much possible. I did that for an Access database.

This is not the default functionality of Report Studio as they are read only tools.

You will need to use SDK for this.

In the SDK you could use the the JDBC driver to connect to the database.

At a high level the following are the steps you will need to take in the SDK:

1) Run the report
2) save the report output to a file location
3) Use the JDOM library to load the file into memory
3) create a database connection
4) set up a loop
5) read values from each row using JDOM functionality and load them into the database

Example syntax for handling the database piece is:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         String url = "jdbc:odbc:employees";
         Connection con = DriverManager.getConnection(url);
         Statement stmt = con.createStatement();

Hope this helps you get started


NKT

[goose]

seems like allot of work to just insert/update some records in the database... Unless cognos is your ONLY option there are a million better ways to provide a web based frontend for your requirement.

[cognosfreelancer]

In this case we needed to scrape the output on the screen to a database.

We had no control over the report meaning the filters, the tables, calculations anything could change so the best option was to extract the data from the report.

If not constrained by Cognos then I would agree with you that writing a simple JSP, FormBean and Java servlet by modeling the MVC pattern, could take care of the inserts

NKT