Working with analysis report calculated fields |
|
|
|
Working with analysis report calculated fields |
|
|
Working with analysis report calculated fields |
|
|
|
Working with analysis report calculated fields |
|
|
This section deals with creating a calculated field using the expression builder. You can also create a calculated field that varies depending on the resource it describes with resource mapping.
When you are building a report, you may need to use the same measure or attribute to form the foundation for a number of fields. If this measure or attribute exists as a row or field in XPAC, you can create multiple new measures or attributes based on this field.
When you create a new measure or attribute, you cannot use that measure or attribute to build further expressions (that is, your new attribute will not be available in the list of query fields in the attribute and measure expression builders).
To create a new query field, you need to calculate your own field. You can do this using the calculated expression builder.
You can open the calculated field expression builder from either the attribute or measure expression builder (see Working with analysis report expressions for more information).
After opening either the attributes or measures expression builder, you can create a new calculated field by right-clicking in the left pane and selecting Add calculated field.
If you right-click a calculated field you have created (as opposed to an existing field), you can edit that field by selecting Edit calculated field. You cannot edit an original field, although you could create a new field to return the same value and edit that.
The left pane lists all the available query fields, which include:
| • | all the data rows you imported originally |
| • | the data rows imported or created by analysis reports as a result of the report type you selected |
| • | calculated fields. |
|
When creating new calculated fields (or measures) that refer to quantitative data for schedule reports, it is important to multiply the calculated fields with the ActualPercent query field. The ActualPercent query field returns the actual percent of each block that is being mined within the period. |
If you right-click a calculated field that you created (as opposed to an original field), you can delete that field by selecting Delete calculated field. You cannot delete an original field.
|
Make sure you include all the right Visual Basic syntax. Do your brackets match and are they of the right type? Did you include "=" where appropriate? XPAC will perform a basic validation check, but can only parse what you have written. |
The caption will display in the left pane of any expression builder as the display name of your new query field.
You can create almost any expression using the query fields, the expressions toolbar and the Visual Basic syntax and functions available in the Microsoft.VisualBasic assembly.
For more information about Visual Basic syntax and functions, see http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.aspx.
The exceptions are as follows:
| • | DistinctSum: you must use the DistinctSum() function when defining a measure with a DistinctSum aggregation type. |
| • | Calculated: there are a limited number of functions available for defining a measure with a Calculated aggregation type, which include Sum, Avg, StDev, StDevP, Var, VarP, Median, Mode, Min and Max. You can also use DistinctSum and AggregateIf functions with the Calculated aggregate function in expressions for measures. Other options may exist but have not been tested. |
When performing division functions using calculated fields, always use an 'iif' statement to trap a denominator that may equal 0. If you do not include this statement in the formula expression, Infinity displays in the cell when the denominator equals zero and the aggregation of the results will fail.
The measures expression builder makes it easier to create weighted averages. If you want to create a weighted average field to use in other measures, you can create the expression with the measures expression builder, then copy and paste it into the calculated fields expression builder.
See Result type in Working with analysis report expressions for more information.
You can select from the following operators to build your expression:
|
Some screen resolutions will not display all operators by default. If your screen does not display all the above operators, you can use the down arrow to the right of the bar to access the remaining tools, or increase the size of the expression builder window. |
