Can we save prompt selections made from Cognos Report Viewer to database?

[kma]

Cannot use Cognos SDK Customization. Cognos web is shared across may organizations.

I have a couple of approaches, need to know if these can work at high volume usages at enterprise level (At least 4K users).

Option 1:
1. Create a Report to which main report will drill down.
2. User insert query in the drilled report query and use the prompt answer in insert query.
3. Once the report executes, Prompt success message.


Option 2:
Use stored procedure instead of insert query, to save prompt selection to database.

Is there any other way by which this can be achieved in much more clean way?

Yes, you guessed it right, I am new to Cognos World..

I am a MSTR SDK developer, with some elementary exposure to Cognos..

[david.stachon]

I'm curious, why do you require this functionality?

There is Audit functionality supplied with Cognos 8 that may suit your purpose.

[kma]

Hi David,

We need to provide end user option to define custom market, apart from the market definition we have in the warehouse.

To Achieve this each user can define his custom market which is a combination of existing market from warehouse.

So if saving custom prompt selection can be directly achieved from Cognos  OOTB Report studio, it would be most desirable.

   MARKET lookup  = LU_MARKET + userCustomMarket_View

If this cannot be achieved then having a custom Java Web Application is another approach. But with this custom java web application, application integration issue may take up lot of time and energy.

The following article on Cognos support site, has information to use stored procedure. ( Al though the article is about some issues while using SP in framework manager.)

http://www-01.ibm.com/support/docview.wss?uid=swg21339265

Any obvious pitfalls in this approach?

Thanks in advance..     

[Sunchaser]

Hi kma,

As we're working with Oracle, for several reasons I use table functions instead of "classical queries" to retrieve datas from Oracle to Cognos.
This point allows me to do several things before the datas are sent, and so I pass all the parameters selected by the user and I can work on it (insert them in a particular table, etc ...) for different purposes.
Most of the time, the way I work in this case is a "critical point" for most of the Cognos developpers, as I call my functions as native sql in cognos, etc, etc... every time I speak about that I have messages tellnig me that it isn't the good way to work in Cognos, etc, but I've never been blocked in any way, performance is quite good (and most of the time better than reports done in a "classical way", for those that I can compare with mine in my job).

[david.stachon]

I don't see that approach in the article really helping you much, and it won't be particularly upgrade friendly. Likely, when you get to Cognos 9 it will need to be re-done.

I've had two projects which required a similar functionality that you're looking for (i.e. user defined groupings of your dimensional data).

I always like the table driven approach. Meaning, these groupings, if possible should be loaded into your data-mart.

....this would likely mean a few more tables off of your market_dim that map user_id, custom_market_group, and market_dim together.

...at run-time, cognos can pass through the user and this can be used to filter these custom groups. (i.e. so one user, doesn't see another user's groupings)


Now, in terms of loading the data into the mart, I've done something as cheap and easy as an Excel spreadsheet load (which works fine provided you're not dealing with too many users),  I've also been part of a .net app which allowed users to create there own "private groupings" for use in cognos.

what's good about the table driven approach is that it's reasonably upgrade friendly, bi tool agnostic (meaning, if you go to another vendor, you can leverage this design), and these tables can be used throughout the bi application as opposed to a bunch of code in one report.

hope that helps.

[kma]

Hi David,

Thanks for the details insight into different approaches..

I concur on all points except on the Cognos application upgrade issues...

I agree, in general, most customization need to be re-visited when a major version upgrade is done. (In this case JS code, maybe)

I would like to know, from your past experience with Cognos various version, has there been significant changes in the way a Stored procedure is used in FM and the Cognos Reports.

Any idea, if this may change significantly with Cognos 9?

Some other info..
We use SQL Server 2005.
We want to avoid using multiple application while providing user option to create custom grouping of our dimensional data.


Thanks again for the time and guidance.
Kma.


 

[Sunchaser]

Hi,

Cognos 9 ?
Whaou !... I'm going to think that there's a new version every 6 month  :D

And It's gonna be funny if we have to change our way of working with every version  ::)

[david.stachon]

It's hard to say what will change when the next major upgrade comes....

I guess you could call me "shy" regarding the creation of a lot of code inside cognos, because I have been bitten.

(although, fair enough ...with a database stored procedure call, you'll likely be okay)


Interesting you're using SQL 2005. This would be another reason to go with a table driven approach as I mentioned before ...if you were to put the groupings in the data warehouse, they could be leveraged by any Analysis Services cubes.