List Filter

[funkigreendog]

I'm trying to filter a list, however I'm running into a few issues. The key upon which I would like to filter is not being displayed in the report, as such I've had to add it into the list and them set visible to no. This works fine when I select a value to display, however as soon as I select all I end up with multiple records per value (they dont get rolled up). Is there any way around this?

[charon]

hi,

im not quite sure what the isse is exaclty, but note: you dont have to add a data item to the list and then set it to invisible, it will be enough to add the data item to the query.
May i ask how you filter your list? And hwat kind of your data source is (rel, dim, dmr?)

[hthakkar]

Can you please elaborate more on this.

[funkigreendog]

I thought I was going out of my mind, so I recreated a simple example. ::) ::)

Please refer to the image attached.

So my drop down list selects a data source. My list does not contain data source in it. When I try and set the variable to filter the data source field, it does not appear as an option. Therefore the only way to do this would be to add the data source into the list, and then not show it. However this causes issues with repeated rows and groupings.

Hope this makes more sense now.

[funkigreendog]

BTW..I just realised what may have caused the confusion. :o :o :o

I'm using an active report and trying to filter a list using a drop down (as opposed to just filtering a query).

[algae]

you probably have answer by now - but if not, have you tried putting the list in a data deck? You may be able to set the data deck default card to Yes, then copy the list into the default card zone.

[charon]

No need to use a data deck for a list. You just need the data items you want to filter/sort in the list itself. in case your query of the list does not contain this item, you need to insert them in the query or copy from the source query you want to filter from (in this case, dropdown).
should work, i just did it myself :)
cheerz

[funkigreendog]

Yes but if you add it to the list and you dont want it shown , it ends up mucking about with your groupings and showing more than one row for the same dimensional value.

[rams143]

Did you find a way to resolve this issue yet? I am facing a similar issue too. Please share your solutions if you got any.

Thanks in advance.  :)

Rams.

[MFGF]

Hmmm. We seem to be going around in circles. There is no requirement to display an item in a list if you want to filter on it (with all the implied complexity of hiding it). It simply has to be in the query the list is sourced from, and enabled in the properties of the list.

1. Select your List in the report, right-click, and select 'Go to query'
2. Drag your required filter item into the query
3. Navigate back to your report page and select your List again
4. Go to the 'Properties' property of the list, and adda check mark next to your item
5. Define the connection between your drop down and your List, and away you go.

Cheers!

MF.

[rams143]

Hi MF,

Thanks for the reply.

I get that we dont have to display in the list it can be added to properties. But the issue here is different let me give you an example. To keep it simple say you have a list that displays Region and Quantity.

Region                    Quanatity
---------                   --------------
Americas                    100
Asia                              80
Europe                         70
International              110

Now in the drop down you have Product Line.

Product Line
----------------
Camping Equipment
Golf Equipment
Personal Accessories

So by default my report should show me Region and Quantity for all Product Lines and when I pick one say Camping Equipment the report display only the Regions where it is sold and the respective quantity. The filtering part works fine when I add the Product Line to the Query properties of the list. But the issue is when it displays for all Product Lines. This is what I see in the output.

Region                    Quanatity
---------                   --------------
Americas                      60
Americas                      20
Americas                      20
Asia                              50
Asia                              30
Europe                         70
International                60
International                40
International                10

So it doesn't group the Region and display 1 row per Region.

Please advice.

Thanks,
Rams.

[MFGF]

Ah - I see! In this case, you need to switch from using a list filtered by your variable to using a data deck.

1. Remove the filter connection between the Drop Down List and your List object.
2. Drag in a Data Deck and drop Product Line into the Values.
3. Drag your List into the Data Deck.
4. Right-click the List and Create a Master/Detail relationship with the Data Deck based on Product Line
5. Create a connection from the Drop Down List to the Data Deck based on Product Line - do not select a default value
6. Select the Data Deck and set the Default Card property to Yes
7. Drag a new List into the "No Data Contents" area of the Default Card
8. Drag Region and Quantity into your new list

Cheers!

MF.

[jaggsjassal]

Hey MFGF,
I have exactly the same issue as op has. I have a list object in Active reports that is filtered upon 4-5 criterias. Using Data Deck and Master Detail relationships you can filter upon small dimensions like Year etc which I have been doing so far so good. But when you need to filter upon several dimensions like Year, Month, District, Worker Type, Expense Type etc, then Master Detail and Data Cards become a drag!!
Furthermore if you have sophisticated filters like Month between 1 and ?Month?, District can be 'All' or any one of the choosen, etc, Master Detail fails as there is no District by the name 'All'. Now I can use the feature of data deck of Default Card, but what if 1 filter like District is properly selected while Worker Type is selected as 'All'???
Is it advisable to include Master Detail for a few filter issues and Container Filter for other filter types, all at the same time on a same list report? If using Container Filters through Interactive Behavior, I again come back to the issue that OP is facing. I do not want to show unnecessary data items on my list report, yet want to filter upon them without going through the aggregation issue.
Please help!!

[MFGF]

Regardless of the number of items, you still need just one data deck, and one master/detail relationship, which just has multiple links, so it's not the nightmare situation you imagine.

If you want to mix the "All" value from some subjects with specific items from others, things are a little more challenging. The Default Card will probably correspond to the "All" value from all controls. If you are reporting off a dimensional package, it's less of a headache - you can feed each control with a set of members comprising the "All" member of the hierarchy and the other relevant items, allowing users to go back and select "All" from any/all subject areas.

Generally, using data decks is considered better practice than filtering. It's not a hard and fast rule, but does seem to be the advice coming out of the experts at IBM.

Good luck!

MF.

[Anantula]

Hi All,

I recently came across the same issue, fixed with below logic:

Issue:

Region                    Quanatity
---------                   --------------
Americas                      60
Americas                      20
Americas                      20
Asia                              50
Asia                              30
Europe                         70
International                60
International                40
International                10

In my report, I have 3 cascading data drop down lists, product line, product type and product. All I want is total revenue in one single row for any of the Product Line/Type/Product selections.

Solution: 
Please refer to attachment and then apply formatting changes.
Change the foreground colour of product line list column body and remove the borders.

Cheers,
Anand

[jdaems]

Thank you Anantula,

But what if you have those three drop down boxes (product line, product type and product), but you want to show the total per region, like this:

Region                    Quanatity
---------                   --------------
Americas                    100
Asia                              80
Europe                         70
International              110

So people have the opportunity to filter on a certain product type or product line or product, but they are always looking at the total per region for those filters. How do I do something like that? My idea was to create a data deck and put product line, product type and product in them. Then put the list in there and use a master-detail relationship. This works, but there are two down-sides: performance is quite bad in making the report as he is creating a list for every combination of product line, product type and product. And by default he is already selecting a certain card, so you cannot choose to see the data for all product lines, types and products. And this is a big downside.