The PivotTable is a very useful tool included in Excel. It allows you to summarise and analyze data from another table in an excel file.
From now, Palisis will allow you to import Pivot files into your Report Templates. This way, understanding your exported reports will be easier and faster.
1. First, you need to create a Report Template:
Reports & Analysis > Configuration > Report Templates
You need to enter a name for the report template, choose the CSV Delimiter (, ; : | ), the CSV Quotes (Quotes, Single Quotes or None), decide if you want to include the headers and finally, the sorting of the data.
After that, you can add columns to the template to include more information about your reporting, for example, bookingID, sales date, sales user, payment account, partner, gross net revenue, etc.
Don’t forget to save the changes!
2. Once the report template is created, you need to export a report using this template. Go to Reports & Analysis > Data Export:
- Choose a date
- Select the Report Template created, in this case, it is called “Pivot Test”
- Choose the document type, in this case, the XLSX type.
- Request New Report Export
3. Once you have downloaded the report, you will create the Pivot Table:
- Open the Excel file
- Select all your data fields
- Go to Insert > PivotTable
In the same file, you will be able to create more than one Table:
Table 1: Gross Net Revenue by Sales user
Table 2: Gross Net Revenue by BookingID (Maybe another, "more interesting" pivot would spark more interest. ;)
*You can learn how to create and use the PivotTable in this link provided by Microsoft: https://support.office.com/en-us/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576
4. After creating the PivotTable, you need to delete all the data of the report, but not the columns name, so the data sheet should be empty.
Now, you need to refresh or clean the PivotTables created:
- Go to Table1 > Analyze > Refresh
- Go to Table2 > Analyze > Refresh
This way, both tables will be empty as the Data sheet:
After deleting and refreshing the data, you need to save as an XLSL file.
Now you have to import the XLSL file to the same Report Template that you created at the beginning.
Go to Reports & Analysis > Configuration > Report Templates > Pivot Test > Choose the XLSL file save previously and save
From now, every time you export a report using the same report template, the excel file will include both Table1 and Table2. You will need to refresh the tables to analyze the data from the new report.