New User - Filtering/Grouping separately for each column in report table

[JPLSteve]

Hi All,

I apologise if this has already been answered elsewhere but was not sure of the best terminology to search for and have not seen it come up in the forum or help files having looked already. If it has, please point me in the direction of that/those post(s).

I'm a new user to Cognos and I'm trying to ascertain whether I will be able to produce tables where every column in the table involves a filtering and/or grouping of the underlying data (i.e. not based on a flat set of results where every column points to one of the fields in the results).

As an example, we're trying to do this for usage stats and we have an enormous dataset which essentially contains a number of records each of which is an "event". Each "event" record contains "event types" such as : login, search, document view, logout etc, along with other generic data like : date/time, userid etc.

Furthermore, for each "event type" there is further info - e.g. for event type "search" there would be the search query and for document view, we would know the document type (e.g. book, looseleaf, journal, report etc)

A very simplified example of this data and of the table we would want to produce would be as per the attachment (example_data_and_table.gif).

As you can see, all the data to populate these columns is in the underlying single dataset (filtered by user only) that could be pulled down in one go, but the data needs to be filtered or grouped further for the result in each column.

I'm guessing this can this be done in Cognos Report Studio but how do I go about achieving (or even starting) this?

Furthermore, I would like the table to be filtered by user as well as a report for each would need to be produced as part of a batch process.

Please let me know if more info is required. Any help to get me started would be greatly appreciated.

Many thanks,

Steve

[wyconian]

Hi

You can probably do this using some case statements in different columns e.g.

case when document_type = 'Book' then 1 else 0 end book_ind
case when docuement_type = 'Report' then 1 else 0 end report_ind

Then you can sum the case statements to get the number of books, reports etc.

You will probably need to group on user id to get the stats for each user.

Good luck :)

[JPLSteve]

Hi Wyconian,

Many thanks, will give that a go now and let you know how I get on.

Regards,

Steve