How Do I Create Periods Using the Group By Function?

Users may wish for a report to display different time periods. This article will explain how to use the Group by and Sum functions to create a report with different time periods. The customer period report may look as follows:

Follow these steps:

1. Create a new Query report, then click Setup > Query wizard.

2. Navigate to Tables, then add the CustTable table and four new Queries using the + sign.

3. Give the new Query a name and set Group by=Yes for all four new Queries.

4. Add CustTrans to the new Queries.

5. Under the Selected fields tab, remove the Recid (Group by) fields from the CustTrans tables to avoid getting the data group by every transaction/record.

6. Add AccountNum and AmountMST to all CustTrans tables.

Set it up as follows:

AccountNum: Group by

This will group the CustTable records by AccountNum.

AmountMST: Sum

This will calculate the sum of the field AmountMST for all records with the same AccountNum.

7. Navigate to the Default ranges page and add the field TransDate to all CustTrans tables.

8. Define the different time periods by clicking the Expression Editor button to change the value in the properties for each field to the following:

• CustTrans: ( today ( ) - 30 ) + ".." + today ( )

• CustTrans_1: ( today ( ) - 60 ) + ".." + today ( ) - 31

• CustTrans_2: ( today ( ) - 90 ) + ".." + today ( ) - 61

• CustTrans_3:  ".." + today ( ) - 91

For more information on this topic, read our Lasernet FO Connector User Guide.