Question
How do I configure custom sql filters in reports?
Answer
Background
Many of Pacific Timesheet's reports have built-in include/exclude filters. However, Pacific Timesheet's reporting engine create your own custom report filters using sql expressions to:
- Filter in any data using any field values. For example:
- Custom field values for any profile object, e.g. employee, group, project, etc.
- Custom field values for system object, e.g. timesheet custom fields, pay codes fields, etc.
- Filter out any data using any field values to support the above examples.
Creating SQL Filters in Reports
To add report sql filters:
- Create a custom version of any time entry or asset & log entry report.
- In edit mode, scroll down to the bottom of the edit report page.
- Note the Custom Filter (SQL): next to an edit box.
- In the edit box you can add sql
- Then click OK to save the report.
Examples of SQL Filter Code
Using SQL Filter to Filter in Custom Field List Values
- List value custom fields can be used on time and expense sheets for billing, craft or other codes.
- As examples, for billing these codes might be numeric values (124, 330, etc.)
- For craft codes, these might be text values (apprentice, operator, foreman, etc.)
- SQL expressions allow you to filter in a subset of a list of list values.
Billing Code Example
Billing code lists can be added to crew or individual timesheet templates so users can code time entries by a billing code.
- t_timeentry.xbillingcode IN ('100','200','300')
Definitions:
t_timeentry.xbillingcode is the database name for the billing code custom field setup in System>Templates>Manage Custom Fields.
IN is sql to include an array of the values listed between single quotes ('item') and (‘51’,’52’….. etc.) is the array of billing codes we are filtering into the report’s output.
- If you ever need to add new billing codes to this filter, just go the end of the values and insert ,’newvalue’
- You can keep adding values this so long as they are separated with a comma and the expression has a close parentheses at the end,
e.g. ‘newvalue1’,’newvalue2’,’newvalue3’)
Craft Code Example
Craft code lists can be added to crew or individual timesheet templates so users can code time entries by a craft code.
- t_timeentry.xcraft IN ('Foreman','Senior Foreman','Crew Lead')
Definitions:
t_timeentry.xcraft is the database name for the craft code custom field setup in System>Templates>Manage Custom Fields.
IN is sql to include an array of the values listed between single quotes ('item') and (‘Foreman’,’Crew Lead’….. etc.) is the array of billing codes we are filtering into the report’s output.
- If you ever need to add new craft codes to this filter, just go the end of the values and insert ,’newvalue’
- You can keep adding values this so long as they are separated with a comma and the expression has a close parentheses at the end,
e.g. ‘newvalue1’,’newvalue2’,’newvalue3’)
Single Value SQL Filter Example
If you only need to filter in a single value and not an array of values, you can use an = sign expression:
t_timeentry.xcraft ='Foreman'
If you need assistance in setting up sql expressions, just Submit a Request in Help Center.
Comments
0 comments
Please sign in to leave a comment.