How to set parametric WHERE clause for SAP

1. Abstract

With the Theobald Board Connector, a SAP-certified middleware is available to load data from SAP systems (ERP/BW) to Board if the database of the system cannot or may not be addressed directly (e.g. HANA via ODBC). More information can be found here:

https://help.theobald-software.com/en/board-connector/

In the simplest case, data is read from SAP tables, i.e. the connector creates a corresponding SQL statement that is sent to the SAP system. Other possibilities are reading Cube/Query data or hierarchies, BAPIs, and some more.

This article describes how to restrict the data to be read via WHERE statements by using corresponding parameters in procedures.

2. Context

Normally, one will want to restrict data according to certain aspects; be it to limit the amount of data to be read or to have to satisfy other aspects (such as language, security, …). A corresponding WHERE condition can easily be defined in the respective extractor. Under certain circumstances, however, you may want or need to be able to control such a condition "from the outside" (in our case, from Board). Such a control can be obtained by calling the corresponding DataReader from a procedure; here the call can be parameterized.

3. Content

3.1 Simple table extraction without restriction

Let’s start with a simple example—a table extractor to read descriptions of Material master data.

The SAP table containing the required information is MAKT; so we would first of all create an appropriate table extractor, let’s call it “MAKTX_LOC”. The appendix “LOC” means that we want to read the data localized, i.e. language-dependent.

The following table fields need to be extracted (tick those you need).

A Live preview could deliver something like this:

Usually, you would want to load the description of an entity in one language only. If you were working on a localization of entity members, however, it would be necessary to distinguish between different languages.

3.2 Table Extractor with WHERE Clause

When we load entity members’ descriptions we would restrict the language to the code that reflects our development environment. In our case that will be “E” for English.

To define the respective restriction in the Extractor, we’ll use the Tab “WHERE Clause”:

Note that you need to place a blank between the (table~) field name and the operator as well as between the operator and the value to compare (which has to be enclosed in inverted commas).

3.3 Parametrizing the Extractor

3.3.1 Creating the Parameter

Whenever we want to set values to compare to in WHERE Clauses dynamically, we need to set up appropriate parameters in the Extractor. This will be done via “Edit runtime parameters”:

We’ll need to set up a new Scalar:

Add Scalar

Edit the Name of Scalar

Set the Type of Scalar

In our case the Scalar’s name is “SPRAS” and its type is “String”.

For further information on this topic please refer to the following link in Theobald Software’s help section:

https://help.theobald-software.com/en/board-connector/table/where-clause#using-runtime-parameters-in-the-where-clause-editor

3.3.2 Test the parameter in a call (Run in Browser)

In order to make use of the defined scalar (our parameter SPRAS for the language) we need to update the WHERE condition accordingly. That is achieved by adding the prefix “@” (at) to the scalar’s name in the clause:

Be aware that from this moment on you won’t be able to load a preview of the data; you’ll have to run the extraction as “Run in Browser” to make use of the parameter. This applies to the preview in Board later on as well. That’s why we have to check the correct outcome here.

So, save the extraction and click on “Run”.

Your screen should depict something like this:

You can see that the parameter is available but currently not assigned to any value. To do that switch to “Custom” and put in a value for the scalar.

The caller-URL changes accordingly and the next step will be to run the extraction using the browser as output.

The result should be looking like this:

3.4 Calling the DataReader in a Board Procedure

The DataReader is created like any other SAP DataReader; the parametrization will be done when the Reader is called by a procedure.

3.4.1 Creating the DataReader

Any SAP DataReader using the Board Connector will use the according Data Source. The next steps are quite similar to creating a SQL DataReader.

Connect to the according source:

Create the Mapping between SAP field and Board entities / Cubes. In our case, the entity “SAP Material” does not contain any member yet, so this is set to “ADD”, as well as the text for the localization.

Since this table (it’s one of the oldest SAP tables) uses a one-digit value for the language, we have to change that to a two-digit value to be used by board correctly. In our case, DE and EN will suffice.

Note that you can’t see any preview values here since the extraction is parametrized as mentioned before.

Our Reading process is now set up but can’t be tested directly – because it needs to be parametrized, too.

3.4.2 Using the Parameter in the Call

Create or change the procedure to read SAP data.

Chose “SAP Data reader” for the step’s action and select the newly created Protocol:

The name-value pair for the parameter is set in the “Parameter” section of the call; please note that you will have to put “&” in front of the parameter’s name and check that “use current selection” is ticked on.

You don’t need to enclose the parameter’s value in inverted commas. If you want to make use of multiple parameters you would concatenate them. The syntax would be

&<Param1>=<value1>&<Param2>=<value2>

Now, our procedure could present itself like this:

That’s it!

Keep the following in mind:

• The extraction’s parameter (the scalar) is used with the prefix “@” in the WHERE Clause and separated by blanks (spras = @SPRAS), whereas
• The DataReader Parameter uses “&” instead with no blanks and no inverted commas or quotes (&SPRAS=E).

3.5 Conclusion

Reading SAP data via the Theobald Board Connector can be configured quite easily so that parameters can be used. This reduces the development effort on the one hand and the support effort on the other. However, you should make sure that such extractions are well documented so that a developer can still recognize later at which points parameterizations have been made and for what reason. Of course, this is especially true if parameters are used to impose time restrictions on the data read.