Skip to main content

Power BI Reports and NetSuite Data Validation Process

Overview

After the Power BI data model is implemented, the next step in the overall implementation process is data validation. It is vital that the ZoneReporting Pre-built reports match the corresponding reports in NetSuite. This step of the implementation process should follow a general quality assurance check of the pre-built reports.

 

Income Statement (Profit & Loss Statement)

  1. While logged in to the Customer’s NetSuite instance, hover over Reports > Financial > click Income Statement (sometimes called Profit & Loss).
    zonereporting_how_to_ns_validation_p&l_1.png
  2. The Income Statement is filtered by Posting Period. The filter can include any number of contiguous periods.
    • .Select range of periods at bottom of page.
      • By default, report shows an aggregation of the periods within the filtered range.
      • To display multiple periods individually, drop down on Column > choose Accounting Period
        zonereporting_how_to_ns_validation_p&l_2.png
      • Click Refresh to display report with selections.
  3. Toggle the granularity of the report by using the expand and collapse buttons in the bottom right-hand corner of the report page.
    • Clicking collapse once will drill up to the lowest granularity.
    • Each successive click of the expand button will drill down one level of detail.
      zonereporting_how_to_ns_validation_p&l_3.png
  4. Begin validation at the second level of granularity (account type).
    zonereporting_how_to_ns_validation_p&l_4.png
    • With the pre-built reports open to the Income Statement page, confirm that the posting period slicer matches the report filter in NetSuite.
      zonereporting_how_to_ns_validation_p&l_5.png
    • Drill to the same level of detail and begin comparing numbers.
      zonereporting_how_to_ns_validation_p&l_6.png
      zonereporting_how_to_ns_validation_p&l_7.png

    • Note that by default, the Power BI report is rounded to whole numbers.
    • Confirm that the numbers match across each of the last 6 periods, and spot-check several periods going back to the start of the customer’s dataset.
      zonereporting_how_to_ns_validation_p&l_8.png
      zonereporting_how_to_ns_validation_p&l_9.png

  5. Drill into the next level of granularity and perform a similar check of account-level detail.
    • Note that there may be other levels of summarization in NetSuite that do not appear in Power BI (400 Sales below). These are summary accounts and generally do not have any transactions.
      zonereporting_how_to_ns_validation_p&l_10.png
      zonereporting_how_to_ns_validation_p&l_11.png

  6. Generally, it is best practice to validate reporting periods up to, but not including the current period. For instance, if you are validating data on the 15th of the April, you would not include the April posting period in your validation. Only closed periods should be used for validation. To check whether a period is closed:
    • Navigate to Setup > Accounting > Manage Accounting Periods
      zonereporting_how_to_ns_validation_p&l_12.png

    • A period is closed if all three categories of transactions have a closed lock icon. This indicates that no transactions are posting to this period.

      In the screenshot below, Jan 2022 is the most recent closed period. The Power BI reports for Feb 2022 may have discrepancies caused by transactions that have posted since the last data model refresh.
      zonereporting_how_to_ns_validation_p&l_13.png

    • If discrepancies are noted during the validation process, check this screen before other troubleshooting.
  7. Document the successful validation with screenshots of the reports in Power BI and NetSuite. Save the screenshots in a PowerPoint file.

 

Balance Sheet

  1. Navigate to the Balance Sheet report by hovering over Reports > Financial > Balance Sheet.
    zonereporting_how_to_ns_validation_balance_sheet_1.png
  2. Unlike the income statement, the balance sheet displays only one posting period at a time. Select a recent closed period to begin validation.
  3. Use the collapse and expand buttons to find the level of detail below.
    zonereporting_how_to_ns_validation_balance_sheet_2.png
  4. Open the Balance Sheet (consol) report page in Power BI. You can choose to display multiple posting periods in the matrix, but you must validate one period at a time.
  5. Begin by checking the “Account Type Sub-Group Name” level of granularity in Power BI against NetSuite.
    zonereporting_how_to_ns_validation_balance_sheet_3.png
    zonereporting_how_to_ns_validation_balance_sheet_4.png

  6. If the numbers tie out, drill one level lower in Power BI and NetSuite and compare at the “Account Type” level.
    zonereporting_how_to_ns_validation_balance_sheet_5.png
    zonereporting_how_to_ns_validation_balance_sheet_6.png

  7. If the numbers tie out at this level of granularity, generally the account level of granularity will also tie out. However, if any discrepancies are noted you will need to drill down to the account level to determine which account(s) are off.
  8. The Balance Sheet is a cumulative report, so if discrepancies are noted, the issue may have originated from a prior period. Spot check prior periods to find where the totals fell out of agreement and begin troubleshooting there.
  9. Document the successful validation with screenshots of the reports in Power BI and NetSuite. Save the screenshots in a PowerPoint file.

 

 

