Cross table reports are a grid defined by analyst values, at the intersections of which there are numerical values corresponding to the values of analyst data. A special case of reports of the Cross Table type are calendar reports in which periods are located on one of the axes: Months, Weeks, etc.
We recommended using the Excel R1C1 formula-style to customize Cross-table reports. When using marker output, the style of the R1C1 formulas is the only one possible. When using the “usual” style of formulas (A1, B1, etc.), after applying markers, formulas do not work.
Fixed pattern crosstab.
To start setting up a report, go to the Report templates list and add a new Excel report template ( see more Excel-based report template ).
Then you need to prepare a report template in Excel. The Excel template will contain two sheets Report and Data. The data obtained by the data source will be displayed on the second Data sheet, and the report form will be located on the first Report sheet.
On the Data sheet, create an Excel table with two rows. Let’s set the name of the records table, this name corresponds to the name of the data source. For columns, we will set the names corresponding to the names of the columns of the data source.
On the Report sheet, create a report layout. We apply the desired design.
Next, you need to set the formulas in the cells at the intersection of the row-column, which will extract the data we need from the Data sheet. Use formula “=SUMIFS()”. Due to the use of the style of formulas R1C1, the same formula can be applied to all other report cells in which you want to get values from the Data sheet.
To calculate the totals for the groups, as well as the results of the project, we use the following functions “=SUBTOTAL()”
The totals in column can be calculated using “=SUM ()” function.
As a drawback of reports with a fixed layout, it should be noted that when adding new analyst values, the report layouts must be changed manually. For example, if we added another expense item, then we will have to go into the Excel template, add a line corresponding to the new article, and copy the necessary formulas into this line.
This drawback can be avoided if you build a report using markers.
Cross table using markers
For this version of the report, additional data sources will be needed that will allow you to list and define a grid for them.
On the Data sheet, we will output records from the data source using markers: {{ <Source Name> . <Column Name> }}
On the Report sheet, you must configure the report layout:
- In cell R2C1, we will set a marker: {{ <Source Name>.<Column Name> }}. Markers are turned off by default, so no other settings for this marker are required.
- In cell R1C2, we set a marker : {{ <Source Name>.<Column Name> ; direction = horizontal }} . Note that in this case the horizontal direction of the turn is clearly indicated.
- At the intersection, in cell R2C2, we define a formula that will extract the data we need from the Data sheet: “=SUMIFS()“;
To calculate the totals for the groups, as well as the results of the project, we use the following functions “=SUBTOTAL()”
The totals in column can be calculated using “=SUM ()” function.