Filter widgets by field that is not shown in report table

[mshparber]

Hello friends,

Please help - I am building a very simple dashboard that shows "Total Revenues" in 3 tables:
1 - by Product, 2 - by Country, 3 - by Time (Quaters). So each table has only 2 fields: the aggregator field, different in each table as stated and "Total Revenue" field.

I cannot figure out how to build a Filter that whould allow my users to choose a Product (or several Products) so that ALL the tables are filtered by that selection.
Currently this filter works only on "by Product" table - since Cognos does not find the "Product" field in other tables.

Originally - I've built all 3 tables based on the same query in Report Studio that does include the Product field - but it seems that Workspace sees only what's in the report table and not the query...

It seems so basic to me to be able to build such a simple dashboard - maybe I am missing something
Your help is so much appreciated

Thanks in advance
Michael Shparber

[MFGF]

Hello friends,

Please help - I am building a very simple dashboard that shows "Total Revenues" in 3 tables:
1 - by Product, 2 - by Country, 3 - by Time (Quaters). So each table has only 2 fields: the aggregator field, different in each table as stated and "Total Revenue" field.

I cannot figure out how to build a Filter that whould allow my users to choose a Product (or several Products) so that ALL the tables are filtered by that selection.
Currently this filter works only on "by Product" table - since Cognos does not find the "Product" field in other tables.

Originally - I've built all 3 tables based on the same query in Report Studio that does include the Product field - but it seems that Workspace sees only what's in the report table and not the query...

It seems so basic to me to be able to build such a simple dashboard - maybe I am missing something
Your help is so much appreciated

Thanks in advance
Michael Shparber

Hi,

I'm assuming you're using a relational package for these reports? If so, try the following:

1. Make sure the Product item is included in the query driving each widget (which it sounds like you already did).
2. Add an extra query to the underlying report containing the widgets. This query should be renamed _BusinessInsight_ (and the case is important as well as the underscores)
3. Add the Product item to this new _BusinessInsight_ query
4. Save the report, then bring the widgets into your workspace. You should now have the ability to bring in a Product select value filter that affects all three widgets.

If you are using a dimensional package, you don't need the extra query. You simply drag your set of Product members to the context area for each of the lists.

Cheers!

MF.

[mshparber]

Thank you very much!

Works like a charm!

I wounder whether there is a "manual" / article that explains exactly how this works

Thanks a lot!
Michael

[MFGF]

Thank you very much!

Works like a charm!

I wounder whether there is a "manual" / article that explains exactly how this works

Thanks a lot!
Michael

I haven't seen anything official. It's just a technique - if you have relational items you want to filter on in Cognos Workspace that are not displayed in the widgets you are bringing in, add the item(s) to the query of the widget and to a new query called _BusinessInsight_

That's it, really :)

Edit. I just checked, and it's in the Report Studio User Guide. Look at Chapter 3 - Creating a report > Reports for workspaces in Cognos Workspace > Enable filters in workspaces in Cognos Workspace (page 63)

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.2/ug_cr_rptstd.pdf

Cheers!

MF.

[mrcool]

Hi,

I'm assuming you're using a relational package for these reports? If so, try the following:

1. Make sure the Product item is included in the query driving each widget (which it sounds like you already did).
2. Add an extra query to the underlying report containing the widgets. This query should be renamed _BusinessInsight_ (and the case is important as well as the underscores)
3. Add the Product item to this new _BusinessInsight_ query
4. Save the report, then bring the widgets into your workspace. You should now have the ability to bring in a Product select value filter that affects all three widgets.

If you are using a dimensional package, you don't need the extra query. You simply drag your set of Product members to the context area for each of the lists.

Cheers!

MF.

Hi MF,

When I used a DMR as source I could see the Data item in filter properties but the values are blank. Is it a bug in 10.2.2?

[MFGF]

Hi MF,

When I used a DMR as source I could see the Data item in filter properties but the values are blank. Is it a bug in 10.2.2?

If you're using DMR, you have a dimensional package, so there's no need for the _BusinessInsight_ query in your report. You simply need to drag the desired set of members to the context area then save your report.

MF.

[mrcool]

Hi MF,

I did the same. I am only seeing blank in the "Use only the following Values" section.

[mrcool]

Looks like bug in 10.2.2. I will install FP and check again.
I tried in 10.2.1 and it worked. Thank you so much MF.

Thanks
MC