Pivot table report [in-depth guide]

Guide contents: Edit view | Import/create a working example

Reports have now become a lot more dynamic with the new Pivot table features in custom reports. While there are ways to create columns of data in the report engine, it was not able to be dynamic and would create a lot of work to create the reports for each date range or scenario. Now with pivot, you can use its drag-and-drop layout features to create dynamic columns of data based on what data is retrieved.

NOTES:

• This feature currently works with custom report datasets only. Standard reports and views are excluded.
• At this time, we do not suggest trying to print landscape as a PDF. Please print/export to Excel and then print that file.
• DO NOT use the Filters section within the Pivot tab. You can however use the Filters tab of the regular report features to filter data.

Edit view

From the custom report click on Edit to open the Edit View screen.

Layout

From the Layout tab, you first want to add the columns that are to be available in the pivot table. For calculated columns, the Caption field will be used to "name" the field in the pivot table view (version 21.04.18.0000 or higher)

Filters

The standard filters in the reports editor still apply and are quite useful.

NOTE: Do not use the filters within the Pivot tab.

Settings

Allows you to edit the name of the report. It can also define access to the report by security group, and allow the report to open showing the pivot

Pivot

From the Pivot tab of the Edit view, you can choose the data that best suits the columns function. Typically this would be a date type of field.

NOTE: Do not use the Filters section within the Pivot tab. You can however still use the Filters tab above to apply the desired filtering of the data.

Import/create a working example

To import a working example of this feature, please try the following:

1. Download the File below.
2. Go to Menu > Admin/manager > Import data.
3. Choose View in the Type of import drop-down.
4. Click on Select a file to import.
5. Choose the Downloaded file from below.
6. Click on Display file.
7. Click on Import selected.
8. Navigate to the Report center.
9. Look for Client billings by month pivot.
10. Open the report and click Search to see if data populates.
    If yes, then move on to the next step.
    If no, then most likely you have no client invoices in the current year to date. You can go to the Filters to edit or remove the date range restriction.
11. Click Edit to modify the report and then click on the Pivot tab.
12. In the Pivot field list, check all that is there.
13. From the Rows, Drag and drop the Invoice month into the Columns.
14. Click on Apply changes. Then click Memorize from the report.
15. Click Search and be sure you are on the Pivot tab in the upper right.

You should see something like this:

If you have any questions or feedback about this feature, please let us know at support@workamajig.com.