While Jet Express does have its limitations, there are ways to use Excel to make a more powerful report. When creating reports you may be missing crucial calculations, which could hinder your report. With Calculated Fields you can create these missing formulas. In the example below, I need to show Customer Sales, Cost, and Profit. However, the Customer Ledger Entry NAV table does not have a Cost field. Using Calculated Fields a formula can be created using Sales minus Profit to calculate Cost which can be used in a PivotTable report. Once a Cost field is added PivotTable reports can be created, charts and graphs can be inserted from the PivotTable reports, and the charts and graphs can be transferred to a new dashboard worksheet. Along with the charts and graphs, slicers can be added to uniformly filter both the charts and graphs, as well as the PivotTable reports. To view the report results quickly for each chart and graph a hyperlink can be created to link them together.
Using Calculated Fields
Using the following steps, a Cost field can be added:
1) Insert a PivotTable from the Jet Express Table Builder report
2) In the Calculations section of the Analyze ribbon select Fields, Items & Sets, then Calculated Field
3) Name the Calculated Cost field Cost ($) using the formula “='Sales ($)'- 'Profit ($)'”
With the Cost field added, a PivotTable can be created to show Sales, Cost, and Profit by the chosen fields.
Charts, Graphs, and Slicers
To create a chart or a graph simply select the PivotTable report, navigate to the Insert ribbon, and select the Charts section.
Once the appropriate chart or graph has been selected, it can be cut or copied and pasted to a new worksheet.
Slicers can be inserted from the PivotTable report, then cut or copied and pasted to the dashboard worksheet similar to the charts and graphs. If more than one PivotTable report is created, then the slicers need to be linked to all of the PivotTables. To link a slicer to a PivotTable:
1) Right-click the slicer and select Report Connections
2) Check the PivotTables the slicer should filter and select OK
To link charts and graphs from the dashboard to the corresponding report worksheet a shape can be created within the chart and graph, then hyperlinked to the correct worksheet using the following steps
1) Click the chart or graphs
2) Under the Illustrations section of the Insert ribbon select the Shapes dropdown
3) After formatting your shape, right-click the shape and select Hyperlink
4) In the Hyperlink window select Place in This Document and select the worksheet the corresponding chart or graph’s report is located
For more detailed information on this topic, watch our on-demand webinar
This article was written by Greg Zavela, a consultant at