Setting Up Basic Features

Custom Analytical Queries is a tool used for reporting and analysis. Although Custom Analytical Queries is an easy-to-use tool, it is aimed at key users rather than end users.

Prerequisite: The Business user/Key user requires the SAP_BR_ANALYTICS_SPECIALIST role to access the Analytical Query Designer.

This tool transforms and organizes raw data delivered from business documents into a meaningful grid. It abstracts the process of writing structured queries in such a way that you don't have to understand the query language or the technical details.

Custom Analytical Queries provide the fields required to design a query. You select the required fields and set filters for your query. You can add restricted measures, and calculated measures, and preview the query results.

In the following, the features of the Custom Analytical Queries tile are explained.

When the Custom Analytical Queries tile is started, the landing page displays and shows the available ABAP View names of those consumption views which contain an Analytical Query. The number of queries shown depends on your authorizations.

Either choose Copy or create to create a new Analytical Query.

If you choose Create, the New Query dialog appears. Here, you specify the technical name of the Analytical Query.

The query name will be automatically prefixed with ZZ1 or ZZ9. The technical name you specify here, is the ABAP View Name of the Consumption View of the Analytical Query to be created.

As the data source for which the query has to be defined, an Interface View has to be used.

The General tab shows the basic details of the Analytical Query. You can enter a meaningful name for the query in the Label field.

Max. Processing Effort:

If large amounts of data are stored, certain queries can retrieve large result sets. With the help of this setting the effort for a query and with this the waiting time for the query result can be defined. You can decide what has priority here: would you like to restrict the query effort or would you prefer to have a result in spite of longer processing and response times? The effort for Analytic Manager depends on the size of the result set and on the complexity of the query.

• Default: Normally the default value is Unlimited.
• Low: This mode is suitable for simple queries with small result sets.
• High: This mode is suitable for simple queries with large result sets or for complex queries with small or medium-sized result sets.
• Medium: This mode is suitable for simple and complex queries if the size of the result set is situated between low and high.
• Unlimited: This mode returns the query result regardless of the size of the OLAP effort.

Zero Suppression is used to suppress rows or columns containing zeros. You can control on which axis the zero suppression needs to be applied.

If you choose the Advanced User Settings icon in the upper right, you will find the name of the SQL Runtime View which is always automatically created.

On the Field Selection tab, the available fields from the Data Source (Interface View) are shown. You can add these fields (Dimensions and Measures) from the Data Source as needed.

The fields in the Available Fields table represent the Data Source fields. In the Available Fields table, choose Selection to add the field in the Selected Fields table. The field in the Selected Fields table represents the query field.

The Display tab has several functions.

To change the position of a field, you can select the query field of your choice and choose Up or Down.

TheOverride Label field enables you to change the default label inherited from the Data Source and to enter the label name of your choice in the Override Label field.

TheDisplay option allows you to hide or display a field in the result set. By default, the Display option appears enabled for all the Dimensions and Measures.

The Display Result option allows you to view the aggregated result when the query is executed. By default, the Display Result option is unchecked.

The Dimension Format option enables you to set the dimensions format in the form of Key, Text, Key and Text, or, Text and Key when the query is executed.

• Text and Key: The field's text is displayed before the key.
• Key and text: The field's key is displayed before the text.
• Key: Only the key is displayed.
• Text: Only the text is displayed.

By default, the dimension format is set to Default - that is, the format is inherited from the underlying Cube.

The Sorting option enables you to sort the dimension values either in ascending or descending order. For example, if the dimension format is set to Key, and you choose to sort the values in ascending order, then the values are arranged systematically in numerically ascending order. Additionally, if the dimension format is alphanumeric, the values are sorted in the lexicographical order.

The Axis option enables you to categorize the dimensions and structures (Measures and Characteristic Structure elements) as Rows or Columns in the preview.

If you select "Include Unbooked Data" you will also see the dimension value listed in your report even if no data was booked to it.

The Enable Hierarchy option appears only if the selected query field supports hierarchies. Choose the option to enable the hierarchy and select a hierarchy of your choice in the Cost Center Hierarchy field.

The Expand Level option is available only if you have enabled the Hierarchy option. This option enables you to set the hierarchy levels to be drilled down for a hierarchy. The default value is three.

For measures in the query you can select:

• Decimals: You can choose the decimal value between 0-9. Choosing this value only shows the specified number of digits in the result. For example, if the resulting value is 100.123456, choosing the decimal value as 3, displays the result as 100.123.
• Scaling: You can choose the scaling factor between 0-9. Choosing a value scales the resulting number by the specified factor. For example, if the resulting value is 12,000,000 and choosing the scaling factor of 6, displays the result as 12.

Reverse Sign: Enabling this checkbox will reverse the sign of the values. This option is supported for measures only.

You can create a restricted measure on a measure in the Data Source.

Use Restricted Measures to define a field that is filtered based on a specific condition. To define a new restricted measure, choose Add → Restricted Measure on the Display tab and define include or exclude filters on one or more dimensions. All the dimensions in the Data Source can be selected.

The upper example shows the set up of a further restricted measure but this time including a customer-defined variable for the Category field.

When the variable was set up in the Restricted Measure, you can enter a default value for the variable on the Filters tab.

