Tag Archives: custom reporting

How to Format Report Dates

report display format dialogA common questions asked by CyberMatrix customers is how to change the format of dates on reports. There are two ways to format report dates: using a format function and by changing the display format. With either method you must first edit the report from the reporting screen. To edit a CyberMatrix report click on the report on the report list and choose the Edit Report button or choose Custom Reports | Edit Report from the report screen menu.

Format Functions

All CyberMatrix software have report date formatting functions. The advantage to formatting functions is that they use the system formats instead of hardcoding a fixed format.

To add a date formatting function to a report date field simply double click the field to open the field edit screen. Usually the field value will look something like [frxDBDataset1.”WorkDate”]. This is just a bare data field. To add the date formatting function to this field the field is wrapped inside <> characters and this text is wrapped within the FormatDate() function call. So the resulting text will be [FormatDate(<frxDBDataset1.”WorkDate”>)].

Changing Display Formats

To change the display format for a date field right click the field and choose Display Format from the pop-up menu. In the Display Format dialog click on the Date/Time category. In the Format String box enter the display format you want to use with the report. For example, for the ISO date format use yyyy-mm-dd. For US date format you can use mm/dd/yyyy.

After changing the report make sure to save it. Also note that reports are overwritten every time you upgrade the software. So if you want your changes permanent you should save the report with a new name.

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.