How to Create and Use Variables in Queries in SAP BW/4HANA

How to Create and Use Variables in Queries in SAP BW/4HANA

How to Create and Use Variables in Queries in SAP BW/4HANA

Variables are a way to make your queries in SAP BW/4HANA more dynamic and flexible. They allow you to replace fixed values with user inputs or system values that can change depending on the context or the time of execution. Variables can also help you to simplify your query design and maintenance, and to improve the performance and usability of your queries.

In this blog post, we will explain what variables are, how they work, and how to create and use them in queries in SAP BW/4HANA. We will also show you some examples of queries with variables and how to save and use query variants.

What are variables in SAP BW/4HANA?

A variable is a parameter of a query that acts as a placeholder for a value that can be entered by the user or determined by the system when the query is executed. A variable can be used wherever a value can be used, such as in filters, formulas, structures, texts, exceptions, or conditions.

There are different types of variables that can be created for different purposes:

  • Characteristic value variables: These are variables that represent characteristic values and can be used wherever characteristic values can be used. For example, you can use a characteristic value variable to filter a characteristic by a user input or a system value.
  • Hierarchy variables: These are variables that represent hierarchies and can be used wherever hierarchies can be selected. For example, you can use a hierarchy variable to restrict a characteristic by a hierarchy that is chosen by the user or determined by the system.
  • Hierarchy node variables: These are variables that represent nodes in a hierarchy and can be used wherever hierarchy nodes can be used. For example, you can use a hierarchy node variable to filter a characteristic by a hierarchy node that is entered by the user or derived from the system.
  • Text variables: These are variables that represent texts and can be used in descriptions of queries, key figures, or structures. For example, you can use a text variable to display a dynamic text that is based on a characteristic value or a system value.
  • Formula variables: These are variables that represent numeric values and can be used in formulas or for selecting exceptions or conditions. For example, you can use a formula variable to calculate a key figure based on a user input or a system value.

Variables are defined for an InfoProvider and can be reused in multiple queries based on the same InfoProvider. Variables are stored in SAP BW/4HANA as master data tables, and they can be created and maintained using the web cockpit or external tools.

How to create and use variables in queries in SAP BW/4HANA?

To create and use variables in queries in SAP BW/4HANA, you need to use the Query Designer tool. The Query Designer allows you to drag and drop InfoObjects or reusable query elements from the InfoProvider screen to the query rows and columns. You can also edit the properties of the variables using the Properties pane.

Here are the steps to create and use variables in queries in SAP BW/4HANA:

  1. Open the Query Designer and select the InfoProvider that you want to use for your query.
  2. Click on Variables on the toolbar to open the Variables screen.
  3. Click on Create Variable to create a new variable, or select an existing variable and click on Edit Variable to modify it.
  4. Enter a name and a description for the variable, and select the variable type from the list of available types.
  5. Specify the reference characteristic for the variable, which is the characteristic that determines the possible values for the variable.
  6. Specify the selection option for the variable, which is how the user can enter or select values for the variable. You can choose from single value, interval, multiple single values, multiple intervals, etc.
  7. Specify the processing type for the variable, which is how the system determines values for the variable. You can choose from manual input/default value, replacement path, customer exit, authorization, etc.
  8. Specify any other properties for the variable, such as default value, ready for input, mandatory entry, lower/upper limit, offset/length unit, etc.
  9. Save and activate the variable.

To use a variable in a query, follow these steps:

  1. Drag and drop any InfoObject or reusable query element from the InfoProvider screen or from the Reusable Components screen to the rows or columns of the query.
  2. Right-click on the element that you want to use with a variable and select Restrict from the context menu.
  3. In the Restrict screen, click on Variable Selection on the toolbar.
  4. Select the variable that you want to use from the list of available variables and click on OK.
  5. Click on OK to apply the restriction with the variable.

To execute the query and see the results, click on Execute Query on the toolbar. You will see a variable screen where you can enter or select values for the variables. After you fill in the variables, click on OK to see the query result.

Here is an example of a query with a variable on the Sales Organization characteristic.

The query uses an InfoProvider named Sales Data that contains the following fields:

  • Sales Organization
  • Material
  • Customer
  • Quantity
  • Revenue

The query has a variable named Sales Org Var that is defined as follows:

  • Variable type: Characteristic value variable
  • Reference characteristic: Sales Organization
  • Selection option: Single value
  • Processing type: Manual input/default value
  • Default value: 1000

The query has a restriction on the Sales Organization characteristic using the Sales Org Var variable.

The query result will look something like this:

MaterialCustomerQuantityRevenue
M01C01500060000
M02C02500040000

You can see that the data is filtered by the default value of the variable, which is 1000. You can also change the value of the variable in the variable screen to see different results.

How to save and use query variants?

A query variant is a query for which variable values have already been selected. You can save and use query variants to execute queries with predefined values for variables, without having to enter or select them every time.

To save a query variant, follow these steps:

  1. Execute the query and fill in the values for the variables in the variable screen.
  2. Click on Save as Variant on the toolbar.
  3. Enter a name and a description for the variant and click on OK.

To use a query variant, follow these steps:

  1. Open the query and click on Open Variant on the toolbar.
  2. Select the variant that you want to use from the list of available variants and click on OK.
  3. The query will be executed with the values from the variant.

Here is an example of saving and using a query variant for a query with a variable on the Month characteristic.

The query uses an InfoProvider named Sales Data that contains the following fields:

  • Month
  • Material
  • Customer
  • Quantity
  • Revenue

The query has a variable named Month Var that is defined as follows:

  • Variable type: Characteristic value variable
  • Reference characteristic: Month
  • Selection option: Single value
  • Processing type: Manual input/default value
  • Default value: 01.2021

The query has a restriction on the Month characteristic using the Month Var variable.

To save a query variant with a different value for the Month Var variable, follow these steps:

  1. Execute the query and change the value of the Month Var variable to 02.2021 in the variable screen.
  2. Click on Save as Variant on the toolbar.
  3. Enter a name and a description for the variant, such as Feb 2021 Sales, and click on OK.

To use the query variant, follow these steps:

  1. Open the query and click on Open Variant on the toolbar.
  2. Select Feb 2021 Sales from the list of available variants and click on OK.
  3. The query will be executed with 02.2021 as the value for the Month Var variable.

Conclusion

Variables are a way to make your queries in SAP BW/4HANA more dynamic and flexible. They allow you to replace fixed values with user inputs or system values that can change depending on the context or the time of execution. Variables can also help you to simplify your query design and maintenance, and to improve the performance and usability of your queries.

You can create and use variables in queries in SAP BW/4HANA using the Query Designer tool. You can also save and use query variants to execute queries with predefined values for variables.

We hope that this blog post has helped you to understand how to create and use variables in queries in SAP BW/4HANA. If you have any questions or feedback, please leave a comment below.

Disclaimer: This content is generated by AI.

Tags: ,