How to Utilize Pivots in Reporting to Build Trend Reports

The pivoting functionality  in the Reporting/ Analytics module can be useful in building out trend reports. You would begin by going into design mode on your existing report OR by creating a new report. 

The pivoting functionality can be found on the FIELDS tab when in design mode. 

Once you've made your selections of your fields that will be located before the pivot, you will then click on the Add Pivot button found underneath the fields.

This will then enable an additional table.

First you must select which field you wish to pivot by in the field after "Add pivot columns for". For trend reports this is typically a date for example: Post Date, Paid Date, or Period Date. For this field you will also choose how you wish to group this field in the function drop down. In our example of date you can choose to group columns by month, quarter or year as well as a few other available options.

Next you must select which field will be the value you are pivoting out in the field after "Calculate cell values on." For example, this could be a sales amount or a paid rebate amount. For this field you then will select your grouping function, if this field is a dollar amount you will want to choose SUM.

As an additional tip: when building a trend report with pivots you likely will want to include a filter with a start and end date such as the example below so that you can run the report for a specified time period to see the trend.

Remember to require at least the first filter before saving the report so that the report will open quickly when you go back to run it.