In this tutorial, we will tackle the advanced reports, that follows the simple reports tutorial. We will see the below points
- Excel reports in listing mode
- Relations in a word report
Excel reports in listing mode
The listing mode is available for Excel & Word, which allows you to create a report based on the data of multiple submissions.
On Word, build your report the regular way, and it will return as many versions of your rapport, one after the other for each selected submissions, in a single file.
In Excel, there is a specific behavior to adopt, completely different from the normal mode. We will explain this behavior here.
First of all, during the creation of your report, you just need to tick the box as shown below to activate the listing mode. For Excel, you need to always select the "Aspose Library for Excel"
In the construction of your Excel report, you need to pay attention to some mandatory elements:
- A PPActions sheet, and a DATA sheet. The PPActions sheet will disappear when you will generate the report, but it allows you to set which fields should go where in the report.
- A specific syntax in the PPActions
In the below example, we will also tackle the case where your submission has child submissions (with a relation field) that you need to display.
In the PPActions sheet, you will configure what will be displayed in the DATA sheet that will allow you to process the information.
In the above example, each line is independent and will represent a Table (in the sense of the Excel object) in the DATA sheet.
On the first line, in the first cell you will put the information related to the excel table of the DATA sheet : <%Type:table ; TableName:Tableau%>
You will have created an excel table beforehand. "Tableau" is the name of the Table you have created (you can find the name in the Table settings, on the top ribbon of excel). In this table, the data that will be there is the data from the form to which you linked the report to. The type "table" indicates that this is your main table. This cell will not be taken into consideration when displaying the table in the DATA sheet.
However, the following cells of the row will be present, in the order in which you write them. The syntax to indicate what data goes in the following cell is always the same: <%Value:SystemName%>, no matter the type of field. It is also possible to apply formulas in those cells like the ones for simple reports.
In rows 3 & 5, the first cells refer to a sub submission, which is why we need to use the type "subtable" in the first cell. You also need to create beforehand 1 Excel table per subtable that you will be displaying. You also need to enter the name of those subtables, the same way that you did for the main table.
You also need to add the information « Relation:RelationLabel » (⚠️ not the system name, but the label). Then, the way you add & order the data is the same as for the main table: <%Value:SystemName%>
In the DATA sheet, you will have created the tables to receive the data like below
You can change the column headers as you wish, however you need to remember that the display order of the data will be the one you defined in PPActions.
The report generation will insert a new row in the tables per submission or submissions.
Relations in a word report
Relations in word require a specific format when we call the fields of a child submission in the report generation of their main submission.
You can notice that we call <%[RelationName.ChildFieldSystemName]%> in the cells where we want the fields of the child submissions.
Additionally, it is important to note that you need a separate table to enter the relation information (in the sense of the Word table object).