Create Custom View

<< Click to Display Table of Contents >>

Navigation:  Professional > Database > Modifications >

Create Custom View

Administrators can create customized views to appear in Open > Views in EQuIS Professional. SQL Server Management Studio (SSMS) for SQL Server includes a user-friendly diagram pane in which to create these views. As the name implies, views are not editable and are used as a way to view data, similar to a report output.

 

To avoid conflicts with database upgrades and maintain greater control of permissions, create custom views in a custom schema, as with custom tables.

 

The following example creates a view with the SYS_SAMPLE_CODE column appended to the DT_RESULT table.

1.In SSMS, navigate to the named database > Views.

2.Right-click on Views and select New View.

3.In the prompt window that appears, add the following tables, in this order: DT_SAMPLE, DT_TEST and DT_RESULT.

4.In the diagram pane at the top, select SYS_SAMPLE_CODE from DT_SAMPLE and any additional columns that need to be included. Columns will appear in the order selected.

5.In DT_RESULT select *(All Columns) at the top.

6.Run the query by selecting Execute SQL (Ctrl + R) from the View Designer toolbar.

7.Verify the output.

8.Save the query as VW_[view name]. This view will appear in the main schema, "dbo".

9.Move the view to a custom schema. For example, this code will move view VW_SYS_RESULT to a schema called "custom":
ALTER SCHEMA custom
TRANSFER dbo.VW_SYS_RESULT;

10.Add any required permissions. The view will inherit permissions from the schema.

11.Restart EQuIS Professional, then log into the database. The view should appear in Open > Views.

 

For multiple fields with the same name in a view, the fields will not show up as options when crosstabbing. Give the fields a new name in the Alias column in SQL Server Management Studio, as shown in the selected field below:

 

15071-vwSSMSalias