Create Custom View
Copyright © 2020 EarthSoft, Inc • Modified: 24 Feb 2020
It is possible to create customized views that will appear in the Views menu in Professional. SQL Server Management Studio (SSMS) for SQL Server 2008 R2 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.
For the following example, a view with the SYS_SAMPLE_CODE column appended to the DT_RESULT table will be created.
1.In SSMS, navigate to the named database>Views.
2.Right-click on the Views folder and select New View.
3.In the prompt window that appears, add the following tables one-by-one, 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.
5.In DT_RESULT select *(All Columns) at the top.
6.Select the red exclamation point to Execute the query and verify these are the results desired to view.
7.Save the query as VW_[view name].
8.Restart EQuIS Professional and the view should appear in the View menu.
Note that if you have multiple fields with the same name in your view, the fields will not show up as options when you choose to crosstab the view. The best practice for designing the view is to give the fields a new name in the Alias column in SQL Server Management Studio, as shown here: