Better Business Through Data Analysis & Monitoring My Account

Exporting Data from 3 Commonly Used Applications

Tech Tip Explains How to Export Data from SAP, PeopleSoft and QuickBooks

Before any work can be done in IDEA, you’ll first need to acquire the data to be analyzed.

Here are the steps to export data from three commonly used applications; SAP, Peoplesoft and QuickBooks. Note that your exact steps may vary slightly depending on the version of software you are using.

Exporting Data from SAP

The easiest method to export data from SAP is to use SmartExporter. This tool is used to connect IDEA directly to your SAP system. It then exports the required table(s) and automatically creates a new IDEA database for you. If you don’t already have SmartExporter, please contact your Audimation Account Manager to get more information.

If you don’t want to use SmartExporter, you can still export your data to an Excel file which can then be imported into IDEA.

  1. In full-screen mode, choose List → Export → Spreadsheet. If you have a choice of more than one format, the Select Spreadsheet dialog box opens.
  2. In the Select Spreadsheet dialog box, select .XLSX or .XLS.
  3. Confirm the settings.
  4. In the dialog box that appears, specify the path and name of the file and save the file. For some formats, you also need to specify other settings for the file that is saved.
  5. Make the required settings, where applicable.

This will open the data in a new Excel spreadsheet which can then be imported into IDEA.

Exporting Data from PeopleSoft

PeopleSoft supports integrations with several different reporting tools, such as Crystal, SQR, PS/nVision, Application Engine, and BI Publisher. Reports are typically created by application developers using the corresponding window-client based designer. In most cases, PeopleSoft Query is the primary data source for data from a PeopleSoft database. Users can then use reporting tools to apply additional formatting (HTML, PDF, XLS, XML, and so on) to the data to create a report.

However, if you have PeopleSoft Pure Internet Architecture access from a browser and do not have access to any PeopleSoft client tools, then you have limited options for customizing PeopleSoft reports.

To provide the flexibility to accomplish reporting tasks from Pure Internet Architecture, PeopleTools has provided a way of extracting, transforming, and distributing data from there without the need to access any client tools.

Note that these instructions are for existing queries. You may need to get a system administrator to assist you if you need to create a new query from scratch.

  1. Navigate to the desired page and select Query Viewer.
  2. Enter the query name and click Search.
  3. Click the Run link for the query.
  4. The query prompt page appears. Enter your Business Unit, Ledger, Budget Reference, DeptID, Fund, Program and Project values. Click the View Results button.
  5. The query results display beneath the entered criteria. To send the query results to an Excel spreadsheet, click on the Excel Spreadsheet link located above the query results.
  6. The File Download window appears. Click on the Save button. Choose the Open button to view results in Excel without saving the file.
  7. Select the save location (drive & folder), enter a File name and Save as type of Microsoft Excel Worksheet. Click Save.
  8. Once the Download is complete, click on the Open button.
  9. Excel is launched and the results have been saved to an Excel file.

Exporting Data from QuickBooks

Exporting from QuickBooks is a straightforward task.

  1. Click "Reports" at the top of the QuickBooks window and select "Report Center" from the resulting menu. The Report Center window opens.
  2. Select the report containing the data you want to export to Excel and click "Display Report" to view it.
  3. Click the "Excel" drop-down arrow at the top of the report. The Export Report window opens.
  4. Click the radio button by "A new Excel workbook" if you want to create a new Excel workbook, or click the radio button by "An existing Excel workbook" if you want to update an existing workbook. If you choose the former, click "Browse" and select the workbook.
  5. Click "Export" to extract your data from QuickBooks to Excel.
  6. The data should then open in Excel. Make sure you go to File → Save as and save the file to your desired location before closing the worksheet.

To sum up, many ERP systems allow some sort of “export to Excel” function, “file save as” function or “print to PDF” function that will enable you to output data for your IDEA projects. You could also create a direct link to your data using IDEA’s ODBC import capabilities, depending on your IT environment.

If you have questions about this or other IDEA functions, please give our help desk a call at:
888-641-2800 Option 4
Or email us at helpdesk@audimation.com

Print

Number of views (2219)/Comments (0)

Brad Newman

Brad Newman

Other posts by Brad Newman
Contact author

Please login or register to post comments.