As a consequence, the end user will find the default variable value in the filter section of the executed query but is also allowed to enter a different value for the variable.

Choose Add → Calculated Measure, then choose Edit to define the calculation logic for a Calculated Measure.

The editor provides you with elements, operators, and predefined analytical functions to define an expression.

Choose CTRL + Spacebar to obtain assistance with completing the expression.

The assistance provides you with the list of functions along with the syntax and also the user input filters and measures along with their types. When you choose a function from the assist list, the pre-filled syntax of the function appears in the editor.

The predefined Analytical Functions are as follows:

• NDIV0(x): The output of this function is 0 if divided by 0 else the output is the value of the division. This is used to avoid displaying an error message or to be able to calculate further with a defined result.

  Example: NDIV0(4/0) is 0; NDIV0(4/2) is 2.

• NODIM(): This function returns a numerical value and suppresses units and currencies.

  Example: Consider the two quantities Profit and Revenue with their dimensions Amount and Integer respectively. In specific conditions, if it is required to add or subtract these quantities then it is not possible to perform this action using standard functions as their dimensions are different. In such cases, you can use the NODIM function. NODIM(Profit) + NODIM(Revenue).

• NOERR (<Expression>):  This function returns 0 if the calculation of <expression> leads to an arithmetical error. Otherwise, the result is the value of the expression. It is used to avoid error messages, or so that you can continue to calculate with a defined result.

  Example: NOERR(SQRT(-1)) = 0.

• HRY_NODE_SIGN_VALUE( <element name>): This function derives the sign of a node. The measure can be multiplied with the result of this function. The parameter of this function specifies the dimension in which the display hierarchy should be used. The hierarchy view must have a field that indicates for which nodes the sign should be reverted. If in a query the sign of a measure should be reverted according to the setting of the node in the display hierarchy, then a formula must be defined.
• SQRT(): This functions returns the square root value of the select measures.
• SUMGT(operand): This function returns the overall result of the operand.
• SUMRT(operand): This function returns the query result of the operand.
• SUMCT(operand): This function returns the result of the operand to all rows or columns.

See documentation for more: https://help.sap.com/docs/ABAP_PLATFORM_NEW/ed0e11412f9841e7ac5cd9a6799368d4/cc4f03e7e20a4542bd8c19e9b70ae039.html?q=Runtime%20Extensibility

• Case Expression: The case when function provides a case when...then...else structure to allow for conditional execution.

  Example: CASE WHEN <"Volume"> > 1000 THEN 1 ELSE 0 END. Returns the measures if its volume is greater than 1000.

• ABS: The ABS function provides the absolute value of any field in the argument.
• CEIL(operand): Returns the smallest integer number not less than the operand.
• FLOOR(operand): Returns the largest integer number not greater than the operand.
• DIV(operand1, operand2): Returns the integer part of the division of operand1 by operand2.
• MOD(operand1, operand2): Returns the integer remainder of the division of operand1 by operand2.

Additionally, you can set some more fields in measures of the data source:

• Decimals: You can choose the decimal value between 0-9. Choosing this value shows only the specified number of digits in the result.

  For example, if the resulting value is 100.123456, choosing the decimal value as 3, displays the result as 100.123.

• Scaling: You can choose the scaling factor between 0-9. Choosing a value scales the resulting number by the specified factor.

  For example, if the resulting value is 12,000,000 and choosing the scaling factor of 6, displays the result as 12.

The figure above gives an example of how the Analytical Function SUMGT<Operand> works.

The figure above shows the use of the Analytical Function SUMGT<Operand> in the exercise scenario.

This feature enables you to define certain parameters or conditions to filter the results of a query.

You can apply filters only to dimensions and user input filters.

The different options available for filters are as follows:

• Fixed Values:

  Choosing fixed values defines a permanent filter value. End users cannot reconfigure this value.

• User Input Values:

  Upon query execution, this option enables end users to choose a value that filters the result set.

The following settings are possible for User Input Values:

• Selection Type:

  This option enables you to enter a single value, a value range, or a hierarchy node option to define the filter condition.

  The hierarchy node option appears only if a field supports hierarchy.

  The hierarchy node option displays the hierarchy tree in the User Input Filter screen.

• Multiple Selection:

  This option enables the end user to choose multiple values as input during query execution.

• Mandatory:

  This option ensures that the end user must make an entry.

• Variable Sequence:

  This option enables you to maintain the order of the parameter. During execution, the fields with variable sequence defined will take the first priority to the fields that have no value maintained for the same.

• Default Value:

  The value that you enter in this field appears in the User Input Value field and is displayed to the end user.

After you have published the query, you can preview the results.

On the Query information tab, you will find the 2C name of the Analytical Query.

Using the Query Browser, you can check the created query for included fields and the annotations of the query.

You can also derive the technical name of the Analytical Query from the SQL View Name (as previously described).

Using the Open for Analysis button, the business user can open the Analytical Query in the Web Dynpro template and use the Navigation Pane to adapt the report as needed.

Either from the Custom Analytical Query Preview or from the Open for Analysis button in the Query Browser as well as from the Show Content button in the View Browser, the business user can save the Analytical Query as a tile and hereby decide to which group in the SAP Fiori Launchpad the new tile has to be assigned.