SmartList Builder 2018
Tables
All Excel Report objects are composed of at least one table. The main table is the first table selected for the Excel Report. All additional tables are linked to this table, either directly or indirectly through another table.
There are 4 types of table that can be added to an Excel Report, Data Connections, Microsoft Dynamics GP tables, Microsoft® SQL Server™, and Extender Resources.
Microsoft Dynamics GP tables are defined in a Microsoft Dynamics GP dictionary. This includes third party dictionaries as well as the main Microsoft Dynamics GP application dictionary. When you select a Microsoft Dynamics GP table, the metadata contained in the dictionary can be accessed. Information such as the display names of the table and fields and list items for list fields are automatically defaulted.
A SQL Server table is any table or view that is contained in your Microsoft Dynamics GP SQL Server databases or any SQL Server database that resides on the same SQL Server as your Microsoft Dynamics GP databases. This feature should be used to access tables that are not contained in a Microsoft Dynamics GP table. You can also use SQL Server views to create complex joins and queries that you cannot create with Excel Report Builder. Since there is no metadata for SQL Server tables, display names and list items are not defaulted.
A SQL Script table is a SQL select script that can be run against the current Dynamics GP company databases. Like SQL Server tables, this feature can be used to access tables that are not contained in a Microsoft Dynamics GP table and create complex joins and queries that you cannot create with Excel Report Builder. The SQL Script option can be used to query tables outside of the current Dynamics GP database as well if the database is specifically included with the table call. Since there is no metadata for SQL Script tables, display names and list items are not defaulted.
Data Connections are pre-defined SQL views for the most common fields and tables used in Dynamics GP. Since there is no metadata for SQL Server tables, display names and list items are not defaulted. However, most of this information is not required because it is already included in the view.
Extender Resources are Windows, Detail Windows, Forms and Detail Forms that have been defined in Extender. When you select an Extender Resource, information such as the display names of the fields, decimal places for numeric fields and list items for list fields are automatically defaulted.
|
If Microsoft Dynamics GP tables, SQL Server tables and Extender Resources are added to the report, the report must be published by a user with SQL administrator privileges. All users can publish reports that contain only data connections. |
To add a Microsoft Dynamics GP table as the main table:
| 1. | Select Microsoft Dynamics GP Table from the Add button above the Tables list to open the Add Table window. |
| 2. | Select the Product, Series and Table. |
| 3. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
To add a SQL Server table as the main table:
| 1. | Select SQL Server Table from the Add button above the Tables list to open the Add SQL Table window. |
| 2. | Select the Database and Table. Select the Views option to display views contained in the selected Database. |
| 3. | If the table or view that you have selected is contained in more than one Microsoft Dynamics GP company database, you can mark the Use Company Database checkbox. When this checkbox is marked, Excel Report Builder will use the table from the Microsoft Dynamics GP database that the user is currently logged in to. If this checkbox is unmarked, Excel Report Builder will always use the selected database. |
| 4. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
|
You can only add SQL tables if you have granted access for those tables to be used in SmartList Builder. |
To add a SQL Script table as the main table:
| 1. | Select SQL Script from the Add button above the Tables list to open the Add SQL Script window. |
| 2. | Enter the SQL Script. |
| 3. | Select Key fields for the table. There will only be one record displayed in the SmartList for each key field. |
| 4. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
|
Security must be granted database before this option will be available. See SQL Table Security for more details. |
To add a data connection as the main table:
| 1. | Select Data Connection from the Add button above the Tables list to open the Add Data Connection window. |
| 2. | Select the Product, Series and Data Connection. |
| 3. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
To add an Extender Resource as the main table:
| 1. | Select Extender Resource from the Add button above the Tables list to open the Add SQL Table window. |
| 2. | Select the Resource Type and Resource Name. |
| 3. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |