How to design Cognos interactive reports

Check list of all posts

The initial motive of researching this new concept is from my consulting job as Cognos consultant, where I’ve spent most of my time to author Cognos reports. It occurs to me that there is a need to create an interactive report for some business users, such as sales managers. These managers want to use reports do perform analysis.  They know the required data presentation, such as variance between sales and budget, but more important, they want reports to be very flexible to perform many analyses.

1. Main challenges for interactive reports

In order to satisfy this requirement, the report must be designed to satisfy three criteria below.

1. Superior flexible

Making report flexible is probably the most important. Technically, there is no way that we provide report with all used needed combinations. In addition, we cannot accurately project what combinations are needed from business users. Therefore, instead of providing pre-generated report, we can provide business users all choices that are needed, or let have unlimited choices for them to generate report on the fly.

2. Superior performance

As data will be presented on the fly, we need to make sure that reports are executed quick enough to give business users an answer. Normally, all prompt controls and filters for such reports are displayed on same page as report itself. If you have many filters on interface, these filters will take very long time to even populate. It is not easy to use traditional way to create such reports, as we need to make sure all elements on report takes the least time together.

3. Complicated data format with chart

This is part where business users know exactly format, such as sales, prior year sales against budget. It can be charts and crosstab. Unfortunately, list will be not used, as flexibility of list report is limited.

2. Options for interactive reports

There are some options to consider, but you will see the simple interactive report is probably the best option.

1. Active reports

Active report is very good to deliver high level information to manager and executive. However, active report cannot deliver too many detail information duo to size limitation. Even with most updated version, there is no way that we can produce all combinations, as the size will be huge, simply impossible.

2. Canned reports

Canned report is highly customized report to satisfy the exact requirement, but it is very costly. Again, there is no way we can get all combinations. Even we could, there is no way for business users to find the information they want.

3. Cubes

Whether it is a traditional power cube or dynamic cube, it provides all combinations to allow business users look into their data. However, there are two problems for them: 1) it requires a skill set to use it; and 2) they can’t get the complicated format generated by report studio.

4. Workspace advanced

This newest version does help business users to get data as requested, but it has the similar problems as cube. Business users need to have framework manager model to built report, and it is difficult for them to create complicated format.

5. Interactive reports

The best solution is to provide an interactive report for business users, where they don’t need to have training, but can produce query data into their requested format easily, and with good performance.

3. Solutions to interactive reports

While there is no problem to get complicated format for business users using report studio, the performance and flexibility becomes two major challenges for Cognos developers.

1. Using Prompt tokens to archive report flexibility

Prompt tokens can supply the actual definition of what the query contains and how it is to behave. This process occurs before a request is sent to the data source and in fact can be used to define what is sent to the data source for execution.

The prompt token macro brings all your imaginations to change Metadata and therefore results in a very way to author report. It can be used in both relational source and dimensional sources. We can provide all choices as simple description, but behind the scene, these descriptions become data items to retrieve data from database or cube.

2. Using Prompt tokens to archive report performance

When implementing an interactive report, right way developers add many filters. Furthermore, these filters will display on the same page as report. As there are too many filters, it is difficult to use and it take long time to even populate these filters. The solution is to make sure that data for filters are only needed when business users want them, otherwise filters should not take any time. The technical solution for this idea is somehow to use leverage prompt token concept, which will be explained later.

Data security could play a very important role, as data can be automatically filtered out and then bring it to Cognos. As the nature of crosstab is to generate a min-cube (.dmc) based on query, therefore to limit data from database becomes so important.

4. Solutions in Detail

the solutions below will be published in the future.

01 - Make columns flexible

02 - Make columns flexible with ID and description

03 - Make filters flexible

04 - Make filters flexible with ID and description

05 - Make filters flexible with operators

06 - Make filters flexible with search prompt

07 - Make Group by flexible

08 - Make measures flexible

09 - Make a filter flexible without page refresh

10 - Make multiple filters flexible without page refresh

11 - Make report view for interactive reports

13 - Make report element show and hide

14 - Make report freeze column