How to create a summary report from a LibreOfficeCalc PivotTable

This is a question about the feasibility of solving a problem with LibreOffice/Calc.

I have a table of data of many years with basically the following columns:

date/time ( Y/M/D time )
quantity

Now, I want to make views of this table as follows:

VIEW-1:
quantity by weekday
 . Mon - Sum Qty
 . Tue - Sum Qty 
 .
 . Sun - Sum Qty

.

VIEW-2:
quantity by year/week
 . 2010 Wk 1 - Sum Qty
 . 2010 Wk 2 - Sum Qty
 . 
 . 2021 Wk 33 - Sum Qty

And many, many other views.

My question is as follows.

Is it possible to create such views (reports) with the help of LibreOffice/Calc Pivot tables in a straightforward manner?

If so, could you give a hint as to how to create maybe one of the above views (reports)?

Solution 1:

Here's a step-by-step to produce the two pivot tables in LibreOffice Calc 6.4.

Note the use of ISO 8601 dates and week numbers with Monday being start of week; dates in early January for ISO week 52 or 53 will be listed as week zero (W00) for correct sort order within a year.

Create a data table in a named range by adding 3 columns to the existing Date and Qty columns:

• insert/edit a header row with these column texts: Date, Qty, Year, WeekISO, Weekday
• in the row below the header insert following formulas, editing A2 to match the cell of the first Date and ; to match the list separator of your locale
• in the Year column insert =YEAR(A2)
• in the WeekISO column insert =IF(AND(1=MONTH(A2);52<=WEEKNUM(A2;21));"W00";TEXT(WEEKNUM(A2;21);"W0#"))
• in the Weekday column insert =WEEKDAY(A2;2) and (in Format > Cells …) give the cell format code NN for locale's abbreviated weekday name
• copy the formulas to all data rows
• select (highlight) the table area incl. header row
• in Sheet > Named Ranges… > Define..., enter range name dataset1, Add

Create a pivot table for VIEW-1 quantity by weekday:

• Data > Pivot Table > Insert or Edit ..., named range dataset1, OK
• from Available Fields drag Weekday and drop on Row Fields
• from Available Fields drag Qty and drop on Data Fields
• Column Fields should contain Data
• accept/edit Options + Source and Destination (defaults are okay), OK
• create a custom sort list: select the 7 daynames in the pivot table, in Tools > Options > … Calc > Sort Lists: Copy from …, edit list, OK
• sort the pivot table: click arrow at Weekday header, Custom Sort, choose a list

Create a pivot table for VIEW-2 quantity by year/week:

• as for VIEW-1 except
  • set Row Fields to: Year and WeekISO
  • set Data Fields to: Qty
  • no custom sort
• in the pivot table: swop the pivot fields by clicking header in column B

As you suggest a recurring need to create pivot tables you may be interested in the LibreOffice Basic code to repeat the steps programmatically; assumes the data table is defined in the named range dataset1, and the pivot tables are not. Link to OOo API docs.

Option Explicit

Sub PivotDemo

    Const rawRgName = "dataset1"        '' defined in Calc sheet
    Const pivot1Name = "qtyByWeekday"
    Const pivot2Name = "qtyByYearWeekISO"
    Const pivot1Sheet = pivot1Name & "_auto_pivot"
    Const pivot2Sheet = pivot2Name & "_auto_pivot"
    Const grandTotalLabel = "Grand total"
    Const fieldNameQty = "Qty"
    Const fieldNameWeekday = "Weekday"
    Const fieldNameWeekISO = "WeekISO"
    Const fieldNameYear = "Year"

    '' initialize
 
    Dim cdoc : cdoc = ThisComponent
    Dim scrRaw      '' com.sun.star.sheet.SheetCellRange
    scrRaw = cdoc.NamedRanges.getByName(rawRgName).getReferredCells()
    Dim dptColl     '' com.sun.star.sheet.DataPilotTables
    dptColl = scrRaw.getSpreadsheet().getDataPilotTables()
    Dim dptDesc     '' com.sun.star.sheet.DataPilotDescriptor
    Dim dpFields    '' com.sun.star.sheet.DataPilotFields
    Dim dpfsi       As New com.sun.star.sheet.DataPilotFieldSortInfo
    With dpfsi
        .Field = "__replace_with_field_name__"
        .IsAscending = True
        .Mode = com.sun.star.sheet.DataPilotFieldSortMode.NAME
    End With
    Dim pivotAddr   As New com.sun.star.table.CellAddress
    Dim sheetIndex  As Integer


    '' create pivot table #1 in a new Calc sheet

    dptDesc = dptColl.createDataPilotDescriptor()
    dptDesc.setSourceRange(scrRaw.getRangeAddress())
    dptDesc.ShowFilterButton = False

    dpFields = dptDesc.getDataPilotFields()
    dpfsi.Field = fieldNameWeekday
    With dpFields.getByName(fieldNameWeekday)
        .Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
        .Function = com.sun.star.sheet.GeneralFunction.SUM
        .SortInfo = dpfsi
        .HasSortInfo = True
    End With
    With dpFields.getByName(fieldNameQty)
        .Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
        .Function = com.sun.star.sheet.GeneralFunction.SUM
    End With

    sheetIndex = cdoc.Sheets.getCount()
    cdoc.Sheets.insertNewByName(pivot1Sheet, sheetIndex)
    With pivotAddr : .Sheet=sheetIndex : .Column=0 : .Row=0 : End With
    dptColl.insertNewByName(pivot1Name, pivotAddr, dptDesc)


    '' create pivot table #2 in a new Calc sheet

    dptDesc = dptColl.createDataPilotDescriptor()
    dptDesc.setSourceRange(scrRaw.getRangeAddress())
    dptDesc.ShowFilterButton = False
    dptDesc.GrandTotalName = grandTotalLabel
    dptDesc.RowGrand = True
    dptDesc.ColumnGrand = True

    dpFields = dptDesc.getDataPilotFields()
    dpfsi.Field = fieldNameYear
    With dpFields.getByName(fieldNameYear)
        .Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
        .Function = com.sun.star.sheet.GeneralFunction.NONE '' .SUM for subtotals
        .SortInfo = dpfsi
        .HasSortInfo = True
    End With
    dpfsi.Field = fieldNameWeekISO
    With dpFields.getByName(fieldNameWeekISO)
        .Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
        .Function = com.sun.star.sheet.GeneralFunction.NONE '' .SUM for subtotals
        .SortInfo = dpfsi
        .HasSortInfo = True
    End With
    With dpFields.getByName(fieldNameQty)
        .Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
        .Function = com.sun.star.sheet.GeneralFunction.SUM
    End With

    sheetIndex = cdoc.Sheets.getCount()
    cdoc.Sheets.insertNewByName(pivot2Sheet, sheetIndex)
    With pivotAddr : .Sheet=sheetIndex : .Column=0 : .Row=0 : End With
    dptColl.insertNewByName(pivot2Name, pivotAddr, dptDesc)

End Sub


    '' refresh pivot:  dptColl.getByName(pivot1Name).refresh()
    '' remove pivot:   dptColl.removeByName(pivot1Name)
    '' activate sheet: ThisComponent.CurrentController.setActiveSheet(_
    ''                 dptColl.getByName(pivot1Name).getOutputRange().Sheet )
    '' remove sheet:   ThisComponent.Sheets.removeByName(pivot1Sheet)