To access this page:
- In Data Workbench, display the Data page.
- Select Download Data from SAP in the page toolbar.
For step-by-step instructions on using this page, see the tutorials:
Download Data from SAP Using a Query
Download Data from Multiple Tables with a Join
In this topic:
Commands
Use these commands to create and edit queries or download data using the query. Note that not all users may have permissions to create or edit queries and/or download data. Permissions are set in the User/Group Management Page by a user with administrator privileges.
| Icon | Description | |
|
Refresh Dictionary | Updates the information stored in Data Workbench about the tables and data types. Active only when Edit or Create permissions are enabled for Queries. |
|
New Query | Opens the Query Designer to create a new query that extracts data from SAP. |
|
Download | Runs a selected query to download data from SAP. |
|
Edit Query | Opens the Query Designer for a selected query, allowing you to change fields and filters for the query. |
|
Delete Query | Deletes the selected query. |
|
Schedule | Click to open the Schedule Download Task dialog and schedule the download for a later time. |
|
Import Query | Imports a previously saved Data Workbench download query. The file extension for saved templates is QDownloadQuery. The file contains the template structure in a non-text proprietary format. |
|
Export Query | Exports a template to a file with a QDownloadQuery extension. The file contains the template structure in a non-text proprietary format. |
Queries
The Queries pane consists of three areas to manage queries:
Tag Pane
Displays the tag folder structure that can be used for increased management and organizational purposes. Access to specific tags depends on the permissions that have been assigned to your user ID. Tags can be managed in the Manage Tags page. User permissions can be managed by an administrator or in the User/Group Management Page. Click on a tag to display only queries associated with the tag. Drag and drop a query on a tag to assign the query to the tag. Use the right mouse button menu to manage tags (inactive on a tag where you do not have permissions.)
Queries Pane
Displays all available queries, either imported or created using the Query Designer. Use either the icons in the Commands pane or the right mouse button menu to act on queries. Note that not all users may have permissions to create or edit queries and/or download data. Permissions are set in the User/Group Management Page by a user with administrator privileges.
Right Mouse Button Menu
| Menu Item | Description |
| Refresh Dictionary | Populates the Data Workbench with the SAP table names from the SAP instance where the user is logged in.This step must be performed when using the Download Data from SAP features for the first time. Perform subsequent refreshes only when necessary. Active only when Edit or Create permissions are enabled for Queries. |
| New Query | Opens the Query Designer to create a new query that extracts data from SAP. |
| Download | For a selected query, runs the query to download data from SAP. |
| Edit Query | For a selected query, opens the Query Designer, allowing you to change fields and filters for the query. |
| Delete Query | Deletes the selected query. |
| Import Query | Imports a previously saved Data Workbench download query. The file extension for saved templates is QDownloadQuery. The file contains the template structure in a non-text proprietary format. |
| Export Query | Exports a template to a file with a QUploadTemplates extension. The file contains the template structure in a non-text proprietary format |
| Views | Allows you to toggle the view between a list that shows query descriptions and tiles (the default.) |
Query Details Pane
This pane displays details for a selected query using either the Description tab, or the Fields tab. Set a description using the Query Designer. The Fields tab displays a read-only summary of the fields involved in the query as well as any selection criteria used to filter data:
| Column | Value |
| Field | The name of the field as selected from the table in the Query Workspace |
| Table | The name of the table containing the field. |
| Output | Checked by default. Indicates whether the field will be included in the final Excel output. If unchecked, defined filters for the field will be used to select the data to download, but the field will not appear in the downloaded data output. |
| Sort Order | Reports the sort order selected in the Query Designer. |
| ID | Reports the sort precedence selected in the Query Designer. |
| Criteria |
The complete expression to filter data. This expression was built using syntax entered in the Criteria and Or fields in the Query Designer. To see the full expression, either enlarge the Data Workbench window, or hover over the Criteria field to display a tooltip containing the expression.
|
Query Designer
Use the Query Designer to build and edit queries that define the data to download to an Excel file. See Download Data from SAP Using a Query for detailed steps on creating a query and downloading data.
Before using the Query Designer for the first time, click Refresh Dictionary to obtain the data dictionary from SAP.
NOTE: The SAP GUI application typically asks for permission at several points during the process of creating a query. Click Allow in the SAP GUI dialogs, or change the Security Settings in your SAP GUI configuration to avoid the dialogs. It is possible that the SAP GUI pop-up is displayed behind the Data Workbench window, and therefore not visible. If the Data Workbench seems to be slow in responding, use ALT-TAB to check if there is a hidden windows waiting for input.
Query Description
Enter a description for the query to display in the Download Data from SAP tab after exiting the Query Designer.
Shared Queries
To create queries for multiple users, check the Share query with other enterprise users option. Other users can use a shared query to download data, but cannot edit the query. A shared query is displayed with a unique icon.
Query Action Buttons
Once the query has been designed, use the buttons at the top of the Query Designer to manage the query. When you have finished, click Close at the bottom of the Query Designer.
|
|
Button | Action |
|
Restore Links | Restores any automatically created links that were removed in the process of designing a query. |
|
Preview | Downloads twenty records and displays them in a table, allowing you to preview the data that your query generates. Note that the preview runs on the current state of the query in the designer, rather than the save state. |
|
Download | Allows you to run the query immediately to download data. |
|
Save | Saves the query to the same name without closing the Query Designer. |
|
Save As | Saves the query to a new name, allowing you to copy and edit a query if you want to make a new query based on a current one. |
Tree View
Displays the available SAP tables to select from in creating a query.
NOTE: As long as you have create or edit permissions for queries in Data Workbench, you can see data in any SAP table. This is consistent with the restrictions placed on SAP users by means of authorizations, user profiles or any other methods utilized by the SAP administrator. However, if an administrator wants to restrict access to table data for certain users, the best approach is to remove the create and edit permissions.
By default, tables are displayed with long names and organized under SAP Functional Modules. Click Show Short Names to view table short names organized alphabetically. Change the default setting for this display in the User Preferences dialog.
To display a table in the Query Workspace:
- Double click on the table
- Drag and drop the table
Options available for query building depend on the type of table. In particular, SAP imposes restrictions for cluster and pool tables. Data Workbench indicates table type using different icons.
|
Cluster Tables Only one cluster or pool table may exist in a query. A cluster or pool table may only be directly joined to one other table. |
|
Transparent Tables Fields from transparent tables can be used for filtering data are not selected for output and do not appear in the results. The 'order by' functionality is not available. |
|
Pool Tables Only one cluster or pool table may exist in a query. A cluster or pool table may only be directly joined to one other table. |
Due to the high volume of data contained in cluster and pool tables, a minimum of 3 joins must be used when joining a transparent table with a cluster or pool table.
Query Workspace
Displays tables selected from the Tree View and allows you to select fields to include in your download query. Pay attention to the field data types so that you can accurately enter filters for data in the Selection Criteria Workspace. Use the Legend to the right of the Query Designer window to help you understand the data types.
- To select a table field, click in the checkbox next to the field. The field is automatically displayed in the Selection Criteria Workspace where you can apply filters.
- To remove a table field, click in the checkbox to remove the check. The field is automatically removed from the Selection Criteria Workspace.
- To select or deselect all fields in the table, right click in the table title bar and choose Select All or Deselect All.
- To search for matching fields to create a join, right click in the table title bar and choose Suggest Links. For more information on joins and links see Managing Joins below.
Managing Joins
When a table is added in the Query Workspace, Data Workbench automatically generates joins based on foreign key relationships. To create additional joins, use the Suggest Links option on the right mouse button menu in the table title bar to open the Suggest Links dialog. However, note that sometimes fields with the same name do not result in valid joins.
To create manual joins, drag a field from one table to a field in another table. These fields do not have to be the same name but must be valid joins for those two tables in SAP.
To delete a join, right click and choose the Delete option.
The default join type is an inner join, but it is possible to set a left outer join for the table that was placed in the Query Workspace first. To set a left outer join, right-click on the link between tables and choose an All Records From... option. The link updates to display an arrow in the direction of the additional data included in the join.
An outer join can also be created in the Relationship Properties dialog. Right-click on the join, select Properties and check the Include all rows from this table option.
Selection Criteria Workspace
Displays fields that were selected in the Query Workspace. Use the Legend to the right of the Query Designer window to help you understand the syntax for selection criteria.
The Criteria and Or fields allow you to set filters for a query to identify the specific records you want to work with. For example, instead of viewing all the vendors for a company, you can view just suppliers from Germany by specifying criteria that limit the results to records with a country field set to "Germany". The value in the Criteria field for this example would be the expression "Germany". You can also use more complicated expressions such as "BETWEEN 2000 AND 7000". To add further expressions, use the Or fields. The final filter used for downloading data, will be a combination of all values found in the Criteria and Or fields using a logical OR operator.
Use the Sort Type and Sort Order to refine the way the downloaded data appears in the Excel file.
After entering criteria for the appropriate fields, choose Ascending or Descending in the Sort Type field, and then, in the Sort Precedence field, choose the sort priority compared to other fields in the downloaded data.
The order in which fields appear in the workspace table determines their order in the output files. To change the field order in a query, right-click on a field and select either Move Up or Move Down to swap the selected field with the one above or below. Use the Delete option to remove a field from the query.
| Column | Value |
| Field | The name of the field as selected from the table in the Query Workspace |
| Table | The name of the table containing the field. |
| Output | Checked by default. Indicates whether the field will be included in the final Excel output. If unchecked, defined filters for the field will be used to select the data to download, but the field will not appear in the downloaded data output. |
| Sort Order | Optionally choose either Ascending or Descending to determine the order of data in the final output. |
| Sort Precedence | Optionally choose an integer to indicate precedence of the field's sort order in the final output. |
| Criteria | An expression to filter data. Use the Legend to the right of the Query Designer window to help you understand the syntax for selection criteria. |
| Or columns | Additional expressions to filter data. All expressions will be combined using a logical OR operator. |
