Grouping by Custom Formula

Version

Expected Effect

Typically, data is grouped according to a specific field, such as the date field, where each date corresponds to a group. However, this grouping display is overly detailed. You want to group and display the data by year and week instead, as shown in the following figure.

Note:

In FineReport, the calculation logic of the WEEK formula is the same as that in Excel, where Sunday is defined as the first day of a week. The first week in a year must start on Sunday.

For example, if 2010/01/01 is a Friday, then 2010/01/01 would not be counted as the first week of 2010, but only as the last week of 2009.

Implementation Method

The database stores dates in the format of year-month-day. You can group the data by year and week by defining Custom Value in the Custom Group dialog box.

Report Design

Report Creation

Create a general report and enter the SQL statement SELECT * FROM Orders_new to create a dataset named ds1, as shown in the following figure.

Report Style

Drag the fields into the corresponding cells, as shown in the following figure.

Grouping by Custom Formula

OrderDate Year Grouping

Select cell A2 where the Orderdate data column is located, and select Group and Advanced for Data Setting. Click Custom to bring up the Custom Group dialog box, and select By Formula. Set Display Mode to Common Group and Custom Value to = year($$$). Set the Orderdate field to be grouped by Year and displayed as a number with year($$$) representing the year from the given date, as shown in the following figure.

OrderDate Week Grouping

Select cell B2 where the Orderdate data column is located, and select Group and Advanced for Data Setting. Click Custom to bring up the Custom Group dialog box, and select By Formula.

Set Display Mode to Common Group and Custom Value to = "No." + week($$$) + "Week" Set the Orderdate field to be grouped by Week and displayed as "No.X (a number) + Week", with week($$$) representing the week number from the given date, as shown in the following figure.

Setting Summarization

Select the C2 cell, choose Cell Element > Basic > Data Setting, and select Summary and Sum, as shown in the following figure.

Effect Display

PC

Save the template and click Pagination Preview. The effect is the same as that shown in section "Expected Effect."

Mobile Terminal

The preview effects on the DataAnalyst app and HTML5 terminal are the same, as shown in the following figure.

For details, you can download the example template: Group by Custom Formula.cpt.