Filtering and Sorting the Data for Report Writers

When you first create the report (or edit an existing report) you see the Edit Report dialog box shown below containing the Filters and Sorts tabs.

Filters

The Filters tab allows you to activate additional data filters at the report level rather than having to modify the WHERE clause of the DataBlock's report query.

 

To add a filter you need to activate additional filtering by checking the Activate additional filtering box.

 

Although filtering in this example is done using only one field, a filter can consist of multiple fields with operators linking them together (=, <, <>, etc.).

You can create filters by selecting the list of fields and filtering them by the operators (+, <, <>, and, or, etc.).   For example to view salaries above 60,000, double-click on Employees.salary, click greater than (>), then enter 60000.  The resulting filter is shown below.

Click OK and run the report to view the application of the filter.

 

 

 

 

 

 

 

 

 

 

 

 

Sort

Click on the Sort tab to add custom sorting.

You will need to override the default sorting in order to add your custom sorting. Check the Override the main report query sort box.

Add Employees.dept_id and Employees.salary by selecting them in the Available fields list and clicking the right arrow button  to add them to the Ordered fields list.

Sorting Options:Double-click Ascending/Descending to toggle sort by ascending or descending order.

You can sort using multiple fields.  The figure illustrates sorting by department name (ascending), then by salary within the department (descending).

You need to activate sorting by clicking the “Override the main report query sort” checkbox at the top of the tab.

Move items over that you wish to sort on by double-clicking on the item or single-click then clicking the arrow pointing to the right. You can reorder fields by clicking the up or down arrow to the right of the Ordered Fields box. You can change the sort order to ascending and descending by clicking on the double arrows.

Filters and sorts involve editing the report so, once they are turned on, only a Report Writer can deactivate or change them, and Report Viewers cannot. To deactivate a filter or sort just uncheck the activate box. You do not need to clear out the filters and sorts. The Filters tab adds filters to the Report query, so all the conditions and filters in the DataBlock run, and then your additional filters are applied.

Note: If you define a sort order by using the sort tab, you will override the sort order in the DataBlock, so you essentially will be ordering the recordsets twice. The DataBlock will sort based on whatever sort order it is using. When the report is created it will reorder the recordsets based on the report sort order.

After choosing your sort options, click OK then execute the report to see the results.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Filtering and Sorting the Data for Report Viewers

 

 Dashboard - Edit Sort

The Configure Sort window allows you to sort the results in the selected multi-column list box. You can build a complex sort based on several fields by adding them to the Ordered fields list.

 

• Available - a list of the available fields that you can sort on
• Add arrow - add the selected field to the ordered field list
• Remove arrow - remove the selected field from the ordered field list
• Ordered field list - list of fields included in your sort, showing the direction of the sort on that field
• Double up/down arrows - toggles between ascending and descending sort
• Up arrow - moves the selected field to a higher position in the sort order
• Down arrow - moves the selected field to a lower position in the sort order

 

Dashboard - Edit Filter

The Edit Filter option filters the results in the selected Multi-column List Box. You can build a complex filter based on several fields by adding them to the Existing Filters list.

 

Options

• Available fields - a listing of the available fields that you can filter on
• Operator - sets the type of condition for your filter such as equals or greater than
• Value - the value you are comparing the field to in your filter expression (case sensitive)
• Plus - add the defined filter to the list
• Existing Filters - the list of filter expressions that will be applied to your Multi-column List Box
• Minus - remove the defined filter from the list