Name:
Email:
Subject:
Message:
x
Global Chemical Company Automates Risks Identification with CaseWare IDEA
14 September 2018

Global Chemical Company Automates Risks Identification with CaseWare IDEA

Global corporation used IDEA to standardize & develop an automated process to comply with anti-corruption policy.

Read More

Telecommunications Provider Turns Two Days of Work Into 20 Minutes with CaseWare IDEA®
14 September 2018

Telecommunications Provider Turns Two Days of Work Into 20 Minutes with CaseWare IDEA®

Auditors Analyzing Hundreds of Millions of Records to Track Buying and Usage Patterns

One of the world’s largest telecom companies' internal audit team leverages tech to analyze trends, tendencies & patterns within customer & financial data.

Read More

Exploring IDEA's Built-In Power of Python
20 August 2018

Exploring IDEA's Built-In Power of Python

IDEA Tech Tip

With the release of version 10.3, IDEA created a buzz with its implementation of the Python scripting language, thereby taking a huge step towards having a more robust coding environment.

Read More

Sharing IDEA Data Using IDEA's ODBC Driver
18 July 2018

Sharing IDEA Data Using IDEA's ODBC Driver

IDEA Tech Tip

Read More

Oncor Achieves Operational Efficiencies with CaseWare IDEA
25 June 2018

Oncor Achieves Operational Efficiencies with CaseWare IDEA

Oncor's internal audit team needed to acquire years of data from different environments and formats to help improve processes and efficiency levels.

Read More

Analyzing the Past to Predict the Future with Trend Analysis
18 June 2018

Analyzing the Past to Predict the Future with Trend Analysis

In business, predicting the future is never an easy task. But IDEA can help as long as you have the right data, and Trend Analysis can help.

Read More

2018 IDEA® User of Excellence Award Winner Selected
15 June 2018

2018 IDEA® User of Excellence Award Winner Selected

Jennifer Girard, CIDA with American Red Cross Recognized for Exceptional Use of Data Analytics

Jennifer Girard, Sr Auditor with the American Red Cross is the recipient of the 2018 US IDEA UoE Award. She utilized IDEA to assist Finance Department during a natural disaster.

Read More

The Rules of Nature and Audit
6 June 2018

The Rules of Nature and Audit

What’s missing is often as important as what’s present. The rules of nature apply very well to audit if you consider, “Everyone knows it, but do you test for it?”

Read More

Using IDEA to Import QuickBooks General Ledger Data
4 June 2018

Using IDEA to Import QuickBooks General Ledger Data

The General Ledger is typically the starting point to an expanse of detailed information, see a step-by-step guide on how to get a readable version in idea.

Read More

New Features in IDEA 10.3 Part II
10 May 2018

New Features in IDEA 10.3 Part II

IDEA Tech Tip

In a recent article, we introduced the improvements to Stratified Random Samples and Python integration added in IDEA 10.3. But that’s just a small part...

Read More

Cargo Company Swaps Programming-Intensive Data Analytics Software for CaseWare IDEA
10 May 2018

Cargo Company Swaps Programming-Intensive Data Analytics Software for CaseWare IDEA

Auditors Achieve More Success in First Year with IDEA Than 5+ Years with Other Tool

With the entire audit team up and running using data analytics, they are finding new ways to integrate IDEA into other areas of the business and automate repeatable processes.

Read More

Maximizing Your Environment - Global Variables
8 May 2018

Maximizing Your Environment - Global Variables

Consider scope to be a building in which people work. The tools required to accomplish their tasks will of course be housed in that building, but in scripting it’s important to determine which tools need to be shared among the buildings.

Read More

Tips on Acquiring Data
8 May 2018

Tips on Acquiring Data

An IDEA User Shares Frequent Practices

When working with rows and columns of data to spot suspicious behaviors, fraud and errors can look very similar to one another. While Yeriazarian often uses Excel as an interim format for data, he also understands there are several avenues to navigate before getting the right data.

Read More

Identifying Churn in AR
19 April 2018

Identifying Churn in AR

IDEA Tech Tip

Customer churn (customer attrition, turnover, or defection) is when a customer decides to stop engagement with your company. IDEA can help easily identify “churn” in accounts receivable by following these steps...

Read More

RSS