Using Relational Calculations

Create calculations to make a report more meaningful by deriving additional information from the data source. For example, you create an invoice report and you want to see the total sale amount for each product ordered. Create a calculated column that multiplies the product price by the quantity ordered.

For relational data sources, if an expression is used in multiple reports or by different report authors, ask your modeler to create the expression as a standalone object in the model and include it in the relevant package. For information about creating filters in the package, see the Framework Manager User Guide.

You can add calculations to lists, crosstabs, and all other data containers. You can also add calculations directly to a page header, body, or footer. However, you must first associate a query to the page. For more information, see Add a Page to a Report.

Steps

1. In the Insertable Objects pane, click the Toolbox tab .

2. To create a calculation that will be applied to data, do the following:

   • Drag Query Calculation to the report.

     The Create Calculation dialog box appears.

   • In the Name box, type a name for the calculation.

3. To create a calculation that contains run-time information, such as current date, current time, and user name, drag Layout Calculation to the report.

4. In the Available Components box, define the calculation:

   • To add data items that are not shown in the report, on the Source tab , double-click data items.

   • To add data items that are in the report but not necessarily in the model, such as calculations, on the Data Items tab , double-click data items.

   • To add data items from a specific query , on the Queries tab , double-click data items.

   • To add functions, summaries, and operators, on the Functions tab , double-click elements.

   • To add a value that is derived from a parameter, on the Parameters tab , double-click a parameter.

     Parameters define prompts , drill-through reports , and master detail relationships .

     Tip: You can also type the expression directly in the Expression Definition box.

     For more information about creating expressions, see Using the Expression Editor.

5. Click the validate expression button .

Units of Measures

When creating calculations in Report Studio and Query Studio, you may encounter problems with the units of measure. For example, the calculation Cost*Cost returns the unit of measure * instead of a currency unit of measure. To avoid this problem, change the format of the corresponding column to obtain the desired unit of measure.