Assigning custom SQL views to data grids

A data grid is based on a SQL view. A SQL view is a stored query of one or more database tables that results in a virtual table. The data retrieved by a view is not stored in the database. Instead, the SELECT statement is stored. The virtual table is made up of the result set retrieved by the SELECT statement. In effect, every view is a filter of the table data referenced in it, and this filter determines both the columns and the rows of the referenced table.

You customize a SQL view for a data grid by modifying a default view. A custom view retrieves the columns for the default view as well as any columns you want to add.

  1. Do not change the SQL script of a default view. Your changes will be overridden when you upgrade to a newer release of Trimble Web Products. See Creating a custom SQL view for a best practice.

  2. Data grids must include certain columns to populate properly. Do not remove required columns from the SQL SELECT statement. Required columns vary for different grids, but the first four to six columns of any grid should not be removed. Generally, customize a SQL view to add columns, then configure the grid to remove columns.

Creating a custom SQL view

A default view for a data grid contains a predefined list of columns. You can use standard grid configuration options to remove these columns and re-add them later. However, you cannot use these options to add any columns to a grid that are not part of the default view. To include columns not included in the default view, you can create a custom SQL view. You customize a SQL view for a data grid by modifying the default view.

  1. Do not change the SQL script of a default view. Your changes will be overridden when you upgrade to a newer release of Trimble Web Products. Use the procedure below as a best practice for creating a custom SQL view.

  2. Data grids must include certain columns to populate properly. Do not remove required columns from the SQL SELECT statement. Required columns vary for different grids, but the first four to six columns of any grid should not be removed. Generally, customize a SQL view to add columns, then configure the grid to remove columns.

  1. As a courtesy, this topic provides instructions for using Microsoft SQL Server Management Studio. The information included here is relevant to Microsoft SQL Server Management Studio Version 11.0.2100.60, using a Windows 7 operating system. If you are using a later version of Microsoft SQL Server Management Studio or a different operating system, you should consult the vendor’s user documentation for instructions.

  2. With new releases of Trimble Web Products, default SQL views may be updated. Customized views may not always automatically incorporate these updates. The procedure below describes how to create a custom SQL view that will automatically incorporate changes to the default SQL view upon which it was based.

Use this procedure to create a custom SQL view.

  1. Open Microsoft SQL Server Management Studio.

  2. Connect to the database.

  3. Create the view’s query from an existing view.

    1. Locate the name of the view you want to customize.

    2. Right-click the view name and select Script View as > CREATE To > New Query Editor Window.

    The view’s query is copied to a new window and is available to be edited.

  4. At the top of the query, replace the name of the existing view with the name of the custom view.

    Note: It is recommended that you append the existing default view name with your own custom identification. Using this naming convention, all related view names will be listed together in Microsoft SQL Server Management Studio.

    This example shows the AOEScrollDriverView renamed as AOEScrollDriverView_DRVPhone.

    image8

    image9

  5. Revise the existing SELECT script.

    This example shows the addition of two columns to the default view AOEScrollDriverView.

    image10

    Note: To see a list of columns available for addition to the view, open the Columns folder under the appropriate table.

    image11

  6. Test the SELECT statement.

    Note: Select and execute only the SELECT/FROM command to verify the correct data is retrieved.

  7. Click Execute.

    The new view is created.

  8. Allow others to use the view.

    1. Enter grant select on [View_Name] to public.

      image12

    2. Highlight the grant select command.

    3. Click Execute.

The new custom view will be available for you to access from within Trimble Web Products to add as a grid configuration.

To modify a custom SQL view, make the necessary changes in Microsoft SQL Server Management Studio, using the SQL Alter view command.

Adding a custom SQL view

You can customize a SQL view for a data grid. Customizing a view allows you to add data that does not appear by default in a data grid. After you create a custom SQL view, you must:

  • Add it to the page for which it was customized

  • Name and save it as a configuration for the data grid

Use this procedure to add a custom SQL view to a data grid.

  1. Go to Menu > Configuration > Grid Configuration Maintenance.
    The Grid Configuration Maintenance window opens.

  2. Add the custom SQL to the page for which it was created:

    1. Click the View Maintenance tab.

    2. Click the Add radio button.

    3. From the Function list, select the page containing the data grid for which you created the custom view.

    4. From the Add View(s) list, select the custom SQL view.

      Show me

      image13

  3. Click Save.

  4. Name and save the custom SQL view as a configuration for the data grid:

    1. Click the Filter Maintenance tab.

    2. Click the New radio button.

    3. From the Function list, select the page containing the data grid for which you created the custom view.

    4. From the SQL View Name field, select the custom SQL view you added in Step 2.

    5. In the Configuration Name field, enter a name for the new data grid configuration.

      image14

    6. To create the new grid configuration, do one of the following:

Removing a custom SQL view

You can remove a custom SQL view that has been added to a page.

Use this procedure to remove a custom SQL view.

  1. Go to Menu > Configuration > Grid Configuration Maintenance.
    The Grid Configuration Maintenance window opens.

  2. Click the View Maintenance tab.

  3. Click the Remove radio button.

  4. From the Function list, select the page to which you added the custom view.

  5. From the Existing View(s) list, select the custom view you want to remove.

    image15

  6. Click Save.

Exporting a custom SQL view

You may want to export a custom SQL view for a data grid. If you created a custom SQL view in a test environment or previous version of Trimble Web Products, you can export this custom view for use in your current version of the application.

Use this procedure to export a custom SQL view.

  1. Go to Menu > Configuration > Grid Configuration Maintenance.
    The Grid Configuration Maintenance window opens.

  2. Click the Import/Export tab.

  3. Click the Export radio button.

  4. From the Function list, select the page to which you added the custom view.

  5. From the Configuration Name list, select the custom view you want to export.

    image16

  6. Click Export.

  7. Save the file by following the browser prompts, making note of the file name and location.

Importing a custom SQL view

You may want to import a custom SQL view for a data grid. You can import a custom SQL view that may have been created in a test environment or previous version of Trimble Web Products. You can import this custom view for use in your current version of the application.

Use this procedure to import a custom SQL view.

  1. Go to Menu > Configuration > Grid Configuration Maintenance.
    The Grid Configuration Maintenance window opens.

  2. Click the Import/Export tab.

  3. Click the Import radio button.

  4. From the Function list, select the page to which to add the custom view.

  5. In the Configuration Name field, enter a name for the custom view to be imported.

    image17

  6. Next to the Select File field, click the image18 Browse button.
    The Choose File to Upload window opens.

  7. Navigate to and select the custom view you want to import.

  8. Click Open.
    The Choose File to Upload window closes. In the Select File field, the name of the file your selected appears. If you do not want to import the selected file or you want to select a different one, click Remove and select a different file.

  9. Click Import.
    A message confirms the custom view was imported successfully.