How to achieve filtering of data without the dependency on other

Considering the following Dataset, we will filter the data using Filter Component and Combo Box in SAP Business Objects Dashboards 4.1

Customer	Material	City	Sales org	Quantity	Price
John	Laptop	Bangalore	1000	10	5000
John	Laptop	Chennai	2000	10	5000
John	Charger	Bangalore	1000	50	1000
Steve	Laptop	Bangalore	1000	50	25000
Steve	Charger	Chennai	2000	100	2000
Mathew	Laptop	Mumbai	3000	10	5000
Mathew	Charger	Chennai	2000	50	1000

Filter Component

Filter component in SAP Business Objects Dashboards 4.1 behaves as the filter in Excel. (It filters all the records on the given criteria).

When data is filtered on Customer- John, then Material Filter shows both Laptop and Charger but Location Filter shows only- Bangalore and Chennai and no Mumbai (when Material is Laptop) because there is no record for John where corresponding City is Mumbai.

Figure 1

Figure 2







Figure 3

Disadvantages of using this Filter Component

1. Default Label Selection cannot be done. Hence all records do not appear initially.
2. “All” Functionality cannot be implemented using filter component.
3. Selected label component will not be displayed in the output.(e.g.- Customer, Material , Location)

Combo Box

To filter the given data set, on one column without depending on others. We use combo boxes.

How to filter data without the dependency on other using Combo Box?

1. Create a Master data for all the dimensions on which you wish to apply the filter with a All value on the top.

Example: - Customer Master Data with “All” on top.

2. Create Combo Boxes with Labels as Master data, Insertion Type as Label, and Destination as some cell.

3. Create a Column for displaying all values by default and also for filtering data without dependency on other.

Column(All Values)=IF($B$16=B2,B2,"All")&IF($C$16=C2,C2,"All")&IF($D$16=D2,D2,"All")

‘B16’, ‘C16’ and ‘D16’- Destination cells of combo box.

4. Now, for Hidden Combo Box, Use the column mentioned in Step 3 as Label. And create a cell where the destination cells of other three combo boxes are concatenated.

Cell( Concatenation)=IF(B16="","All",B16)&IF(C16="","All",C16)&IF(D16="","All",D16)

‘B16’, ‘C16’ and ‘D16’- Destination cells of combo box.

For more Information,Download the Xlf from the below Link

https://www.dropbox.com/s/k7tnf1cq020gwqd/Dashboard%20Sample.xlf