Creating a New View - Full Overview

Tony Coffman -

Creating a New View (.VDM)

Expand the desired table on the left and then double-click on the field name. Double-clicking the field will place the field into the Report Fields section of VDM (right side panel).  Alternatively, you can drag and drop the desired field from Tables and Fields to the Report Fields panel.  Repeat this step to add additional fields.

 

Removing report fields from View

To remove a Report Field from the list, simply double-click on it. Alternatively, you may right-click on the field and select Remove.


Move Report Field Up or Down

The order in which the Report Fields appear in the list will be the order they are displayed once the search has executed.  To move report fields up or down in the list, drag and drop the Field up or down. 

FSGS

After selecting your tables and fields you can then Filter, Sort, Group and Sum the returned results using the FSGS tab.

Filter

Filtering your data will allow you to extract just the information you need from your database.

To add a new filter, click on the FSGS tab.  From the Filter section, click on the Field drop-down list and select the Report Field you wish to filter on.  Next select the operator from the operator drop-down list and then add the appropriate Value criteria.  Repeat for each additional filter using the + button to add each new filter line.

In the example below we are asking VDM for all accounts with the Desk_ID = 'BD1' and the Assigned_Date is equal to today.

VDM is data aware and you do not need to place single quotes around string and date fields.  In the example below we are filtering on the Desk_ID and Assigned_Date. 

 

Operator Options and Definitions

Equals                             Returns data equal to the entered Value for the selected Field.

Does not equal                 Returns data not equal to the entered Value for the selected Field.

Greater Than                    Returns data greater than the entered Value for the selected Field.

Greater Than or Equal To   Returns data greater than or equal to the entered Value for the selected Field.

Less Than                         Returns data less than the entered Value for the selected Field.

Less Than or Equal To        Returns data less than or equal to the entered Value for the selected Field.

Starts with                       Returns data that starts with the entered Value for the selected Field.

Ends with                         Returns data that ends with the entered Value for the selected Field.

Contains                           Returns data that contains the entered Value for the selected Field. 

Does Not Start with           Excludes data that starts with the entered Value for the selected Field.

Does Not End with             Excludes data that ends with the entered Value for the selected Field.

Does Not Contain               Excludes data that contains the entered Value for the selected Field. 

Is Null                               Returns null data for the selected Field.

Is Not Null                         Returns non null data for the selected Field.

Is between                        Returns data between two Values for the selected Field.  Usage: 50 AND 100

Is between                        Returns data not between two Values for the selected Field.  Usage: 50 AND 100

Is any of*                         Returns data that matches the entered list Values.

Is none of*                       Excludes data that matches the entered list Values.

*For the Is any of and Is none of operators, use the Create/Edit Value List feature to manage your list:

 

Special Date Filtering Options

VDM has several reserved key words designed for Special Date Filtering:

@Date = Today’s Date

@XDaysAgo = Replace X with a number of days in the past; @30DaysAgo, @94DaysAgo

@XDaysOut = Replace X with a number of days in the future; @30DaysOut, @94DaysOut

@XWeeksAgo = Replace X with a number of weeks in the past; @4WeeksAgo, @8WeeksAgo

@XWeeksOut = Replace X with a number of weeks in the future; @4WeeksOut, @8WeeksOut

@ThisMonth = Creates a date filter that will return everything from the first day to the current day of the current month.

@LastMonth = Creates a date filter that will return everything from the first day to the last day of last month.

@XMonthsAgo = Replace X with a number of months in the past; @6MonthsAgo, @10MonthsAgo

@XMonthsOut = Replace X with a number of months in the future; @6MonthsOut, @10MonthsOut

@XYearsAgo = Replace X with a number of years in the past; @3YearsAgo, @4YearsAgo

@XYearsOut = Replace X with a number of years in the future; @3YearsOut, @4YearsOut

 

Usage Examples:

Assigned_Date = @Date (will return everything assigned today)

Assgined_Date >= @30DaysAgo (will return everything assigned in the last 30 days)

Promise_Date >= @30DaysOut (will return all promises with a promise date over 30 days from the current date)

Assigned_Date = @1DaysAgo (will return everything assigned yesterday)

Assigned_Date >= @7DaysAgo and Assigned_Date < @Date (if run or scheduled to run on a Monday it’ll return everything assigned last week).

Transaction_Date >= @365DaysAgo (will return all transactions for the last year).

Assigned_Date = @1DaysAgo (will return everything assigned yesterday)

Assigned_Date >= @7DaysAgo and Assigned_Date < @Date (if run or scheduled to run on a Monday it’ll return everything assigned last week).

Transaction_Date >= @365DaysAgo (will return all transactions for the last year).

Assigned_Date = @4WeeksAgo (will return everything assigned today)

Transaction_Date = @ThisMonth (will return all transactions from the first of the current month through the current date)

Transaction_Date = @LastMonth (will return all transactions in the last full month)

  

Sorting

Sorting allows you to order the returned data however you choose. To add a sort, select the desired Report Field from the sort drop-down list, then select ASC (Ascending) or DESC (Descending) from the Type drop-down list.  Repeat to add additional sorts.

The data will be sorted in the order the sort options are displayed in the list box. To change the sort order, drag and drop the sort item up or down.

  

Formatting

Formatting a column allows you to quickly and easily change the formatting of the data displayed and change your Column Names. To format a column first click on a value in the appropriate column, right click and select one of the options available under Format Column.  To remove formatting from a selected column click the None option under the Format Column Menu.

When formatting a column, VDM will use it for the current view and ask if you’d like to remember this Format for future views. This will insure that anytime the selected field is used in a new View VDM will remember your formatting preference.

Grouping

Adding a group to your View will allow you to summarize your data by the Report Field(s) selected.

To add a grouping, select the desired Report Field in the Group drop-down list.  Click the + button to add additional groupings or the - button to remove a grouping.  In the example below we are grouping by the Assigned_Date.

Grouping Options

There are a couple special grouping options in VDM that will allow you to further aggregate your date data fields by Month, Month.Year and Year. 

 

Summary

Adding summaries to your View will allow you to perform calculations on your grouped fields. Please note that summaries should only be added to numeric fields and not strings.

To add a summary, select the desired report field from the summary drop-down list and select the appropriate function. Click the + button to add additional summaries or the - button to remove a summary.  In the example below we are requesting a count for Account_ID and a Sum of the Orig_Assgn_Amt.

 

Summary Options

AVG                       Computes an average of the selected report field

COUNT                 Counts the total number of records

MAX                      Computes the maximum for the select report field

MIN                       Computes the minimum for the select report field

SUM                      Computes a summation of the selected report field

 

Linking

Linking in VDM (or any query and reporting tool for relational databases) is required when requesting data from two or more tables. Linking tells VDM how to join the tables together in order to return the requested data set.

In the example below we are pulling data from the ACCOUNT_SELFPAY, CLIENT and QREF tables. The relationship between ACCOUNT_SELFPAY and QREF tables is the Account_ID field and the relationship between ACCOUNT_SELFPAY and CLIENT tables is the Client_ID field.  Linking can be a bit challenging in the beginning, but as you become more comfortable with your data and VDM it will become second nature. 

To add a new linking select the field from one table then drag and drop it the related field in the second table. Then choose the Linking Type.  If you have more than two tables, repeat the process for each table.

Auto-Linking

As you build new relationships through linking, VDM will learn your data structure and remember your linking preferences for future View creation.

Have more questions? Submit a request

Comments

Powered by Zendesk