Reporting - Writing Best Practices

In this article, we will go over several common practices that will allow you to write reports that are more organised, easier to update, and more efficient to create.

How to Name Variables

Whenever you create a JOIN in one of your reports, you will need to allocate it a name so you can call upon it in the SELECT section and pull the content you need from it. We recommend that you follow the camelCase naming convention.  Camel case is a way of writing variable names that makes them easier to read by using a lowercase word to start a variable's name, then starting every following word with a capital letter. We use this because variable names cannot make use of empty spaces at all, so this allows us to make use of multi-word names while bypassing this limitation.

Using this convention, we recommend writing out variable names in a very literal way where you combine the names of the items in the JOIN together so that you can see exactly what that connection is at a glance when you go to add it to your report.

Some examples include:

• Writing Case Type Name as caseTypeName
• Writing Client Person as clientPerson
• Writing Closing Details Created Timestamp as closingDetailsCreatedTimestamp

Note: The one exception to this naming rule is when you define a JOIN that goes to a Case record. The word 'case' is treated as a reserved word in HQL, so we cannot use it as a variable name as it is already associated with something else. We recommend using the word Kase instead whenever you perform this JOIN.

How to Format Elements

When you add more code to your report, you may notice that the elements you bring in will appear in different formats. Some lines may be misaligned from one another, and you may copy in some code only for it to slide to the opposite side of the window. Writing your code in a consistent way is crucial to visual clarity and making it easier to read, understand, and amend your code as you run into different issues or choose to add additional content. A key tool in doing so is indentation (TAB key on Windows & Mac), which allows you to create a gap larger than those created by the Spacebar.

A good rule to follow is to have each new element you add be indented from the element above it to visually indicate that the two are connected. 

See below for an example of this idea using the HTML tags from the Tables section.

Whem writing a query, try to follow this same rule to keep the query as readable as possible. The properties that extend from the section headers (SELECT, FROM, WHERE) should be placed below these words and indented in once to visually indicate a separation between the two.

How to Approach Writing Reports

It can be daunting to figure out where to start when creating a fresh report. A good way to simplify the process is to use a standard template as a launching off point. This template contains a default parameter block, an empty query block, and an empty table, all laid out in a standardised format for ease of use. If you use this as your base line for every report you create, it will streamline the process.

When your template is in place, it is best to start the report with the FROM section. If you start here, you can map out all the data you want to bring out and plan out the rest of the report from there without missing any connections between the JOINs and the fields you want to output. When that is complete, you can then use the connections you made with the JOINs to fill in the SELECT section and determine the data that is being displayed in the report. Finally, you can add in any WHERE clauses that you will need to augment the results of the report as this section will often involve calling data from both the SELECT and FROM sections in order to make it work.

Once your query is prepared, displaying it in the table becomes much easier. You now know how many fields are going to be pulled out by the query and can create an appropriate number of columns to support it in the report's header, which you can then populate in the tr macro.

How to Find Properties on a Data Type

One issue you will often run into when writing reports will be figuring out which properties are available on each data type. A quick way of finding this out would be to check out the Property Browser. You can access this by going onto the Reports screen and opening the Tabular Report Wizard under the Report Tasks area.

In Step 1, select the data type that you would like to find the properties off, then continue onto Step 3 of the process. For this example, we will be selecting the Case level.

Once you reach this step, press the Browse... button to gain access to the Property Browser. In here, you can quickly view every property that extends from the case such as the case's open date, the case type and custom fields attached to the case.

At a quick glance, you can determine which properties are available on each data type and bring them into your own reports. Please note that the names for most properties will be the camelCase version of what is shown in the Property Browser; for example, Created Timestamp becomes kase.createdTimestamp and Case Type becomes caseType.

In the event that you are unable to find the properties you need in the Property Browser, please consult the following document on Data Types.