FAQ - How to drill down on Month and Year grouping.

PivotPoint allows you to 'drill down' to the set of list items related to a particular cell, row, column or chart item. For example in a pivot table showing tasks by priority and team member by clicking on a cell you could go straight to a list showing only "Nancy's high priority tasks".

However you're not able to use the 'drill down' feature at the same time as Date Grouping, for example if you wanted to show your sales grouped by Month and Year.

This is because SharePoint is unable to filter records on ranges like "1st June to 30st June" but instead we can do this grouping using a calculated column and then pivot and filter on that grouped column.

Create a new column in your list and choose the "Calculated" option.

Set the formula to

=TEXT([Purchase Date],"(YYYY-MM) MMM YYYY")

where [Purchase Date] is the name of your field.

Leave the data type returned as "Single line of text"

Uncheck "Add to default view" - we don't actually need to display this column to be able to use it in PivotPoint.

This will change a date into the format "(YYYY-MM) MMM YYYY", for example "(2011-06) Jun 2010". The first part of the date in brackets allows correct formatting (otherwise April comes before January) and the part outside the brackets is useful for display. You can later crop the sorting part out of the row/column labels that PivotPoint displays.

You can now change your PivotPoint web part to pivot on that by row or column and also drill down to see, for examples, all the invoices for that month.

Tip - You may want to look at our SharePoint Calculated Column Cheat Sheet for formatting examples you can use with the Text function and other handy formula to use with calculated columns.