QuickBooks Advanced Reporting – Building A Report

by

In this example of using the QuickBooks Advanced Reporting feature for QuickBooks Enterprise, we'll look at building a dashboard that illustrates sales profitability by customer.

In order to build this report, you must first build a pivot-table showing Profitability by Customer, and then edit an existing pivot-table to create a Profit Margin by Customer bar chart.

.

Building a Pivot-table showing Profitability by Customer:

1) From the list of available Reports, select the Start from Scratch

2) In the Library, select Text & Utility. Double-click to add the following items:

  • Current Selection
  • Report Title

3) In the Library, click Date

4) Double-click Transaction to add it to the report

5) Right-click Report Title, and then select Properties

6) Within the Text field of the General tab, enter Sales Profitability by Customer Dashboard (shown in the red box below)

7) Click OK

8) Within the General tab, right-click on any area of white space; now select New Sheet Object > Chart

9) In the Window Title field, enter Profitability by Customer

10) For the Chart Type, select Pivot Table (shown mid-figure below in the red box)

11) Select Next at the bottom of the General window (shown in red box above)

12) Review the Dimensions window for Available Fields/Groups

Note: It may be easier to narrow down the fields you want to add as dimensions using the Show Fields from Table drop-down list below the Available Fields/Groups list (shown above).

13) Add the Transaction.Customer Full Name dimension from the Available Fields/Groups

14) Within the Label field of the Dimensions Window, enter Customer

15) Check the Suppress When Value is Null box to specify you don't want to see anything that doesn't have your assigned value

16) Click Next

17) In the Edit Expression window that appears, enter the following expression

18) Click OK

19) Within the Label field of the Expressions window, enter Sales Revenue

20) Click the Add button to enter this expression in the Edit Expression window:

21) Click OK

22) Within the Label field of the Expressions window, enter Cost

23) Click the Add button to enter this expression in the Edit Expression window:

24) Click OK

25) Within the Label field of the Expression window, enter Profit

26) Click the Add button to enter this expression in the Edit Expression window:

27) Click OK

28) Within the Label field of the Expression window, enter Profit Margin

29) Click Finish

.

Editing a Pivot-table to Create a Profit margin by Customer Bar Chart:

1. Locate the Sales Revenue pivot-table, and then right click so select Clone

2. Drag and drop the cloned pivot-table to an open area

3. Right click on the cloned pivot-table, now select Properties

4. Within the Window Title field of the Chart Properties General tab, enter Profit Margin by Customer

5. Change the chart type to Bar Chart

6. In the fast Type Change selection, select Line Chart

7. In the Expressions tab of Chart Properties, highlight each of the following expressions, and then uncheck Enable to disable each of them:

  • Sales Revenue
  • Cost
  • Profit

8. Open the Edit Expressions window by double-clicking on Profit Margin

Alternatively, you can click the three-dot [...] button next to the Definition field

9. Notice that the current expression for Profit Margin references both the Profit and Cost expressions you disabled in Step 7 above. You will need to edit the expression so that it no longer references those expressions, but still defines Profit Margin.

In order to do this you must enter the following expression into the window:

(Note: This expression is wrapped in the above text, but it will be entered in continuous line fashion within the Edit Expression window.)

10. Click OK

11. Within the Axes tab of the Chart Properties window, click the radial button for / within the Dimension Axis settings for the Primary Dimension Labels

12. Change the font size of the Dimension Axis by clicking Font > choose size > OK

13) Click OK to view the finished dashboard

The QuickBooks Enterprise Advanced Reporting feature makes otherwise complex work simple with some easy to follow guidelines and practice examples. But you're not simply going to point and click the way you do within QuickBooks standard "canned" reports. 

This feature will require a reasonable learning curve to take full advantage of the capabilities the tool provides.

Thanks to Intuit for the illustrations, learning exercises and guiding content used in this feature.