Managing user-defined SQL pages

You can retrieve data from your TMWSuite database by adding a where clause to any of the SQL views in the database. The results can be saved as a new menu option in Trimble Web Products, and then assigned to be viewed by users through a user role. Viewed reports can be exported to Microsoft^® Excel.

To add a user-defined SQL view as a menu option, follow these steps.

Go to Menu > Configuration > User Defined SQL Maintenance.
Click Add New User Defined SQL.
A blank form appears.

Show me

Complete the form.

Field definitions

Required field: *

Field Definition

Field	Definition
Name *	Enter a name for the new page.
Description *	Enter a brief description for the new page.
Database Location *	Select the database that contains the SQL views.
SQL View *	Select the SQL view to which you want to add the `where` clause.
Update Procedure *	Select the stored procedure in the TMWSuite database that is used to populate the selected SQL view.
Where Clause	Enter the `where` clause you want to add to the selected view.
Add to Page Definition	Select this check box to make this page available to add to a menu.
Profile Properties Available	List of available variables you can use in the `where` clause

Name *

Enter a name for the new page.

Description *

Enter a brief description for the new page.

Database Location *

Select the database that contains the SQL views.

SQL View *

Select the SQL view to which you want to add the where clause.

Update Procedure *

Select the stored procedure in the TMWSuite database that is used to populate the selected SQL view.

Where Clause

Enter the where clause you want to add to the selected view.

Add to Page Definition

Select this check box to make this page available to add to a menu.

Profile Properties Available

List of available variables you can use in the where clause

Click Create.
If you selected the Add to Page Definition check box, do the following:
1. Add the page as a menu option.
  
  Show me
2. Add the page to the role.
  
  Show me

Editing an existing user-defined SQL page

To change any of the selections for a user-defined SQL page, follow these steps.

The Name field is a read-only entry. You cannot change the name of the user-defined SQL page.
If you clear the Add to Page Definition check box, when it was previously selected and added as a menu option, the menu option remains but no report is displayed. You must also remove the menu item.

Go to Menu > Configuration > User Defined SQL Maintenance.
Select the User Defined SQL you want to edit.
The populated form appears.
Edit the form.
Click Update.

Deleting a user-defined SQL page

You can delete a user-defined SQL page. However, user-defined SQL pages that are already added as a menu option cannot be deleted. You must remove the menu item first. Removing an item removes that page from any roles to which it has been assigned.

Go to Menu > Configuration > User Defined SQL Maintenance.
Select the User Defined SQL you want to delete.
The populated form appears.
Click Delete.
A confirmation window opens.
Click OK.

Note: If the page was added to the menu structure, a message instructs you to remove it first.