Accounts Payable Aging

  1. Navigate to Reports > Vendors/Payables > AP Aging.
    zonereporting_how_to_ns_validation_ap_aging_1.png
  2. Click Aging Options at the bottom of the report page to determine whether the report is using Due Date or Transaction Date to determine age.
    zonereporting_how_to_ns_validation_ap_aging_2.png
  3. In Power BI, open the AP KPIs page and select the AP Aged Trial Balance visual.
  4. Examine the Values well to ensure that the measures in use match the Aging Based On setting in NetSuite.
    zonereporting_how_to_ns_validation_ap_aging_3.png
  5. It may be easier to perform validation using a tabular format. To view the visual as a table, click the More Options icon > Show as a table.
    zonereporting_how_to_ns_validation_ap_aging_4.png
  6. Compare the columns in NetSuite to their corresponding columns in Power BI.
    zonereporting_how_to_ns_validation_ap_aging_5.png
  7. Note that small discrepancies on A/P and A/R aging reports. Unlike the financial statements above, we are looking at transactions in the current period, which means that transactions have likely posted between since the last Power BI refresh. When in doubt, you can run a transaction search following the instructions in Appendix A
  8. Check several vendors (or customers in the case of an A/R report) to ensure consistency
    • Try to select vendors/customers with balances in multiple age bands as these make for better test cases. 

 

Accounts Receivable Aging

  1. In NetSuite, navigate to Reports > Customers/Receivables > A/R Aging.
  2. Use the same procedure outlined in the steps above, but use the AR KPIs page in Power BI instead of AP KPIs.
  3. Document the successful validation with screenshots of the reports in Power BI and NetSuite. Save the screenshots in a PowerPoint file.

 

 

Appendix A

Creating a saved search to investigate report discrepancies

It you find material discrepancies on any of the AR or AP aging reports, or any discrepancies on the income statement or balance sheet, use the following procedure to create a saved search that you can use in your investigation

  1. In NetSuite, navigate to Lists > Search > Saved Searches > New.
    zonereporting_how_to_ns_validation_ar_aging_1.png
  2. Select Transaction as the search type.
    zonereporting_how_to_ns_validation_ar_aging_2.png
  3. Enter a search title and search ID.
    zonereporting_how_to_ns_validation_ar_aging_3.png
  4. Check the Use Expressions box in the Criteria section.
    zonereporting_how_to_ns_validation_ar_aging_4.png
  5. For filters, enter the following:
    • Main Line = Either
    • Date Created > Description = 
      • First dropdown = “Within”
      • For the filter, expand Available Selectors > (Relative)
      • From = 2 Days Ago
      • To = Leave blank
      • Click Set
        zonereporting_how_to_ns_validation_ar_aging_5.png
    • Date Last Modified
      • Use the same date criteria as above
      • Surround both Date fields with parenthesis (per screenshot below)
      • Under And/Or, choose OR
    • Account Type 
      • Set based on the report that you are investigating
        zonereporting_how_to_ns_validation_ar_aging_6.png
  6. Under the Results tab > Columns section, click Remove all.
  7. Use the following results configuration, or feel free to customize to suit your needs.
    • Field = Document Number
    • Field = Internal ID
    • Field = Date
    • Field = Amount
    • Field = Period
  8. Hover over Save > click Save & Run.
    zonereporting_how_to_ns_validation_ar_aging_7.png
  9. Use the search results to determine whether transactions have been created or modified since the last Power BI refresh, or to find transactions from prior periods that were created/modified recently.
Was this article helpful?