Create a Query Calculation

If you are using a IBM® DB2® data source, the subtract operator is invalid if you combine the datatypes timestamp2 and packed decimal.

When creating an expression that for use in a double-byte environment, such as Japanese, the only special characters that work are ASCII-7 and ~ -- || - $ ¢ £ ¬.

Procedure

  1. From the Toolbox tab toolbox tab, drag Query Calculation to the report.
  2. In the Name box, type a meaningful name for your expression.

    For example, if you are calculating the difference between 2007 revenue and 2006 revenue, you could name your expression 2007 - 2006 Revenue.

  3. Click the type of calculation that you want to create, and select the hierarchy or dimension that contains the data that is the focus of your calculation.
  4. In the Available Components pane, define the calculation:
    • Specify how you want to view the available data items in your data package by clicking the view member tree button view member tree icon or the view package tree button view package tree icon.
    • To add data items that are not shown in the report, on the source tab source tab, double-click the data items.
    • To add data items that are in the report but not necessarily in the model, such as calculations, double-click the data items on the data items tab data items tab.
    • To add functions, summaries, and operators, on the functions tab functions tab, double-click elements.
      Tip: To filter the visible functions, summaries, and operators in the Available Components pane, click the filter button filter icon and filter by the function type, what the function returns, or what the function acts on.
    Tip: You can also type the calculation directly in the Expression Definition box. When typing date values, ensure that the date format is correct for your database type.
  5. Click the validate button validate icon.

    Any validation errors appear on the Errors tab of the Information pane.

    Tip:
    • To show or hide the Available Components pane, click the blue arrow to the right of the pane.
    • To show or hide the Information pane, click the blue arrow above the pane.
    • To copy and paste expression components in the Expression Definition pane, use the copy button copy icon and the paste button paste icon.