You can export a SharePoint List directly into Excel as a Pivot Table if Excel is open before you do the export.
See below. If Excel is not open, SharePoint exports the list as a table.
You can export a SharePoint List directly into Excel as a Pivot Table if Excel is open before you do the export.
See below. If Excel is not open, SharePoint exports the list as a table.
This SharePoint Forum post titled “Determine a sub-task’s level using SharePoint Designer” discusses an approach for identifying a sub-task’s level.
We needed to identify the sub-task level for reporting in our Office 365 SharePoint solution “SB2O”. There will be different types of customers using our SharePoint small business template, so we needed a more flexible approach to identifying levels. This blog discusses our approach for identifying N levels.
Below is a screen shot of a standard Task list titled “Sub Task Level”. The list contains a parent and 5 sub-task levels. The ID column is the Item’s ID. I added a “ParentLevelId” number column to capture the ID of the parent Item and a “Task Level” number column to capture the task level.
I then developed a SharePoint Designer 2010 workflow. Since a top-level task, in this case the “Parent Task”, always has a null Parent ID, we test to determine if the task’s Parent ID is empty. If so, we set the Task Level to 0 and the ParentLevelId to 0. We then stop the workflow.
If it is not, we set an integer variable called “ParentId” to the Parent ID of the current item. We then set a number variable called “TaskLevel” to the Task Level of the Parent plus 1.
We find the Task Level of the Parent by doing a Lookup on our Task List, “Sub-Task Level”, with ID and the ParentId variable to relate the two items.
We then up date the current item as shown below and stop the workflow.
Below are the completed workflows.
Note that the only reason the ParentLevelId and the Task Level number match is because I entered the items one after another and no task rearrangement. Not a likely user scenario.
Below is our Customer/Prospect Contact List entry form. The user picks the active Customer or Prospect of the Contact person from the drop-down list. The drop-down is a Lookup Column from our Customer/Prospect List, which also includes no longer active accounts.
Naturally we don’t want these non-active accounts on our drop list, so we use a Calculated Column called “Active Customers Prospects” =IF(OR([Customer Status]=”Customer-Active”,[Customer Status]=”Prospect-Active”),[Customer Name],””). The “Active Customer Prospect Name” Lookup field is based on this calculated column.
A complete contact entry looks as follows:
An issue comes up when we make a Customer or Prospect not active. The name does not appear on the drop-down list as desired, but their name also does not appear on the Contact list. See below:
Here is the approach we use to keep the Customer/Prospect Name on the Contact List:
We also turn off on the under Contact List Settings>Advance Settings>Quick Property Editing since using this view requires the Customer/Prospect Lookup column to be on the Views and added “Please refresh page to view changes.”
The blog post “SharePoint Task Plan – Tracking Time” discusses capturing time against a SharePoint Task Plan using SharePoint’s standard capabilities. This post presents an approach of capturing job hours at the Customer/Job level. It is based on Thuan’s post on The Soldier of Fortune blog titled “Building Timesheet Management Solution in Office 365 Without Code” but goes a couple of steps further.
We start by using the Customer List and from that setup a Customer/Job List as illustrated in the screen shot below:
The Job Customer field is a drop-down list from the Customer List. The Job Name field is added in by the user as is the Customer/Job Name. The screen shot does not show the Job City, Job State, Job Zip Code and Sale Person fields that are part of this list.
The Customer/Job field is needed for the Customer/Job Timesheet List below. You will also notice that I added Timesheet Activity as an optional field for billing and/or reporting purposes.
I combined Timesheet Year and Month for grouping as opposed to having them separately as in Thuan’s post. The formula being: =TEXT([Timesheet Date],”YYYY”)&”/”&TEXT([Timesheet Date],”MM”) . By establishing the Customer/Job Name field, it stops users from coming up with their own names and making time reporting unmanageable. See illustration below.
I also added views and grouping for Customer/Job/Date and Staff/Date/Customer/Job so you can see the hours and totals associated with those views. The screen shots in this post are using the New look for lists. Under the SharePoint Classic view, Total Hours are displayed by the selected group.
I then went ahead and generated a PowerApps cell phone application, so staff can enter the data when they are away from their computer. A screen shot of the PowerApps is below:
The PowerApps application above took about 5 minutes develop without any coding. See the post “QuickBooks Employee List PowerApps Example “ for more information on PowerApps.
A Customer Level Timesheet can be developed under the approach outlined above. The difference would be that a Customer/Job list would not be necessary, and the Job Name and Customer/Job Name fields would also not exist.
I’m adding a short post on Wave since it has gotten good reviews and deserves to be discussed with the other accounting systems.
Wave is based in Toronto Canada. Wave’s Accounting, Invoicing, and Receipt Scanning are free. Wave charges for Payroll, Credit Card and Bank payment processing.
Here is a Wave Review 2018 by Cloudwards that provides more information on the product. The reviews I have read give Wave high marks and classify it as very good for single owners/very small businesses. I believe that if Wave is used in conjunction with Office365/SharePoint the combination could meet the needs of many small business and save those businesses money on their accounting software.
Customer are set up under Sales in Wave. Wave provides the capability of importing clients via a CSV file. The customer data in Wave is limited but if you are doing your customer management in SharePoint this would not be a factor. See my post on Customer Management for further information.
The post “The Case for a Project List” – I suggest you should set up projects in both SharePoint and QuickBooks. I also point out that although you can import from QuickBooks to SharePoint, there are multiple steps involved. It is easier to go from SharePoint to QuickBooks.
There is no concept of Job or Project in Wave. You can set up a product or service, but they are single items. Bottom line — this is not a job/project oriented application. However, you could do your project management and bill preparation in SharePoint/Office365 and record the invoices and payments in Wave.
Task Plan Synchronization
As in Xero, there is no concept of Task. You could use the product/service field to track tasks but then you can’t track a project if you use those fields for that purpose. Again, this is not a drawback if you are using SharePoint Task Management capabilities.
The post “SharePoint Task Plan – Tracking Time” discusses time capture alternatives. Wave has a timesheet capability as part of payroll but there is no import capability. Entering the data in two places is necessary if you are tracking hours for payroll and project or customer management.
Wave’s Estimates and Invoice features do not support importing of data. Their related reports can be exported into Excel, so you could update SharePoint with Estimate and Invoice information. As mentioned above, you could decide to undertake these functions in SharePoint.
Wave has no document management capabilities.
Overall Import/Export Capabilities
Wave has limited import capabilities but does provide report export capabilities. Unlike FreshBooks and Xero it does not have an Application Program Interface (API) that can be used by third parties to provide integration.
Like Xero and Billy, if you decide to run your business mostly using SharePoint and Office365, you can use Wave to do your basic accounting, financial statement preparation, payroll and collections. Its added advantage is that the software is free.
Microsoft included Business Center in Office 365 Business Premium. They added “Preview” after the word Center since it’s a new offering. This is the link for more information on Office 365 Business Center Preview. I’m not going to go through Business Center’s pluses and minuses since its new, but at this time it doesn’t even come close to the customer management that you can do through SharePoint.
Office 365 Business Center Preview does have a couple of advantages:
Business Center functions in a closed environment, unlike SharePoint, making it hard to share data. I think Microsoft would have been better off making Business Center a SharePoint application.
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.
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.
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.
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.