Connecting tables in Active Reprort

[mshparber]

Hello friends,

Please help - I am building a very simple report 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 want to allow my users to click on a Product in "by Product" table so that the other 2 tables ("by Time" and "by Country") are filtered by that selection.
The problem is that the "New Connection Wizard" does not find the "Product" field in other tables (and I do not want to show it in those tables, just to filter by it.

I've built all 3 tables based on the same Query that DOES include the Product field - but it seems that Connection 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 report 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 want to allow my users to click on a Product in "by Product" table so that the other 2 tables ("by Time" and "by Country") are filtered by that selection.
The problem is that the "New Connection Wizard" does not find the "Product" field in other tables (and I do not want to show it in those tables, just to filter by it.

I've built all 3 tables based on the same Query that DOES include the Product field - but it seems that Connection 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,

Hmmm. A very similar requirement to the Cognos Workspace widgets we discussed in your other post :)

To be able to filter a list using an Active Report variable, the item you want to filter on *must* be in the list. However, it doesn't have to be visible :)

Drag the Product item into the other two lists, then select the Product column in each of these two lists and set the Column Visibility property to "Hidden". You will now be able to set up a connection to these lists that filters on Product, but it will not be shown when the Active Report renders.

Cheers!

MF.

[mshparber]

Yes, it's really the same problem I was facing in the Workspace and that you helped me resolve there.

I am hesitating whether to choose a Workspace or an Active report option for my Dashboard.
I find Active report more flexible since it has more widgets such as Datatab etc.
And I mostly like the ability to click on a member of a column in a table so it filters the connected objects. If I am not mistaken, in Workspace there's no such an option - you have to do this only with the filter object.

I have tried to Hide a column as you propose, but the problem is that I have summarized tables and not a simple list. Let's say my "by Country" table shows total revenues for each of my 8 countries, so there are only 8 rows. If I add the Product field, it will show more rows - for Product*Country, even if I HIDE the Product column ( it just shows each country several times which I don't want).

I am pretty sure that there should be a workaround, maybe some trick.

Please help

Thank you very much
Michael

[MFGF]

And I mostly like the ability to click on a member of a column in a table so it filters the connected objects. If I am not mistaken, in Workspace there's no such an option - you have to do this only with the filter object.

If you have Cognos 10.2.2, there is now the ability to select a widget in a workspace and turn on a "Master filter" option, which then allows you to click on values in that widget to filter the other widgets on the page.

I have tried to Hide a column as you propose, but the problem is that I have summarized tables and not a simple list. Let's say my "by Country" table shows total revenues for each of my 8 countries, so there are only 8 rows. If I add the Product field, it will show more rows - for Product*Country, even if I HIDE the Product column ( it just shows each country several times which I don't want).

You will see repeating values when the Product column has no filter values. When you select one specific product, the repetitions disappear. The simple workaround is to define a default value for the Product variable so that only one product is ever selected.

Cheers!

MF.

[mshparber]

Thanks MF.
I have 10.2.1 so I still don't have this option in Workspace

In Active report I cannot filter by one product by dfault, since I want my users to see ALL the revenues: by Country, by Product and by Time. Only then I want them to click on values and filter other tables by the value.
I still think there should be a nice workaround. It just seems to me such a simple and straightforward task

Michael

[MFGF]

In Active report I cannot filter by one product by dfault, since I want my users to see ALL the revenues: by Country, by Product and by Time. Only then I want them to click on values and filter other tables by the value.
I still think there should be a nice workaround. It just seems to me such a simple and straightforward task

Oh. Filtering doesn't work that way, sadly. An alternative approach is to use a deck with two cards. The first card contains a list with no filtering and no hidden Product column (so the user sees an overall summary for all products). The second card has the filtered list with the hidden Product column (what you have at the moment). You can drive the cards based on whether the Product variable is set or not - ie when it has no value, the first card is displayed, and when you choose a Product the second card is displayed.

Cheers!

MF.

[mshparber]

Hi MF,

Very nice trick! I liked the concept and definitely will try it!
I am still bothered that this way I would have to maintain two reports - one in each card, including maintaining the design, interactivity, etc. Generally I don't like having to maintain the same report in two places...
Another issue is that I want this kind of interactivity for EACH of my three tables on the report.
I want to allow my users to click on any value - Product, Country or Time. So I should maintain 2^3=8 cards - so that's a lot of "double maintainence"

Isn't there another option? Maybe to have some "hidden object" that replaces just that particular table that the user click on?
Or some other workaround?

It just looks to me such a simple task - I am definitely not the first that has this straightforward need for a simple interactivity

Thanks a lot!

Michael

[mshparber]

Hi MF,

Just to let you know that I have implemented your recommendation to use a deck with two cards - each containing a table with different granularity.

Thank you very much for taking time understanding my needs and providing a fast professional answer.

I appreciate your help very much!

Sincerely,
Michael Shparber