Multi-layered grouping and cross-reports with complex formats are common. In Excel, the pivot table can be used to achieve grouping and summary, but the results can only be displayed as a fixed layout, often not the results we want. For example, to create such a report using sales data from xls:

It looks like this when using a pivot table:

The style of a pivot table is very rigid, with no merged cells, no slashes in the table header, and inserting rows and columns that are not allowed. There are too many restrictions.

The summary of amounts and the count of orders belong to the same group. It will be more convenient to view the data in a merged cell for the group. A diagonal line in the header can provide a more intuitive understanding of different areas in the report, which cannot be achieved with the pivot table. Although it is possible to manually add a few more rows to make a fixed header, this method is also not feasible as it needs to be redone once the data changes.

It is easy to make with ReportLite. Just create a report template according to the requirements and flexibly set the report style in the template.

Report making:

Start ReportLite, create a new report, and set the data used in the report: ReportLite toolbar - Report - Dataset - Add - File dataset, select the xls file you want to use.

The ds1 in the Name column is automatically generated, indicating the name that the xls is read into ReportLite. This name is used in the report template to set the binding relationship between cells and data.

Next is the making of the report template:

ReportLite adopts an Excel-like design approach, with cell naming and some common formulas consistent with Excel.

In grouping and cross reports, the group function is mainly used, such as the A4 cell expression: =ds1.group(States;States:1), which means retrieving the States column data from ds1 and grouping the same data into one group. This way, when the cells on the right side of it retrieve data from ds1, they will retrieve data from the corresponding group and aggregate. C2 cell also uses the group function, and set the “Expanding Mode” attribute of C2 cell (in the attribute bar on the right) to “Horizontal”. When previewing the report, it will achieve a bidirectional stretching effect of A4 vertical stretching and C2 horizontal stretching.

The C4 cell expression =ds1.sum(Amount) represents getting the sum of Amount in the crossover section, while the D4 cell expression =ds1.count() counts the data.

It is easier to adjust the format, mainly by merging cells and other operations:

Merge A4 and A5 so that when stretching by Sates, the sum for each State will be added. Merge C2 and D2, and when stretching horizontally by Category, the total order amount and order quantity will be included under each Category.

The cross header in ReportLite allows for merging cells and setting slashes. Merge cells from A2 to B3 and set header slashes. The font color, fill color, data format, and other operations of the cells are the same as those in Excel. The difference is, in ReportLite, cells are automatically stretched based on the amount of data, and when setting styles, you only need to set on a few cells, making it easier to operate.

After setting the report template, you can preview the report:

Now you can save it as an XLS file.

When creating a report template, it can be seen that cell merging, header slash, style, and other settings can be flexibly set in the report template, making the display effect of the report richer. Moreover, cells will automatically stretch according to the amount of data, and some style operations only need to be set on a few cells, making it more convenient to operate.

To download ReportLite and for more information: ReportLite.