Using Extender to Create SQL Views

Extender is a powerful tool—probably more powerful than you realize. Besides being used to create user-defined windows without any programming, you can use it to create your own SQL Server views. Think of Extender as a way to extend your SQL skills.

SQL Views

An SQL view is a way to look at one or more tables using tools such as SQL Server Management Studio, SQL Server Reporting Services, Report Builder, Microsoft Access, SmartList Builder and Excel Refreshable Reports. You can use an SQL view to restrict what data are shown by limiting columns, rows or both. For example, you can use a view to restrict what transactions can be displayed from the Sales Transaction History table, so the user doesn’t have to use the restriction capability in SmartList.

Look at the following example table called SalesExample.

You could create a view that limits the data based on the SOPTYPE—you could call that view SalesInvoices if the SOPTYPE value is 3. You could create a view that only displays the SOPNUMBE and CUSTNAME columns—you could call that view SalesCustomerTransactions. You also could create a view that only displays rows where the SOPTYPE is 2 and only displays the SOPNUMBE and CUSTNMBR.

Once you have a view, you can make it available to users of various tools to create reports. By limiting the information in the view, you can make life easier for the report creator or user by saving them the steps to limit the data. You also can use views to hide data users shouldn’t see.

Creating Views Using Queries

The normal way to create views is to create queries using SQL Server Management Studio. For example, to create the view above called SalesInvoices, you’d type something like this in a query window:

As you can imagine, views can be much more complicated than the above example. Nevertheless, for those experienced with SQL, creating views can be a great way to pull data together from multiple tables and display it easily.

Creating Views Using Extender

Extender also allows users to create views. For those using Extender windows who want to use them in reports, it is much easier to create views using Extender.

For this example, we’ll create a view that does not include any Extender windows.

Note that you must be logged into GP as ‘sa’ or a user with appropriate permissions in GP and SQL Server.

Go to Tools | Extender | Extender.

You should see “Views” as an option. Click “Views.”

Click “New” on the toolbar.

Type a unique ID for the view. By default, the ID becomes the name in SQL Server, so the ID should not have any spaces or illegal characters.

This example creates a view called ZSALESINVOICES. You may prefer having a prefix distinguishing custom views from those created by Microsoft.

Once you’ve entered the ID, description and SQL object name, it’s time to select the tables.

Click “Add,” then select the drop-down list.

For now, select “Dynamics GP Table.”

Select the columns by checking the boxes.

When you’re done, you can click the Preview button.

If the view is as you want it, click “Save” on the toolbar. This creates the view in the database. You can also click the “Options” menu (not the button) and select Display SQL, which displays the transact-SQL code. You can copy and paste the code in a message to your sysadmin or into a SQL query window.

Unfortunately, WHERE clauses limiting the data as discussed above must be added to the view using SQL Server Management Studio.

Using the View

As mentioned above, there are several tools that can use the SQL view. This example will demonstrate how to select the view in SmartList Builder.

Click the + button to add a table and select SQL Server Table. (Note the appropriate security to the table must be granted.)

After selecting the view and entering the information about it, select the columns you want to display, as shown here.

For more information or assistance on this topic, contact our Microsoft Dynamics GP support center at gpsupport@bkd.com.

Leave a Reply

Your email address will not be published. Required fields are marked *