Folder Level Filters in Impromptu

[badhri]

Greetings,

Let me ask my question with a small example to make sure I communicate my requrirement clear . Say I have a fact table with the columns SUBJECT, COL1, COL2

SUBJECT COL1 COL2
XXXXX 1 A
YYYYY 2 B
RRRRR 3 C

Now in my Catalog apart from the normal dimension tables I want to have 3 folders say XXXXX, YYYYY, RRRRR and when the user uses the columns from the corresponding tables I want only the corresponding subject records to be displayed to the user.

Kindly let me know how we can achive this using Cognos Impromptu. Please let me know incase you need any more info in this regard.

Thanks and Regards,
Badhri ...

 

[griffindm]

Badhri,

The best way to do this, if you have sufficient rights, is to create three views of the table with a corresponding filter in each view, as in

CREATE VIEW TAB1XXXX AS SELECT * FROM TABLE1 WHERE SUBJECT = 'XXXXXX'

This ensures that the user will only get the filtered data in Impromptu regardless of the source selected.

The other alternative is to create three aliases in Impromptu and join each to another dimension table that contains the data on 'SYSTEM' and filter the join. The limitation on that approach is that the report must pull dimensional data from the second table in order for the join to be repected.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com