Using SharePoint Online & Power BI Desktop to Solve the QuickBooks Online Salesperson Commission Void

The most voted on QuickBooks Online (QBO) feature request under the Customer Center category is Enable a Sale Rep to be assigned to a Customer/Client. This request has been sitting around since 2012 and was flagged as “Under Review” in 2016. Intuit has kind of address this request by suggesting that an Invoice custom field be used to assign a salesperson to a Customer Invoice. However, I’m not sure that this meets the needs of most users that want to report on their salesperson/customer relationship. Additionally, several comments under this feature request suggest the ability to compute sales commissions on a billed and collected basis be provided.

This post illustrates how these reporting needs can be met using SharePoint Online, Power BI Desktop, and Excel. I suggest the reader first review the posts QuickBooks Online & SharePoint Online Synchronization, Power BI and SharePoint, QuickBooks Online & SharePoint Online Synchronization Revisited, Using Power BI & SharePoint Online to Solve the QuickBooks Online “Type” Void, QuickBooks Online Tables via Microsoft Power BI Desktop Content Pack.

The post SharePoint for Customer Management and many other posts on this blog illustrate how SharePoint Online can be used for customer relationship management. Below is a screen shot of Customer & Prospect List using the Active Customer List. Here is where the Salesperson to Customer relationship is established and this used the Desktop Power BI report showing sales commissions by salesperson on both a billed and collect basis.

Note: A non SharePoint Online user could also use an Excel spreadsheet with Customer and Salesperson as subsititue for the above Customer & Prospect List.

Next I decided to use a simple Excel spreadsheet to track Salesperson and their commission percents.

I should note that in real life I would keep this information on the Human Resources SharePoint subsite.

As mentioned in the post QuickBooks Online & SharePoint Online Synchronization Revisited referenced above, I uploaded QuickBooks Desktop’s Larry’s Landscape and Garden Supply sample company into QBO. The sample company has more data for 2019 so that is the year I will use for the sales commission report. I will assume that Salespersons are paid commissions on a quarterly basis and focus in on the 4th Quarter of 2019.

I then modified QBO’s Sales by Customer Detail report as shown below. I ran the report for both accrual and cash basis and exported the reports to Excel.

I then opened Power BI Desktop and connected to the above Excel reports as a data sources, the SharePoint Customer & Prospect List, and the Salesperson Commission Spreadsheet. I then set the relationships:

I then modified the Excel Accrual spreadsheet table.

As you can see, I eliminated the columns that were not necessary. Notice that I did not eliminate the Memo/Description column. This is because if there are shipping and finance charges on an Invoice it tells you that in this field. I set up separate shipping and finance columns based on the formula in the screen shot. I assumed that a salesperson is not commissioned on Shipping, Finance Charges and Tax so I added a new column called Commission Base which is Amount-Shipping-TaxAmount-Finance Charges. I also computed the Commission Amount based on the Salesperson Commission Spreadsheet and assigned the salesperson based on the SharePoint Customer & Prospect List.

I made the same changes to the Cash basis version of the spreadsheet.

I then added 3 Visualizations to review the data and placed each on its own page.

You will notice that I did not use the invoice and payment tables available through Power BI Desktop data source connector for QBO’s. While developing this post, I ran into difficulties with how the invoice and payment data is extracted from QBO. There were duplicate amounts that I could not eliminate. My work around was to use a QBO custom report and export it to Excel. This is disappointing since the connector would allow refreshing of the data directly from QBO. If data changes after exporting the Excel spreadsheet, you need to reimport it.

QuickBooks Online Tables via Microsoft Power BI Desktop Content Pack

The post Using Power BI & SharePoint Online to Solve the QuickBooks Online “Type” Void discusses how the three products can be brought together to improve management reporting and fill a void in QuickBooks Online (QBO). I will be publishing more posts on how SharePoint Online and Power BI can address other reporting voids in QuickBooks Online. Therefore, I thought it would be a good idea to first list the tables currently available through the Power BI Desktop Content Pack for QuickBooks Online. The post QuickBooks Online & SharePoint Online Synchronization Revisited provides some additional background information.

Below is a screen shot of the current QuickBooks Online tables listed in the Power BI Desktop’s data source Navigator. As you can see there are 39 tables and 2 functions.

Power BI tries to figure out the table relationships for all but a dozen or so tables but doesn’t do a great job of it. Many of the tables are also de-normalized so you don’t need them all to do reporting. The GeneralLedger table is a good example because it contains Account Name, Customer Name, etc.

Using Power BI & SharePoint Online to Solve the QuickBooks Online “Type” Void

The post QuickBooks Online & SharePoint Online Synchronization Revisited provides an overview of how Power BI can be used to not only synchronize both online products but also integrate them. The post SharePoint for Customer Management discusses how a SharePoint Online list can be used for customer management. One of the fields in the SharePoint Online Customer list is “Customer Type”. While the QuickBooks desktop version supports various “types” for financial and management reporting, the QuickBooks Online version (as of the time of this post) does not support these capabilities. Below is an illustration on how Power BI, SharePoint Online, and QuickBooks Online can be brought together to full this reporting void. The illustratiom is a Power BI report displaying total customer billings by customer type.

