Excel has long been the quick answer to QuickBooks reporting woes. Although it is inefficient and often inaccurate, it was the best there was for many years. All of that has changed with XpandedReports (XR).
Use Excel with XR "Flat File"
Need: There are many people who are Excel gurus and they have no intention of giving up Excel as a "tool" of their trade. However, every single one we have spoken with still sees a need for XpandedReports to do their job more efficiently and/or to provide a "self service" tool for some of the client's more basic needs.
- No longer is the export, manipulate, re-export, re-manipulate process required.
- Most of the common changes (and many more) to QuickBooks reports, which previously required Excel, can be made in XpandedReports
- With a quick click of the Refresh button, the data is automatically updated from QuickBooks. No more working from inaccurate reports. And no more duplicate data entry or error prone cut and paste procedures, ever.
- Excel still has a place in this new work flow: as a way to archive historical reports or to send data to individuals without access to QuickBooks and XpandedReports.
- Excel best practices are built into XpandedReports without Excel knowledge needed
- Date parsing automatically included in XpandedReports (day of the year, month, quarter, etc)
Use Excel with XR "Flat File"
Need: There are many people who are Excel gurus and they have no intention of giving up Excel as a "tool" of their trade. However, every single one we have spoken with still sees a need for XpandedReports to do their job more efficiently and/or to provide a "self service" tool for some of the client's more basic needs.
In Excel: Starting with the report from QuickBooks often requires manipulation to remove subtotal lines, formatting, etc. This can make the process more complex and time consuming.
In XR: A major advantage of XR for Excel users is the ability to "reset to default" which removes all the grouping, subtotals, etc. so the data is just a "flat file" which can be exported to Excel for further manipulation. This is much easier and efficient that removing that information from a QuickBooks file or using the qODBC to extract the data.Memorize report for future use
Need: Accounting data reports are frequently “recurring” reports, needed daily, weekly, monthly or quarterly.In Excel: Setting up automatic recurring reports in Excel requires the use of an ODBC connection to get the data in to Excel. Alternatively, a user could export data from QuickBooks into a pre-determined workbook and worksheet or export and then copy/paste it into Excel. Once the data is in Excel, great because the formulas and Pivot Tables can the automatically update. The process of getting the data in to Excel however, is typically an issue. With XR, that issue is eliminated.
In XR: A major feature in QuickBooks reporting is the ability to save a report layout as a template for reuse. XR is designed to act look and act like QuickBooks memorized feature. When the memorized report is displayed the current data from QuickBooks is available with no modifications needed. XR memorized reports can be used with other company files, and the template can easily be shared with other users of XR.
Live Data in Excel
Need: This is really a misnomer: Usually what people want is their report, their way, with real time data, but the report is not available in QuickBooks directly.In Excel: To create an Excel report that uses "live" data usually requires QODBC, a third party utility. QODBC pulls in QuickBooks transaction data, but not QuickBooks reports. Thus, generating reports in Excel requires pulling in data from QuickBooks, and moving the data out of QuickBooks into Excel can be challenging, even with third-party utilities like QODBC. Since XR reports always run from the live QB data, this issue is eliminated.
In XR: With XR, the data fields are readily available with user friendly names. Just click on the data to include on the report. No joins, figuring out which table is used, etc. For the report to include the most current QuickBooks data, simply click on the Refresh button at the top of any report.
Build calculated fields easier (including range names, testing, etc.)
Need: The are countless reasons to add formulas in a report, merging two columns of data together, performing a calculation based on data in multiple columns, looking at the data in a specific column to create a new column, and more.In Excel: Calculated fields in Excel are easy, and a joy to create. However, one problematic issue is ensuring that after the reports are generated the calculated fields will be properly maintained down the column and through subsequent periods. How can you easily ensure that another user won’t accidentally replace a formula with a value or an incorrect formula? While using worksheet protection may help, XR eliminates the need for extra security since report data can’t be overwritten by the user.
In XR: Multiple features have been built into XR to facilitate adding formulas quickly and accurately:
- There are no hard coded amounts or inconsistent formulas which result in unexpected results.
- It is impossible to have incorrect ranges, circular references, etc.
- There is no way for people to just type in numbers to "break" the spreadsheet.
- Best practices are used with column names (Amount, Quantity, ..) rather than the spreadsheet cell reference approach (A1, B1, C1,..).
- A test feature is provided to validate a formula prior to adding to a report. Values can be entered to validate different calculation scenarios.
- Calculated columns are available for both detail and summary reports.
- See more features and formula comparisons with screen shots.
Pivot Tables with a single click
Need: Pivot tables is a common Excel term which refers to a way of manipulating and summarizing data. The data is summarized in both row and column form, and then this data is move around in unique ways to answer business questions.In Excel: If you love Excel Pivot tables, then you will feel right at home with XR’s report interface, which has been modeled after Excel Pivot Tables feature. Creating Pivot Tables that are based on multiple underlying data sources can be manual with Excel, but XR can generate Pivot Table reports on live data from multiple data tables.
In XR: Creating and editing an XR Pivot table is quick and easy
- There is no preparation of data necessary, all of that work has been done automatically by XpandedReports.
- To create a pivot report, simply click on the button and choose your data.
- As part of the process, XR automatically parses the dates making it quick and easy to click and drag for creating columns for year, quarter, month, etc.
- Easily change data to rows, columns, amounts, and filters with a simple click and drag.
- Modify Report to add more data. Choose the data and it will appear on the field list for use on the report. The same is true with calculated columns. Add a formula and it will appear on the field list for use on the report.
- All summary reports in XR are done as Pivot tables and can be memorized for ease of reuse.
Minimize errors by changing existing reports
Need: Add more data, group or subtotal in a different way, hide data columns.In Excel: If your Excel report’s starting point is Quickbooks data, then adding new accounts, customers, vendors, etc will necessitate manually inserting a new row on your report (unless your report is a Pivot Table of course.) XR’s capabilities include automatically including new accounts, customers, vendors and so on in the dynamically generated report, eliminating the need to manually add new items to the report. Additionally, changing the structure (including columns, column order and subtotals) of an Excel report can be time consuming and a manual process, depending on how the report was set up. XR report structures are easily changed and rerun with live data. Additionally, if the QB Export isn’t in the right format, then formatting it properly can take time, or the knowledge of Macros.
In XR: Many QuickBooks type reports are available as a starting point and can be manipulated as needed. With the familiar user interface, simply modify the report to add more data to the report. Right click on the column heading to group, subtotal, or hide data.
Merge Report Data
Need: Combining data from one or more reports with another report.
In Excel: When combining data from two or more data sources, you’ll need to use formulas like VLOOKUP(), MATCH(), INDEX() and OFFSET(), and you’ll also need a common field to perform the join. Then of course for recurring reports, you need to babysit the formulas to make sure they are filled down properly each period. Using external data ranges with QODBC can alleviate some of this frustration, but keeping the QODBC connection consistent across multiple users and multiple QB files can be tedious.
In XR: There are two ways that XR has addressed this specific need:
- From one report as a starting point, many of the additional or related data fields have conveniently been added. Simply open the modify dialog and choose the required field to add the column to the report. Below is an example of the data that is available from QuickBooks versus XR for a Time Report. First, here is QuickBooks:
Then for XR. Note that there is additional data available. To Add the Payroll Wage, for example, in Excel, would require exporting the time report, and the Employee Wage report then matching the payroll item between the two reports to use the information. You could then do a calculation of the hours at the specific rate. But again, matching the two, adding subtotals, etc. will require some sophisticated Excel knowledge. With XR, you check the box to add the data to the report, and it is there with all the same formatting, subtotals, etc as was on the report previously.
- The Expanded Report Menu has many examples where the work has been done so you can do one step further. The example above, using the time was very simple. Using the time to create pre-billing worksheets can be more complicated based on price levels, billing rates, etc. With XR, the same rules QuickBooks uses are already built into the report. No complicated formulas are needed, just click on your desired format for cost or revenue or both, and the XR will extract the data from QuickBooks and return the report.
Security
Need: The accounting data is extremely confidential and should be closely guarded.
In Excel: Excel’s data security is independent of QuickBooks user permissions, and thus protecting the report files requires some extra security measures (Network security, Windows security, Workbook protection, Worksheet protection, and so on). XR security control is inherited by the QuickBooks user permissions, allowing users to only see the data they have permission to see while logged in to QuickBooks.
In XR: XR does not store any information, all data is read in real time from QuickBooks. XR can only access the information based on the current user that has QuickBooks open when using XR. If a report is attempted for data which the specific user does not have permission, the following error will appear.
Data snapshots
Need: When reviewing financial information for decision purposes, having current, accurate information is critical.
In Excel: Having multiple versions of Excel reports on the network can create confusion about which version is the latest version. XR runs on live data for the reports.
In XR: Since the data is read live from QuickBooks, everybody has the same view of the data.
Filters
Need: Reports are almost always prepared based on a subset of the full data. Some filters may be as simple as a date range. Others may become more complex.
In Excel: If you enjoy Excel’s powerful ability to filter table data, then you’ll be comfortable with XR’s filtering capabilities.
In XR: The filtering is far superior to QuickBooks alone, and by filtering the data on the Modify Report screen, there is no issue with calculations and subtotals. Each type of data has many different built in options for filtering. For example, this screen shot is from QuickBooks for the Memo Field. It is a text type data field, and is simply a fill in box where if the data matches, the transactional details will be returned for the report.
The next example is from XR. Note that more control over the filter exists. Other data types have similar enhanced functionality such as not equal to or a range when filtering on a numeric field.
