Request direct xMart DB access (one time)

In order to write a SQL view, you need a WIMS account and access to the database.

In order to request database access, you will need to fill out this form

Create the View

In order to use a custom view, it must exist on the database in the schema martcode_c where martcode is the code for the mart.

In this case, I created a simple view via SQL Server Management Studio:

CREATE VIEW training_cf_c.SiteList
AS
	SELECT
		s.Site,
		c.[NAME_SHORT_EN],
		s.WHO_VILLAGE_NAME
	FROM
		[training_cf].[REF_SITE] s
	INNER JOIN
		[training_cf].[REF_COUNTRY] c
	ON
		s.COUNTRY_FK__Sys_ID = c.Sys_ID

Register the View

To do this, navigate to the Mart.

Select Admin and then Catalog and then the blue + next to Custom Views.

A properties box will appear.

The name of the schema you need to add the views to is in the pop up.

Select a view from the dropdown at the top and then close the dropdown.

Enter the name and description. There are also some dynamic description fields available.

These are [RowCount], [ColumnCount], [FirstUpdatedDate] and [LastUpdatedDate].

Scroll down to the bottom and press Create View.

The view should now appear on the right-hand side.

Add the View to a Category

This now appears on the table list when you add tables to Categories.

Look at the Data in the View

It also now appears on the Data view.

It is treated like any other table and the data can be viewed by clicking on it.