The first consideration is how we are going to sync the SharePoint Online Customer List with QuickBooks Online customers. The post QuickBooks Online & SharePoint Synchronization discusses how customers can be imported and exported using Excel. I’m going to assume that SharePoint Online is being used as either the CRM or lead tracking application. Consequently, the customer data in SharePoint Online is imported into QuickBooks Online via Excel.

Here are the overall steps I undertook to develop the report in Power BI Desktop.

1. Because I already established a connection to my SharePoint Online site, I chose Recent Sources and then the SharePoint connection.

2. Selected the Customer & Prospect List table and then edited the query to only include Customer Name and CompanyType. I also renamed the columns Customer and Customer Type.


3. I connected to the QuickBooks Online data source, you can see it in 1, and selected the Customer table. I eliminated all the columns expect for CustomerParent, Customer Full Name, and Id. Renamed Id to CustomerId and CustomerParent to Customer Name. The reason I needed CustomerParent is because QuickBooks Online treats jobs/projects as Sub Customers. The total billings report that I am illustrating is at the Customer level so we need to know the parent name.

4. I then selected the QuickBooks Invoice table and only kept the columns you see below.

5. I then set the relationships between the quires.

6. Just to be on the safe side, I copied the Customer Name from both the SharePoint Online Customer & Prospect List and QuickBooks Online Customer quires and pasted them into Excel so I could compare them via a formula to make sure the names are exactly the same.

7. Lastly I added the three tables you see below to form the report.

For illustration purposes, I kept the report simple. Parameters could have been added for dates, amounts >, etc. Graphs and drill downs could have been used for visual effects. My purpose was to show how Power BI and SharePoint Online can be used to improve financial and management reporting for QuickBooks Online users.

Note: Organizations not using SharePoint Online can use a simple Excel Worksheet with client name and customer type to develop the same report.  Power BI Desktop comes with a connection for Excel data sources.

QuickBooks Online & SharePoint Online Synchronization Revisited

The post QuickBooks Online & SharePoint Online Synchronization discusses how the two products can be synchronized – the bottom line in that post being not very well. I decided to revisit this post after examining Microsoft’s QuickBooks content pack for Power BI . I briefly discuss Microsoft’s Power BI in my post Power BI and SharePoint. Power BI content packs connect to a service, in this case QuickBooks Online (QBO), and then create a Power BI dashboard and a set of Power BI reports. As Microsoft points out, the content pack is in beta and might change, so any reports and dashboards you develop may not work/need to be changed in the final version.

I uploaded QuickBooks’ Example Larry’s Landscape and Garden Supply company into QBO. (As a side note, the upload went smoothly.) I then used the Power BI content pack to connect to QBO. The content pack quickly developed a dashboard and reports for Larry’s Landscape and Garden Supply based on the example company. Below is screen shot of what was in the dashboard. The example company has very little data and its dated so the dashboard looks funny but it gives you an idea of what the content pack generates. Also Power BI map visualizations can generate odd results when first generated…I didn’t bother to go back and clean up the Customer/Client Map. You can drill down on the reports to see more detail. A neat feature of Power BI is the “Ask a question about your data”, it generates reports based on your question and based on my experience it does a pretty good job of providing the information.

As I mention in my SharePoint & Power BI post you can develop dashboards and reports for SharePoint Online lists and embed them on a SharePoint page. Power BI allows you to use multiple data sources in report development. Consequently, you can combine SharePoint List data and QBO data to develop integrated reports. Below is screen shot of a Power BI Desktop where I’ve taken in the Invoice table from QBO and the Customer & Prospect List from SharePoint so I can develop an integrated report using both data sources.

Combining SharePoint Online, QBO, and Power BI can provide small businesses with a rich environment for managing their businesses.

Power BI & SharePoint

Microsoft’s Power BI is a powerful tool for reporting against SharePoint Online Lists. Click Power BI Website to find more information about it and its capabilities. You can embed Power BI dashboards and reports directly into SharePoint web pages. In future posts, I will discuss using Power BI, QuickBooks Online, and SharePoint Online together. There is a free version of Power BI that can meet the needs of most small businesses. I suggest all SharePoint Online users check out Power BI.

Example SharePoint Survey Application

SharePoint comes with an easy to use survey application that can be used for just about any type of survey (e.g. customer, employee, vendor, etc.). This blog post illustrates using the survey application for an internal employee survey. We use Monster’s “Employee Survey: Use this Sample to Build your own Questionnaire” to illustrate setting up a SharePoint survey.

Select the app by going to Site Content>Your Apps>Apps You Can Add and on the second set of apps is the Survey app. Double click on the icon and the app will be added to your SharePoint site. In this case, we added it to the Human Resources subsite.

Once added, the Employee Survey app looks like this:

The survey name and description is set through the app General Settings page illustrated below.

