How to add column total to custom report?

Hi, I’m struggling to get my head around custom reports.

I would like to add a total to at the bottom of a specific column (total hours paid).

What would be the correct syntax to be able to do this?

Here are some photos:

I think you are editing the wrong report. This report is used to edit the punch clock for workers on the Punch Editor screen. There should be Punch Report, that is the one you want to edit.

This post might help:

On a side note, you can press the Print Screen on your keyboard to take a screen shot and paste (Ctrl + V) it into the post box. Or if you want some software, Sharex is a free program to handle print screen and edit the picture before posting.

Hi @Bob_be, would you know how to add a row at the bottom that totals the hours?

The time clock I’m using is slightly different to that post.

Here are the screenshot of the punch report settings:

The post I linked (post 301) to is referring to the time clock your using…it is a little off topic of the original post.

You should be able to copy the report from the post and paste it in a new report to test, look at and study.

Just tried pasting the report.

It seems to be registering the users but doesn’t seem to extract the hours worked.

Would you know why this would be?

Was a work period open when the clock in/clock out was used?

If you want a grand total you would remove other fields from expression.
See how duration has .sum on the end, this would sum any identical lines, so if you removed all fields exc user it would give you total per user.
If you removed all but the duration it would give you total for all users for the period.
So duplicate the report expression underneath, reduce the fields.
You would likely need to adjust the column formatting bit at the ends with the curly brackets.
If you see that formatting section you will see 4 is missing, this is because the raw duration is column 4, and then the two blue fields (shown on one line) both refer to field $5 and format it. So you may need to do similar if you want that same format.
You would need to adjust the numbers to correspond to new field number etc.

Success!

Got the report to look like what I needed :slight_smile:

Was a bit tricky to get my head around it but you gave enough hints to figure it out. Thanks @JTRTech

Here’s what it looks like:

Would someone know how to convert the hh:mm format into a two decimal format?

Tried to replace - TSD.Clocked In.sum;fs\:hh\:mm,

to - TSD.Clocked In.sum;0.00,

but didn’t seem to work :confused:

I just realised that this report doesn’t correct the error that you picked up on in the other thread @Bob_be.

It doesn’t seem to calculate the updated hours when modifying hours through the punch editor.

Would you know how to apply that fix to this report?

The original Punch Report that came bundled with the Time Clock import uses, as well as your report, Clocked In state. When the time is edited the state time is not adjusted.

You will need to take the duration and subtract the break. Something like this:

=TN(’[T.Duration]’)-TN(’[[TSD.Break]’/60));fm\:h\:mm

I haven’t tested this, but this should get you going in the right direction.

1 Like