Use SQL and Business Function (OLAP) Snippets

Overview

In the Jet Data Manager, you can create commonly used pieces of SQL code and parameterize them as SQL Snippets.  This allows frequently used pieces of functionality to be saved once and then easily deployed across different tables or fields without recreating all of the SQL functionality.

SQL Snippets can be used with:

• Field level transformations
• Views
• Stored procedures
• User defined functions
• Script actions

Manage Snippets

1. In Jet Data Manager, on the Tools menu, click the Snippets button.

   

2. From the Snippets window, you can Add , Edit , Organize, Import , and Export all snippets regardless of the type. To further streamline the snippets feature business functions have been renamed OLAP snippets.

   

Create a SQL Snippet

1. On the Snippets windows, click the Add button.

   A window with the following options will appear:

   

2. Type a Name and Description for the SQL Snippet.  The Description is optional but allows other users know what the Snippet will do.

3. Type or paste the SQL command into the Formula section.

4. For any variables (in this example FieldName) highlight the variable and click Add Parameter .  This will add the highlighted text as a parameter name in the Parameters section.

   

5. Change the Type to match what the variable represents.  The available options are: Table , Field , Database , User Defined Function , Stored Procedure , and Value

6. Click OK to save the SQL Snippet.

Edit a Snippet

1. On the Snippets window, select the snippet you wish to edit and then click the Edit button.

2. Adjust the Snippet as needed.

3. Click OK to save the SQL Snippet.

Delete a SQL Snippet

1. On the Snippets windows, select the snippet you want to delete and then click the Delete button.

2. Click Yes to permanently delete the SQL Snippet(s).

   

Implement a SQL Snippet in a Project

1. Right-click the field to add the SQL Snippet to, go to Add SQL Snippet Transformation and select the desired SQL Snippet from the available list.

   

2. Drag the desired field(s) from the Data Fields pane on the right and drop the field on the Object Name/Value column for the desired variable. The Object Name/Value column and Variant column will populate automatically.

   

3. Click OK.