Notice the Survey Options settings. I decide for this illustration that I don’t want user names showing up in survey results. If I had set it to Yes, only users with the proper permissions could see the user names. Also I want to limit the user to one survey response. Setting “Allow multiple responses?” to No means the user cannot access the survey once they fully complete it the first time.

Below is the screen shot of the actual Employee Survey web page. You can’t tell by looking at the screen shot, unless you are already familiar with SharePoint surveys, but there only two questions. The first question is “Sample Employee Survey*” and the second is “Comments:”.  The * meaning required to be completed.

The numbered questions you see under “Sample Employee Survey*” are sub-questions. I set them up as sub-questions because I want to use apps built in Rating Scale capability for answering the questions. You set the scale range and the Range Text you want to use. Notice there is “Branching Logic” in the settings. I’m not showing that in my screen shot because I’m not using it, but that capability allows you to control the next question the user sees based on the answer to the current question.

Instead of using the built in Rating Scale, I could have set each sub question up as a separate question and required a 1 to 5 number response (or anything else). I felt it was important to allow employees a submit comments so I set the Comments question as multiple lines of text.

The Survey app comes with a “Show a graphical summary of responses” and a “Show all responses” view. Screen shots are shown below.

Notice that the Created By is hidden since opted not to include user names in the survey.

Setting up this Employee Survey took me about 5 minutes and it furthers show has SharePoint Online can be used to monitor and improve your business.

Dashboard & View Ideas – Taking the Project List One Step Forward

The post The Case for a Project List discusses the advantageous of setting up Jobs/Projects in SharePoint. Reporting being one of the major advantages. Let’s look at an example.

On the Project List web page, I developed a Monthly Estimate view based on the listing. The screen shot is below:

I grouped the Customers and Projects by Service Type and then computed the Monthly Hours and Monthly Revenue. The computation was easy to do since I knew the Frequency, Estimated Work, and Project Rate. Monthly Hours and Monthly Revenue are what SharePoint calls “computed columns”. They do not require programming but they require an understanding of SharePoint formulas. Anyone on your staff that understands Excel formulas can set them up. You may be thinking this view is helpful but where are the totals? You cannot total on computed fields without doing some custom programming. Since you’re a small business and don’t have either that type of staff or money, I don’t recommend doing it.

There is a much better solution and by the way a picture is worth a thousand words. Connect & Export this view to Excel:

Then develop several pivot charts and place them onto the web page to view the data. Each time the Project Lists changes the pivot charts are automatically updated with your changes. BTW pivot charts are extremely easy to do with Excel 2013 or 2016.

Below are couple of possible pivot charts and tables.

The Case for a Project List

In my post Job/Project setup alternatives, I discuss  a couple of alternatives once you get a project and want to monitor its progress and results.  To recap:

  • Set up a project team site if the project is either large and/or complicated.
  • Set up a separate task plan to manage the project.  This is a subset of the project team site.

In the post We Got The Job! Now What?, I discuss maybe not even setting up a project in SharePoint and just setup the job in QuickBooks.  I then list reasons why you may want to set up projects in SharePoint.  The last reason being reporting across jobs/projects. This reason is why you should seriously consider setting up projects up in SharePoint.

So to illustrate another project alternative, I took the customers and jobs for QuickBooks’ Sample Larry’s Landscaping and Garden Supply company and set them up in Project List. Here is screen shot of the list:

Projects List

I added Frequency, Estimated Work, Rate, and Estimated $ as fields not found in QuickBooks.  The frequency and estimated work is need for  customer & employee scheduling.  Since I already know the rate, frequency, estimated work, I can make a projection of total estimated customer revenue.  I added Service Type since I didn’t like the way QuickBooks sets up a separate description for the service being provided.  Service Type also let’s me report across all projects. Noticed that view we are looking at is only Active Jobs.  I also have views for Not Active Jobs and All Jobs.

I could have added similar information as is found in QuickBooks like Project Address, Project Contacts, etc. if I so chose. Based on this Project List, I can easily copy and paste the job information into QuickBooks as outlined in the import/export post.

I also decided to place this list on the main firm website since it will probably be accessed in various subsites.  Not only Service & Delivery for scheduling but also Marketing & Sales for customer relationship management.

Another advantage about having project information on SharePoint is that I can easily customize my list views to meet a particular business need.  The example I use here could not have been done in QuickBooks. It just doesn’t have that flexibility.   In fact, I had to run several custom reports and export them to Excel in order to put the data together the way I wanted it.

You may also want to either attach a copy of the Service Agreement to the project row or hyperlink the service agreement to the Service Agreement Library so it can be viewed directly from the list.

Dashboard & View Ideas – Expiring Service Agreements

I will include various ideas for dashboard and view items as they come up.

Here is one for keeping track of service agreements that are expiring with the next 30 days:

Expiring Agreements

This 30 day view is based on the agreement end date.  I added this to the Service Agreement Document Library web page.  To make it more prominent, it could be added to the main Marketing & Sales web page.