Creating a Query and Including Exceptions

Objectives

After completing this lesson, you will be able to:

  • Create a query and include exceptions

Exceptions

You can highlight deviations of key figure values in a query result using exceptions.

When there is a deviation from a predefined threshold value or intervals defined in the query, key figure values and the related characteristic values are displayed in different colors when the exception is active.

Creating an Exception

  1. In BW Query Designer in SAP HANA Studio, choose the Exceptions tab.

  2. Right-click in the Exceptions screen area to open the context menu, and choose New Exception.

  3. On the Exception Properties screen, create the exception definition.

General Properties

On the General tab of the Exception Properties screen, set the description, the default status, and one or more exception values.

Select Exception is Active to set the exception’s default status, as shown above. When this option is selected, the exception is applied as soon as you execute the query.

Use exceptions to evaluate one or all key figures in your query definition. To use exceptions on specific key figures in your query definition, you must define an exception for each key figure in question.

Alert Levels and Exception Styles

You can set nine different alert levels using the colors red, yellow, and green. There are three shades of each color, with the rank of exceptions ranging from 1 for Good 1 to 9 for Bad 3:

  • Red: Bad 1, 2 and 3

  • Yellow: Critical 1, 2 and 3

  • Green: Good 1, 2 and 3

The nine different alert levels are assigned to the SAPExceptionLevel1 … 9 styles in SAP Analysis for Microsoft Office. You can change the exception styles, for example, font and background color in the Home tab of Microsoft Excel:

  1. Open the context menu of the selected style in the ribbon and choose Modify.

  2. Save style changes by saving the Analysis workbook.

  3. Save colors as a style set.

  4. Apply other style sets by choosing the Analysis tab and selecting Styles.

Thresholds

You can include all values or specific intervals, for example, only those falling below the threshold value in the red area, in the threshold value catalog.

When key figure values are on the border between two alert levels, the highest alert level rank is used. An example of this is when there is an alert level of Between 0 and 1000, using Bad 3, and another alert level of Between 1000 and 5000, using Critical 3, and the key figure being evaluated has a value of 1000. The key figure value of 1000 is the common point between the two alert level ranges. In this case, the value will be assigned the color used for Bad 3 because it has a higher alert level rank.

Exception Definition

On the Definition tab of the Exception Properties screen, configure the exception so that you can:

  • Evaluate the exception against one or all of the key figure elements in a key figures structure and/or against the elements in one or two characteristic structures. Remember: A query can have a maximum of two structures, either one key figure structure and one characteristic structure, or two characteristic structures.

  • Determine whether the exception is evaluated before or after any local calculations.

  • In the Is Displayed in Data Cells / Characteristic Cells screen area, control the cells in your report that will receive color for an alert level.

Exception Is Displayed in Data Cells

Choice of Structure ElementsResult
Evaluated ElementApply the alert level color to the structure element chosen for exception evaluation.
All ElementsRegardless of which structure element is being evaluated in the exception, apply the alert level color to all structure elements.
Given ElementEvaluate one structure element but apply the alert level color to a different structure element.

Exception Is Displayed in Characteristic Cells

Characteristic Cell ChoiceResult
RowsApply the alert level color to the characteristic values in the row where the exception occurs.
ColumnsApply the alert level color to the characteristic value in the column where the exception occurs.
Rows and ColumnsApply the alert level color to the characteristic value in both the column and row where the exception occurs.

Exception Cell Restrictions

On the Cell Restrictions tab of the Exception Properties screen, specify whether the exception applies to both detail and result values, or to detail values only, or to the result values only.

In the Cell Restrictions section, choose Add to define an operator for each characteristic in the query definition, and a value in case of the operators Fixed Value and Level.

In the Standard operator for all characteristics not listed field, you specify whether the exception should affect all rows or only result rows in the characteristics that you have not defined a restriction for in the lower screen area.

The following table presents the results for available cell restrictions operators and shows the corresponding operators in SAP Business Objects Analysis:

Cell Restrictions Operators

BW Query Designer: Exceptions: OperatorsResultSAP Analysis for MS Office: Conditional Formatting: Operators
EverythingThe exception applies for every drilldown state of the characteristics with no restriction on cell context (detail values and result values).All Members and Total
TotalsOnlyThe exception affects only the aggregated values of the characteristics (result values only).Total
EverythingExceptTotalsThe exception affects all values except for the aggregated values of the characteristic (detail values only).Members
FixedValueThe exception applies only to a specific characteristic value. You can also use a characteristic value variable../.
LevelThe exception is valid for a particular hierarchy level of the characteristic only.Level

Exceptions

In the following demonstration, you learn how to define Exceptions and how they improve the clarity of a report.

SAP Analysis for Microsoft Office: Conditional Formatting and Exceptions

In SAP Analysis for Microsoft Office, you can:

  • Define a new Conditional Formatting at workbook level
  • Activate and deactivate Exceptions defined in the BW Query Designer
  • Edit the format of Exceptions defined in the BW Query Designer
Note

If a cell is affected by both the Conditional Formatting and the Exception, the setting of the Conditional Formatting overrides the setting of the Exception.

Log in to track your progress & complete quizzes