How to Create a Custom View

1. Create a suitable query in the adhoc window. A suitable query just searches a table or tables and
names each column selected for output (ie. Select * from … is not suitable). An example from
Adventureworks is:
select e.EmployeeId, p.FirstName, p.LastName,
          e.Title as JobTitle
  from HumanResources.Employee e
  join Person.Contact p on p.ContactID = e.ContactID

2. Once the query is generating the correct results select Data, create custom view. If all goes well
you should see a message that confirms that a custom view has been created and gives the
temporary name assigned to it. If the view cannot be created then a different message is shown
reminding you of the restrictions.

3. Close the adhoc query window and press the drop down view list next to the GO button on the
tool bar. You should find the temporary name in the list. If you select the name you should see the
output of the query in the normal result grid.

4. Now you can modify the view to change its name to something appropriate and add sorting (as
sorting is not permitted in the original SQL). You might also notice that the original SQL is displayed
in the box labelled table. You can see the whole of it by pressing the button next to it. While it
may appear that you can change the SQL at this point you cannot.
Data, Fix Bad DataViews

Last edited Apr 21, 2013 at 10:04 PM by Dave999, version 3


No comments yet.