What tools will I have to modify custom reports?

Question

What tools will I have to modify custom reports?

Answer

Pacific Timesheet has a number of advanced tools for your customer reports.

Include/Exclude Filters

Dynamic filters: Employee Hours by Pay Code use case

After you run most timesheet, employee, asset, expense and log reports you can use include/exclude filters. These filters, which appear on the left side of reports, can be used to filter in or filter out key object data such as:

• Reporting Groups
• Employees (Resources)
• Projects
• Phases
• Tasks
• Pay Codes
• Other objects

For example, for an Employees Hours by Pay Code Report - Year-to-date, you can:

1. Create an Employee Hours by Pay Code report by navigating to Reports > Timesheet Reports > Employee Hours reports, and click on the show/hide button to display more Employee Hours report options. 
2. On the Employee Hours by Pay Code row you can click the report name to run an template report that cannot be customized.
3. Or you can move your cursor to the right of that row and click on the Plus (+) button to create a custom version of the Employee Hours by Pay report.
4. Click on the Employee filter on the left of the report page.
5. Set the filter to "include only the selected items."
6. If the number of employees is less than 100, a full list of employees will display in the filter.
7. You can select John Smith by scrolling down the filter list to John Smith's name, press the control key and then click
8. This will mark John Smith as a selected item for the report.
9. Then click Run to run the report and the system will return report data for just John Smith
10. If the number of employees is greater than 100, the full list of employees will be blank
11. Click on the Plus (+) button to display the Employee search list.
12. Enter the employee's name and the list will filter to that one option
13. Place a check mark next to the employee name and click Add
14. The employee's name will be added to the include filter
15. Then click Run to run the report.
16. You can add multiple employees to the filter this way and then run the report.

Preset filters: Employee Sick Time Hours report use case 

You can create an Employee Sick Time Hours report that always filters to the Sick Time pay code.

1. Create and name an Employee Hours by Pay Code custom report.
2. In report edit mode, navigate down to the Pay Code preset filter
3. Make sure it's set to "include only the selected items"
4. Select the Sick Time pay code on the left picklist
5. Click the Plus (+) button to add Sick Time to the include filter.
6. You can preset a date up top like Year-to-date.
7. Save and run the report.
8. It will now display employee hours coded by the sick time pay code year to date. 

SQL Filters

At the bottom of every time, expense, asset and log summary or details report is a SQL Filter. This filter use SQL to include or exclude report data.

Preset Include Filter: Employee Type use case

The SQL t_user.type='non-exempt' will filter in data for employees set with the employee type 'non-exempt' in their employee profile.  

Preset Exclude Filter: Project = Overhead use case

Project managers might want a report that does not include, or filters out, "Overhead" hours. 

The SQL t_project.name<>'Overhead' will filter out data for the project named overhead. The SQL can also include * as a wildcard character: t_project.name<>'*Overhead', where the asterisk will include any text before Overhead in the filter.  

SQL Expressions

You can adding report columns to any hours, expense, asset or log report by finding and selecting a field on the right and clicking the + button. At the bottom of that list is Add SQL Expression. A SQL Expression field allows you to use SQL to create a values in a variety of ways. 

Display Project Manager Name in report field
Pacific Timesheet allows you to assign project managers to projects with the project manager security permission. This permission will allow these users to project time approve their projects. To display a project manager's full name in a project hours report:

1. Create any project hours report, e.g. project hours by employee, project hours by department, etc.
2. Create a sql expression field.
3. Enter the following sql:
   select fullname from t_user where id=(select top 1 userid from t_acl where obj='Project' and objid=projectid and roleid=(select id from t_role where name='Project Manager'))
4. Change the sql expression file label setting it to "PM" or "Project Manager" 
5. Save the report and run
6. The result will show the Project Manager 

Display an Approver's Name in report field
Pacific Timesheet allows you to assign managers to approve employee time and expense sheets. To display an approver's full name in an employee hours report:

1. Create an employee hours report
2. Create a sql expression field.
3. Enter the following sql:
   select uu.fullname from t_user as uu where uu.id=t_user.apprv1id
4. Set data type to 'Text'
5. You will need to include t_user.apprv1id in the "group by" expression.
6. Click on Options and navigate to the Group by expression editor.
7. Append, t_user.apprv1id to the expression and click Okay.
8. Save the report and run
9. The result will show the level 1 Approver full name.