HowTo:Pivot Tables

Pivot Tables in Pirouette

Pivot Tables can be created in the Pirouette. The point of a Pivot Table is to sum things. A Pivot Table can only have one Column. To set the Pivot criteria using a date field you must first drag the field into the Rows area, select the option from the drop-down, then drag the button to the Pivot section.

First Step in using the Pirouette is to create the Definition. The Main Object must be the lowest level the data comes from. For Example: Analyzing Expense Accounts on an AP Invoice is taken from the AP Invoice Lines rather than the Invoice itself because the GL accounts are attached at the AP Invoice Line level.

Add the desired Properties to the Pivot Table Properties area. The Property names can be found on the Main Table by displaying Help.

If you want to expand on the above by adding in Vendor Name, you need to add in a related object because it is on the AP Invoice rather than the AP Invoice Line. To do this you manually add the related object from Relationships to the specific line detail of the Pivot Table Properties (to get to the line detail of Vendor, double-click on the blue square at left of Vendor line).

Definition – Pivot Table Properties:

• Description: the abbreviated text that appears on the button
• Long Description: longer text describing the button
• Property: Property name of the piece of data
• Rel Type: Rel Type to the Property selected, if one
• Mask: sets the input mask
• Group Name: Name for multiple data combined into one (i.e. Address)
• Col Width:
• Detail ID:

Creating a Pivot Table

• Select a Pivot Table from the left pane
• Click Fields
• Drag and Drop the fields into the desired Build Area (Rows, Columns, Filters, Pivot).
• Click Rebuild to see the results
• Save the table as a Preset

Helpful Hints:

• Start with a small amount of data while building the table
• Prove out the data to verify the build is correct
• Save Preset (give it a meaningful name)
• Continue to add fields, test, and verify build is correct
• Save (overwrites the current Preset)
• To Save as a new Preset, preserving your current Preset, typeover the name with a meaningful name of the new preset and save
• Drill down into data by double-clicking on a field in the results area