Tag Archives: project clock

How to Create a New Project Clock Report Using a Different Rate

custom reporting tool

custom reporting tool

One of the most common questions we get about our time tracking software is “why don’t the reports pick up the rate we’re using?” The problem is that each report uses specific instructions to pull data from the database. There is no simple way to tell a report which fields to use while still providing a report editing feature. What you can do instead though is use this report editing feature to create your own version of a report that uses the rate you want.

The technique is very similar in all our time tracking software but suppose you are a Project Clock. You like the Project Chart report but you want to use the Task rate instead of the Project rate. To edit that report, just click it and press the Edit button. This brings up the report designer. Now what you have to do is change the report’s database instructions or SQL. SQL is a language use to communicate with database.

To change the SQL choose Report | Variables from the menu. Then click on the SQL variable. You should see the reort’s SQL which looks like this:

Select P.Description AS ProjectName,Rate,SUM(CAST((EndTime-StartTime) SECOND AS INTEGER)/3600) As Hours From Projects P JOIN TimeHist T ON(P.ProjectID=T.ProjectID) GROUP By P.Description,rate Order By P.Description

Most basic SQL statements have four main parts. The SELECT statements states which fields you want to get. The FROM statement says from which tables to get the fields. If there are more than one table, each table must be joined to one other table by a common field (in this case the ProjectID field). The GROUP BY part states which fields are not being operated on (in this case summed). The ORDER BY statement indicates by which fields do you want the results sorted by.

So to use the task rate we need to link to the Tasks table using the field TaskID which is in both the Tasks table and the TimeHist table. We also must explicitly say which table to get the rate from. So we get SQL like this:

Select P.Description AS ProjectName, Ta.Rate,SUM(CAST((EndTime-StartTime) SECOND AS INTEGER)/3600) As Hours From Projects P JOIN TimeHist T ON(P.ProjectID=T.ProjectID) JOIN Tasks Ta ON(Ta.TaskID=T.TaskID) GROUP By P.Description,rate Order By P.Description

Notice that the Rate field is prefaced by Ta which is short form for the Tasks table. Press the green check mark icon to save changes to the SQL variable and return to the designer. Now choose File | Save as to save you new report with a descriptive name like Project Chart with Task Rate. Close the designer and press F5 to see your new report in the report list.

You can find out more about our time and billing products including Project Clock and Timesheets at cybermatrix.com.