Active Report (Relational) Challenge

[Ravisha]

Hello Cognoise,

I'm tasked to create an Active report. We are on 10.1.1 version of Cognos.

I've to call a stored procedure (sql server) from the Report studio (pass-through SQL). The proc has two parameters out of which one of the parameter (date) is optional. When the proc is executed without the 'date' parameter, the data set is returned for current_date.

So, the report requirement is to provide the user with a date prompt on the report page layout itself. So that the user can select any date (less then current_date) and one of the dynamic column in the crosstab changes per user's date selection. Also, the report shouldn't have any prompt page.

The reason I've choose active report rather than a traditional report authoring is because the requirement demands 'Data button bar' to filter the crosstab.

Since the active report downloads the whole data set into .mht file (in this case current_date when date parameter is not passed into proc) on initial run ; after the report is executed, the user will neither have the ability to select a different date nor slice and dice the dynamic date column per date selection from the date prompt. Is there any way to re-call the stored proc again based on the user's date selection in an active report?

Example:

Product Line (data button bar) :

Product Type (data button bar):

DatePrompt:

                  Dynamic Date (default to current_date)       MTD         QTD        YTD

Measure1                       123                                              123           123        123
Measure2                       123                                              123           123        123
Measure3                       123                                              123           123        123

Any solutions would be greatly appreciated.

Thanks in advance

[MFGF]

Hello Cognoise,

I'm tasked to create an Active report. We are on 10.1.1 version of Cognos.

I've to call a stored procedure (sql server) from the Report studio (pass-through SQL). The proc has two parameters out of which one of the parameter (date) is optional. When the proc is executed without the 'date' parameter, the data set is returned for current_date.

So, the report requirement is to provide the user with a date prompt on the report page layout itself. So that the user can select any date (less then current_date) and one of the dynamic column in the crosstab changes per user's date selection. Also, the report shouldn't have any prompt page.

The reason I've choose active report rather than a traditional report authoring is because the requirement demands 'Data button bar' to filter the crosstab.

Since the active report downloads the whole data set into .mht file (in this case current_date when date parameter is not passed into proc) on initial run ; after the report is executed, the user will neither have the ability to select a different date nor slice and dice the dynamic date column per date selection from the date prompt. Is there any way to re-call the stored proc again based on the user's date selection in an active report?

Example:

Product Line (data button bar) :

Product Type (data button bar):

DatePrompt:

                  Dynamic Date (default to current_date)       MTD         QTD        YTD

Measure1                       123                                              123           123        123
Measure2                       123                                              123           123        123
Measure3                       123                                              123           123        123

Any solutions would be greatly appreciated.

Thanks in advance

Hi,

No - this isn't how Active Reports are designed to work. All of the data required needs to be included as the report is initially rendered, and the Active Report controls then allow you to control which parts of the data are displayed.

MF.

[Ravisha]

Hi Mark,

Thanks for the swift response. I very much appreciate it.

I thought of the same way. But the requirement is to have the data button bars. If I create a traditional report (Relational), I might end up creating cascading prompts (which isn't the part of requirement and users doesn't prefer it). Is there any way/workaround it ? Please advise.

Thanks

[Ravisha]

Hi Cognoise,

Any ideas or suggestions in this matter? I still can't seem to figure out an approach.

Thanks