The CData Excel Add-In for SAP ERP dynamically retrieves schemas for SAP tables, queries, and function modules at run time. However, since it can be inefficient to retrieve the metadata for the entire SAP system, the add-in also applies default filters to represent only a slice of your SAP system.

Below is an explanation of how the add-in models each type of SAP object. How the add-in determines what to display is highly customizable; see Accessing SAP Entities for more information on selecting the SAP objects you want.

SAP Tables

SAP tables are listed as views. This is because SAP strongly discourages directly making changes to SAP tables. Instead, function modules such as BAPIs should be used for making changes to table data. See Function Modules below for more on that.

Table data is retrieved from SAP using either the RFC_READ_TABLE or Z_CUSTOM_READ_TABLE function module if you have it installed. These give access to basic SQL support, but more complex queries such as joins and group by must be handled locally. More complex queries will be handled automatically by the add-in as long as SupportEnhancedSQL is set to true.

See Using a Custom Read Table Function for more information on circumventing limitations with RFC_READ_TABLE.

SAP Queries

Just like tables, you can also work with SAP queries as relational views. By default they are turned off to try and avoid cluttering the add-in with too many options. They can be turned on by using the QueryMode connection property.

Unlike tables, SAP queries may require certain values to be specified as inputs. You can specify these query inputs as part of the predicate in the SQL. For instance:

SELECT * FROM Z_SAP_QUERY WHERE InputName='x'

All SAP query views will include a Variant column that you can also input as part of the projection.

Be aware that values that are only inputs in an SAP query will be reflected back in the results as the same value that was specified. This does not actually indicate that SAP returned the value that way. It is just something that is required to keep the SQL statement valid.

Function Modules

Function modules in SAP can be used for almost any task. These include the very RFC calls that the add-in makes such as RFC_READ_TABLE and function modules that modify data such as BAPI_PO_CREATE.

You can execute a function module directly as a stored procedure. SAP function modules accept and return primitive values and complex structures; the corresponding stored procedures accept and return SQL types and tables. For instance:

EXEC STFC_CONNECTION

For a more complex example, try RFC_READ_TABLE:

  EXEC RFC_READ_TABLE @QUERY_TABLE='MARA', @FIELDS='FIELDS#TEMP', @ROWCOUNT=1

In the above example, the RFC_READ_TABLE function module is executed to return the MARA table. The QUERY_TABLE and ROWCOUNT parameters are passed in as primitive values. In this function module, FIELDS is an input table. To provide this input, you can populate a temporary table with the #TEMP syntax and INSERT statements. For example:

  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('MANDT');
  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('MATNR');
  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('ERSDA');
  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('ERNAM');

Alternatively, JSON or XML can be used to submit tabular data for environments where the #TEMP tables would be difficult to use. For example:

  EXEC RFC_READ_TABLE OPTIONS='{ \"TEXT\": \"TABNAME LIKE ''MARA%''\" }', QUERY_TABLE='DD02L', ROWCOUNT='1', ReturnTables='DATA'

Customizing Stored Procedure Schemas

Instead of using the dynamic, in-memory schemas, you can use GenerateSchemaFiles to save static schema files that are easy to customize. A common use case is to remove extra unwanted inputs and outputs that the add-in may report for a stored procedure.

In SAP, there is nothing in the metadata that distinguishes tables in function modules as either inputs or outputs. Since there is no way to know which tables are actually used for inputs and which are used for outputs, the add-in reports the tables as being available for both inputs and outputs.

To generate the schema file, set GenerateSchemaFiles to "OnUse" and call the stored procedure. Also, set the Location property to the folder where you want the schema to be output.

To modify the schema file, open the generated schema file in the Location folder with any text editor. Then find the <input> or <output> tags that are not needed and remove them. Refresh the connection in the add-in to pick up the schema and your changes.