In this article, written by Yoav Yahav, author of the book SAP BusinessObjects Reporting Cookbook, we will cover the following recipes:
(For more resources related to this topic, see here.)
Filtering data can be done in several ways. We can filter the results at the query level when there is a requirement to use a mandatory filter or set of filters that will fetch only specific types of rows that will correspond to the business question; otherwise, the report won’t be accurate or useful.
The other level of filtering is performed at the report level. This level of filtering interacts with the data that was retrieved by the user and enables us to eliminate irrelevant rows.
The main question that arises when using a report-level filter is why shouldn’t we implement filters in the query level?
Well, the answer has various reasons, which are as follows:
In this article, we will explore the different types of filters that can be applied in reports: simple ones, interactive ones, and filters that can combine interactivity and a custom look and feel adjusted by the business user.
The first type of filter is a basic one that enables us to implement quick and simple filter logic, which is similar to the way we build it on the query panel.
We have created a query that retrieves a dataset displaying the Net Sales by Product, Line, and Year. Using a simple filter, we would like to filter only the year 2008 records as well as the Sports Line.
Perform the following steps to apply a simple filter:
We will navigate to the Analysis toolbar, and in the Filters tab, click on the Filter icon and choose Add Filter, as shown in the following screenshot:
In the Report Filter window, as shown in the following screenshot, we will be able to add filters, edit them, and apply them on a specific table or on the entire report tab:
By clicking on the Add filter icon located in the top-left corner, we will be able to add a condition. Clicking on this button will open the list of existing objects in the report; by choosing the Year object, we will add our first filter, as shown in the following screenshot:
After we choose the Year object, a filter condition structure will appear in the top-right corner of the window, enabling us to pick an operator and a value similar to the way we establish query filters, as shown in the following screenshot:
We will add a second filter as well using the Add filter button and adding the Line object to the filter area. The AND operator will appear between the two filters, establishing an intersection relationship between them. This operator can be easily changed to the OR operator by clicking on it.
The table will be affected accordingly and will display only the year 2008 and the Sports Line records, as shown:
In order to edit the filter, we can either access it through the Analysis toolbar or mark one of the filtered columns, enabling us to get an easier edit using the toolbar or the right-click menu, as shown in the following screenshot:
The report filter simply corresponds to the values defined in the filtered set of conditions that are established by simple and easy use of the filter panel. The filters can be applied on any type of data display, table, or chart.
Like the query filters, the report filters use the logic of the operators AND/OR as well and can be used by clicking on the operator name.
In order to view the filters that have been applied to the report tabs and tables, you can navigate to the document structure and filter’s left-hand side panel and click on the Filter button.
Filters can be applied on a specific table in the report or on the entire report.
In order to switch between these options, when you create the filter, you need to mark the report area. To create a report-level filter or a specific column in a table, you need to filter a specific table in the report tab.
Another great functionality that filters can provide us is interaction with the report data.
There are cases when we are required to perform quick filtering as well as switch dynamically between values as we need to analyze different filtered datasets.
Working with the filter bar can address these requirements simply and easily.
We want to perform a quick dynamic filtering on our existing table by adding the Country dimension object to the filter bar.
Perform the following steps:
By navigating to the Analysis toolbar and then to the Interact tab, we will click on the Filter Bar icon:
By doing so, a gray filter pane area will appear under the formula bar with a guiding message saying Drop objects here to add report filters, as shown in the following screenshot:
In order to create a new filter, we can either drag an object directly to the filter pane area from the Available Objects pane or use the quick add icon located in the filter bar on the left-hand side of the message.
In our scenario, we will use the Available Objects pane and drag the Country dimension object directly to the filter bar:
By adding the Country object to the filter bar, a quick drop-down list filter will be created, enabling us to filter the table data by choosing any country value:
This filter will enable us to quickly create filtered sets of data using the drop-down list as well as using an interactive component that doesn’t have to be in the table.
The filter bar is an interactive component that enables us to create as many dynamic filters as we need and to locate them in a single filtering area for easy control of the data, and they aren’t even required to appear in the table itself.
The filter bar is restricted to filter only single values; in order to filter several values, we will need to either use a different type of filter, such as input control, or create a grouped values logic.
When we drop several dimension objects onto the filter pane, they will be displayed accordingly; however, a cascading effect of filters (picking a specific country in the first filter and in the second filter seeing only that country) will be supported only if hierarchies have been defined in the universe.
Input controls are another type of filter that enable us to interact with the report data.
An input control performs as an interactive left-hand side panel, which can be created in various types that have a different look and feel as well as a different functionality.
We can use an input control to address the following tasks:
In this example, we will filter several components in the report area, a chart and a table, using the Region dimension object. We will be using the multiple value option to enhance the filter functionality.
First, we will navigate to the input control panel located in the left-hand side area as the third option from the top and click on the New option.
Perform the following steps:
We will choose the object that we need to filter with the table and the chart, as shown in the following screenshot:
After choosing the Region object, we will move on to the Define Input Control window.
Input controls enable multiple-value functionality, and in the Choose Control Type window, we will choose the Check boxes input control type, as shown in the following screenshot:
In the input control properties located at the right-hand side, we can also add a description to the input control, set a default value, and customize the list of values if we need specific values.
After choosing the Check boxes component, we will advance to the next window, choosing the data element we want to apply the control on.
We will tick both of the components, the chart and the table, in order to affect all the data components using a single control, as shown in the following screenshot:
By clicking on the Finish button, the input control will appear at the left-hand side:
We can easily change the selected values to all values (Select All), one, or several values, filtering both of the tables as shown:
As we have seen, input controls act as special interactive filters that can be used by picking one of them from the input control templates, that is, the type that is the most suitable to filter the data in the report. Our main consideration when choosing an input control is to determine the type of list we need to pick: single or multiple.
The second consideration should be the interactive functionality that we need from such a control: a simple value pick or perhaps an arithmetic operator, such as a greater or less than operator, which can be applied to a measure object.
An input control can also be created using the Analysis toolbar and the Filter tab.
In order to edit the existing input control, we can access the mini toolbar above the input control. Here, we will be able to edit the control, show its dependencies (the elements that are affected by it), or delete it, as shown in the following screenshot:
We can also display a single informative cell describing which table and report a filter has been applied on.
This useful option can be applied by navigating to the Report Element toolbar, choosing Report Filter Summary from the Cell subtoolbar, and dragging it to the report area, as shown in the following screenshot:
By clicking on the Map button, we will switch to a graphical tree view of the input control, showing the values that were picked in the filter as well as its dependencies:
If you need to display the values of the input control in the report area, simply drag the object that you used in the control to the report area, turn it into a horizontal table, and then edit the dependencies of the control so that it will be applied on the new table as well.
An element link is a feature designed to pass a value from an existing table or a chart to another data component in the report area.
Element links transform the values in a table or a chart into dynamic values that can filter other data elements.
The main difference between element links and other types of filtering is that when using an element link, we are actually using and working within a table, keeping its structure and passing a parameter from it to another structure.
This feature can be great to work with when we are using a detailed table and want to use its values to filter another chart that will visualize the summarized data and vice versa.
Perform the following steps:
We will pass the Country value from the detailed table to the line quantity sales pie chart, enabling the business user to filter the pie dynamically while working with the detailed table.
By clicking on the Country column, we will navigate in the speed menu to Linking | Add Element Link, as shown in the following screenshot:
In the next window, we will choose the passing method and decide whether to pass the entire row values or a Single object value. In our example, we will use the Single object option, as shown in the following screenshot:
In the next screen, we will be able to add a description of our choice to the element link.
And finally, we will define the dependencies via the report elements that we want to pass the country value to, as shown in the following screenshot:
By clicking on the Finish button, we will switch to the report view, and by marking the Country column or any other column, we will be able to pass the Country value to the pie chart, as shown in the following screenshot:
By clicking on a different Country value, such as Colombia, we will be able to pass it to the pie and filter the results accordingly:
Notice that the pie chart results have changed and that the country value is marked in bold inside the tooltip box, showing the column that was actually used to pass the value.
The element link simply links the tables and other data components. It is actually a type of input control designed to work directly from a table rather than a filter component panel or a bar.
By clicking on any country value, we simply pass it to the dependency component that uses the value as an input in order to present the relevant data.
An element link can be edited and adjusted in a way similar to the way in which an input control is edited.
By right-clicking on the Element Link icon located on the header of the rightmost column, we will be able to edit it, as shown in the following screenshot:
Another good way to view the element link status and edit it is to switch to the Input Controls panel where you can view it as well, as shown in the following screenshot:
In this article, we came to know about the filtering techniques we can apply to the report tables and charts.
Further resources on this subject:
At Packt, we are always on the lookout for innovative startups that are not only…
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…