Modifying Formulas (Report Builder)
To return the data that you want to include in your report, you can change the formula, specify the aggregation level, remove duplicates, and apply filters to the formulas in the fields that you create and the fields that are a part of the data source.
Viewing Formulas in Fields
Some fields in your data source have formulas associated with them already, for example aggregate fields. You can view the formula of a field by right-clicking the selected field name in the Define Formula dialog box and selecting Replace With Definition. The field name is replaced with the formula used in the field.
Expanding Fields
A field that is referenced in a formula does not need to come from the same entity; it can come from a related entity. Expanding a field allows you to see the relationship between the field and the context entity of the formula that references the field. Once you have expanded the reference, you can change the aggregation level, remove duplicates, or apply a filter to an entity anywhere along the relationship path.
You can expand any formula within your data source as well as those formulas that you have created for this report. To expand a formula, simply double-click the formula in the Formula box, or right-click the formula and select Expand. The relationship between the entities is displayed below the Formula box.
.gif "Expanded field reference in Define Formula")
Filtering Formulas
Using filters on the formulas in the report, you can answer precise questions about the data. Filtering is used to exclude data from the report.
For example, suppose you want to create a customer list based on customer behavior. You want to know the average age of customers who purchased specific items within the last three months. To identify which customers fit into this category, you need to apply a filter. When the filter is applied, the list of customers is reduced to just those customers who fit the criteria that you specify. After this list of customers is returned, you can apply formulas to the customer list to get the information you want from the data.
To apply a filter, expand the formula, click no filter applied, and then click Create a new filter. If you have a previously defined filter that you want to apply to this formula too, you can click Use an existing filter instead. In the Filter Data dialog box, you can create, modify and save your filter, just like any other filter in Report Builder. When you save the filter, the name of the filter is displayed beside the name of the field formula. You can continue to edit the filter and the formula.
Working with Duplicate Data
Depending on the question you are trying to answer, you might want to keep or remove duplicate data in your report. For example, if customers have orders with products and you want to count the number of products per customer, you need to decide if you want to count products purchased multiple times by the same customer or only count distinct products per customer. By default, Report Builder retains all duplicates.
To remove them, expand the formula and click the Keep all duplicates of link. In the Remove Duplicates dialog box, you can select which duplicates to keep and which to remove.
Aggregation Levels
Depending on the type of data that you want to analyze and how you want to analyze it, you might want to specify an aggregation level on your data somewhere along the entity relationship within the expanded reference. Work with aggregation levels when you want to use two aggregations within the same formula.
For example, to get the average Amount per Order across all Orders for an Employee, you would Sum the individual line totals per order (Sum Amount from Order Details) to the Order level, then Average the amounts per order for all orders for the employee. The Sum of the Amount must be aggregated to the Order level only, and not floated to the Employee level. The level of aggregation you choose depends on how you are analyzing the data.
To change the aggregation level, you need to expand the field, right-click the entity that you want to aggregate to, and then select Aggregate to Here. In order to perform this type of aggregation, you need to have more than one entity displayed when the field is expanded.
See Also
Concepts
Working with Formulas (Report Builder)
Working with Filters (Report Builder)
Working with Literals in Formulas and Filters (Report Builder)