Grouped by Formula

1. Version

2. Expected effect

Usually, data is grouped by a certain field, such as the date field. Each date corresponds to a group. At this time, the grouping is too detailed, and you want to display it by year and week. As shown in the figure below:

Note: in FineReport, the calculation logic of week formula is consistent with that in Excel, and Sunday is defined as the first day of a week. The first week of the year must begin on Sunday.

For example, if January 1, 2010 is a Friday, then January 1, 2010 is not included in the first week of 2010, but only in the last week of 2009.

3. Implementation method

The date stored in the database is in the format of month, year and day. The user can group the data according to the year and week through the custom value in the custom group.

1. Report design

1) New report

Create a new normal report, add data set DS1, and the SQL statement is select * from orders. As shown in the figure below:

2) Report style

Drag the field to the corresponding cell, and set the data in cell C2 of paid amount to summary and sum as shown in the following figure:

2. Custom formula grouping

1) Order date grouped by "year"

Select the date data column in cell A2, and modify the data setting to group and advanced. Click the custom button below to open the custom group dialog box and select formula group.

The display mode is normal grouping. The custom value is year ($$$). The date field is grouped by "year". As shown in the figure below:

2) Order date grouped by "week"

Select the date data column in cell B2, and modify the data setting to group and advanced. Click the custom button below to open the custom group dialog box and select formula group.

The display mode is normal grouping, and the user-defined value is week ($$$) + "week", so that the order date field is grouped by "week". As shown in the figure below:

3. Summary settings

Select cell C2, select "Cell Elements > Basic > Data Settings", and modify the data settings to "Summary" and "Sum", as shown in the following figure:

4. Effect preview

1) PC terminal

Save the template and select pagination preview. The effect is as shown below:

2) Mobile terminal

App and HTML5 are showing the same below:

For completed templates, see %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\BaiscApplication\GroupInFormula.cpt

Click download template: 

GroupInFormula.cpt