In my last blog, I showed how to build a straight table using the Lawson M3 AR data. Continuing with the same document, I will show how to create Pivot Table showing the outstanding amount in 0-30-60 days breakup.
In the worksheet right click on the mouse button to bring the popup context menu, and click on New Sheet Object –> Chart to bring the Chart object designer as shown below.
Type Pivot Table Outstanding Amount in the Window Title Text Box, and select the Pivot table in the chart type (last icon on First row) .
Click Next button to bring the next window Dimensions. Here we can select the Dimension fields to be displayed in the pivot table. Highlight the fields CustomerNumber and CustomerName in the Available Fields/Group box, and click Add. The highlighted fields will be moved into the Used Dimension box.
Click the next button to bring the Expression Window and will display a popup window Edit Expression. Type Sum(If(DueDays <= 0 ,OutStandingAmount,0)) in the box and click OK button .
Add additional 3 more Expression as shown below.
Add the Labels to all the four expressions
Click the next buttons and we will accept defaults in the Sort, Presentation and Visual Cues,Style windows.
On the number windows, select Money for Number Format Setting for all the expression as shown below.
Click finish to display the Pivot table.