Filtering

Tony Coffman -

Filter

Once you’ve finished adding the report fields and have them in the desired order, you can filter the View. 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.

Important Filtering Notes

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.

@TodayXMonthsAgo = Replace X with a number of months in the past; @Today4MonthsAgo, @Today4MonthsAgo.

@TodayXMonthsOut = Replace X with a number of months in the future; @Today4MonthsOut, @Today4MonthsOut.

@TodayXYearsAgo = Replace X with a number of years in the past; @Today4YearsAgo, @Today4YearsAgo.

@TodayXYearsOut = Replace X with a number of years in the future; @Today4YearsOut, @Today4YearsOut.

@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

@XDayYMonthsAgo = Replace X with the Day of the month and Y with the number of Months in the past; @1Day12MonthsAgo

@XDayYMonthsOut = Replace X with the Day of the month and Y with the number of Months in the future; @1Day12MonthsOut

@LastDayofLastMonth = Gets the last day of the last month based on the date the report is executed

@XDayYMonthsAgo = Replace X with the Day of the month and Y with the number of Months in the past; @1Day12MonthsAgo

@XDayYMonthsOut = Replace X with the Day of the month and Y with the number of Months in the future; @1Day12MonthsOut

@LastDayofLastMonth = Gets the last day of the last month based on the date the report is executed

 

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)

ACCOUNT_SELFPAY.Assigned_Date >= 1Day12MonthsAgo will create the filter (ACCOUNT_SELFPAY.Assigned_Date >= '2014-01-01') AND (ACCOUNT_SELFPAY.Assigned_Date <= '2014-12-31') if the report was run in January 2015.

ACCOUNT_SELFPAY.Assigned_Date <= @LastDayofLastMonth will create the filter ACCOUNT_SELFPAY.Assigned_Date <= '12/31/2014' if the report was run in January 2015.

Have more questions? Submit a request

Comments

Powered by Zendesk