Skip to main content
Cartegraph Campus

Common Report Filters and Expressions

An expression, as used in Cartegraph Reports, is defined as a function that is applied to the field level within a report. Most commonly, expressions are applied within the Table Contents window of the Report Designer and are specific formatting, functions, or calculations on one field or column.  

Filters are defined as filters and expressions that are applied at the table or report level. Filters are applied using the Properties window for a specific table or using the Filter option on the Project tab of the Report Designer. Filters affect data for the entire report, where an expression affects a specific field or column.

For more information about where to apply filters and expressions, please see Campus articles and attend reporting web courses or contact Support at 877-647-3050.

Common Expressions

It may be useful to format certain fields or data with different colors based on a data value. In the below example, the ADA Ramps Estimated OCI values will appear black in the table if they are higher than 16, but if they are less than or equal to 15, the data will appear red. This is accomplished using the If function within the Report Designer. The way to read the If the function is as follows: If ADA Ramps Estimated OCI is greater than or equal to 15, the text will be black. If not (else) the text will be red. The third option (for the false answer) is an optional value.

If(ADA_Ramps.Estimated_OCI>=15, LL.Color.Black, LL.Color.Red)

The expression below is used to put in filler data if a value does not exist for a certain record. The example below is written using an If statement and the Is Null or Empty function of the Report Designer. The expression is designed to check if the Sign Position is null or empty and if so, to replace the blank with No Sign Position. The expression can be read as follows: If the sign position is empty, write No Sign Position else print the Sign Position.

If(IsNullorEmpty(Signs.Position), “No Sign Position”, Signs.Position)

The below expression is used to replace null (blank) data with a zero. This is useful in tables to see where there may be no data but where the user may want to see zeros to be able to do calculations including the zeros. An example of where this may be a useful expression is on a labor log report to display all hours worked in a week by a laborer—the report will show a zero if there was no labor log entry for a given date rather than leaving the cell blank.

NullSafe(Labor_Log.Date, 0)

It may be useful to count the number of records in a column. This can be done using a simple Count function. The below expression shows a count of the Task IDs in a report.

Count(Tasks.Task_ID)

Summing of costs is one of the most important functions for many reports. This action is done using a simple Sum function. The below expression will show a sum of the Task Total Cost values for a report.

Sum(Tasks.Total_Cost)

Often, users will want to require that certain data within a report be populated—not see records where certain fields are blank. There are two easy ways to specify these using simple expressions. The first expression uses the Is Null function where the function is set to False. This expression can be read as follows: Request Department is not null. Setting the Is Null = False says Is it or can it be null? No.

Is Null(Request.Department) = False

The second way to require that a field have data is to use a Not Is Null or Empty function. This expression can be read as Request Department is not null or empty.

Not IsNullorEmpty(Requests.Department)

Common Filters

For a report created and run from the detail view or list views within OMS, a special filter must be applied. This filter allows the report to have its results reflect only the selected records when run from the list view, or only the single record when run from the detail view. See the below example of the filter. To use the filter on different modules, replace the Signs.OID with the module you are running it for (example: Materials.OID, Tasks.OID, Pavement.OID). The parameter for @cgOIDS is a default parameter available in all reports created using Cartegraph OMS.

Signs.OIDs = @cgOIDs

To have a report parameter default to the first day of the current month.

Date( ToString$(Month(Today())) + "/1/" + ToString$(Year(Today())))

Reports may commonly need to be filtered to show data within a date range. One way to do this is to use the Date in Range function within the reporting software. The Date in Range function tests a specific date to be within a given range. In the example below, the filter is looking at the Task Actual Stop Date and determining if it is within the user-specified range.

DateInRange(Tasks.Actual_Stop_Date, @StartDate, @StopDate)

Another type of date range filter is to use the below filter. This filter will show data where the Actual Start Date is greater than or equal to the user-entered start date and where the Actual Stop Date is less than or equal to the user-entered stop date. This filter can be read as Task Actual Start Date is from the entered start date and Task Actual Stop Date is through the entered stop date. For a video walkthrough of how to utilize this filter, please review this video: Reports: Date Ranges.

Tasks.Actual_Start_Date >= @StartDate AND Tasks.Actual_Stop_Date <= @StopDate

It may be useful to filter a report by the most recently modified child data. In this filter example, the filter is looking at the Material Log Last Modified date and compares it to today’s date minus the number of user-entered days. The filter then shows the Material Log data, which has been modified within the number of days. This is used in the Cartegraph standard report titled Stagnant Material.* Below is the simple filter and instructions for how to set up the Last Log Days parameter.

Materials_Log.Last_Modified >= Today()-@LastLogDays

               @LastLogDays parameter setup:

                              Name: @LastLogDays

                              Available Values: Manual Input

                              Type: Numeric

                              Default Value(s): 30

                              Control Type: Text

                              Description: “Last Modified Date Range

A simple way to narrow down a task report’s results is to apply a filter by activity. The below filter is a simple way to specify which activity the report wants to return data for.

Tasks.Activity = ‘Install’

Another way to narrow down report results is to filter by a specific asset type. The below filter specifies which asset type we want to see results for.

Tasks.Asset_Type = ‘Sign’

Task reports will commonly need to be filtered down to a specific asset type and activity. For example, if the report is called Sign Inspection Report, the below filter can be applied to get the necessary data. The AND statement is used to combine two expressions to create a more complex filter.

               Tasks.Asset_Type = ‘Sign’ AND Tasks.Activity = ‘Inspect’

One filter Cartegraph utilizes in the standard reports is below. This filters by Task Actual Stop Date within a given range OR Task Actual Start Date within a given range and that the task is still open. This is useful to broaden the filter to include tasks that may have only an Actual Start Date because the task is still open.

DateInRange(Tasks.Actual_Stop_Date,@StartDate,@StopDate)

OR

(DateInRange(Tasks.Actual_Start_Date,@StartDate,@StopDate) AND (IsNullorEmpty(Date$(Tasks.Actual_Stop_Date))))

There are both simple and complex ways to filter by record status. This is useful for request, work order, task, or inspection reports. Below are three examples. The first is a simple equals filter, the second is an OR filter, and the third is an exclusion filter using the Not Equals (!=) operator to exclude values.

Tasks.Status = ‘Planned’

               Tasks.Status=’Completed’ OR Tasks.Status=’Canceled’

               Tasks.Status != ‘Canceled’ AND Tasks.Status != ‘Planned’

Another filter used by Cartegraph standard reports is below. This filter will show only tasks that have an Estimated Start Date within the last 30 days. This filter utilizes the Date Diff function, which returns the difference between two dates in days and then compares that number to 31 to show only tasks where the Date Diff is less than 31.

DateDiff(Today(),Tasks.Estimated_Start_Date) <31

The below filter displays tasks that have a status of Completed or Canceled and the estimated start date is within the last 30 days. This filter is a combination of others referenced in this document. This is an example of how multiple filters can be combined using AND/OR statements.

(Tasks.Status="Completed" OR Tasks.Status="Canceled")

AND (DateDiff(Today(),Tasks.Estimated_Start_Date) <31)

  • Was this article helpful?