Menu

Time Summary by Work Type in Report Writer

Google+0
LinkedIn0

We were recently asked to build a report for a cloud partner that needed to see a summary of each employee’s time by pay period. The summary was to show total hours for certain PTO work types, as well as summaries of time by custom field selection. In addition, calculations needed to be made on the totals to determine how much of the time was overtime. As some of this is very specific to the particular client, I won’t get into all the details. This guide is meant to give someone who is familiar with Report Writer a general idea of how to go about pivoting data that you can’t normally pivot in Report Writer.

Setup

First, you need to create your main report and subreport and link them together.

Main Report

Data Source: v_rpt_time

Fields: Member_ID (to show the actual member ID) and Member_ID again (to drill into the subreport)

Filters: Date range based on Date_Start

Subreport

Data Source: v_rpt_time (my client joined with v_rpt_time_customfields for further time entry grouping but we’ll skip that in this example).

Fields (all set to SUM)

  • Hours_actual. Add this field multiple times–once for every work type you want to total.
  • IMPORTANT: Check the “Add Subtotals” box

Misc Tab: Set Drill-down key to member_id

Filters: Same Date_Start filter as main report but uncheck the Parameter box so it doesn’t show up on the report. It will automatically populate with the date entered on the main report.

Syntax for Calculations

I won’t go through each field’s Advanced Properties but essentially, here’s what you are doing. For each work type you want to to total, enter with the following syntax in the Expression box:

Form Design

Depending on what your fields are named, your design should look something like this. Note that you must use the @Subtotal for each field. It will not work properly if you attempt to add any fields without the @Subtotal. Wrap the entire HTML with <!–[repeater]–> at the beginning and <!–[/repeater]–> at the end.

Getting Fancy

Now, if you need to calculate any fields based on the results of other fields, you’ll use Subtotal Expressions for that. Here’s an example of how I went about it for the ME Reg field. In this example, I took the sum of all time with the ME custom field selection and then subtracted out anything that was overtime or belonged into another time bucket.

Additional Resources

More information on working with Subtotal Expressions can be found in my earlier blog post on the subject.

Want to do more?

Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.

Related Posts