Report Generation w/ SAP Business One and MS Excel : Information Technology and Computer Science LCCM Report Generation w/ SAP Business One and MS Excel MIS-CA
1st Semester, 2011 - 2012
Topic Objectives : Information Technology and Computer Science LCCM Topic Objectives At the end of this topic, you should be able to:
Determine the essential management inquiries;
Identify data sources from within and around the company’s computing infrastructure;
Export the data to formats acceptable across computing platforms; and
Process raw data with spreadsheet applications.
Topic Contents : Information Technology and Computer Science LCCM Topic Contents SAP Business One basic report generators
SAP Business One Drag-and-Relate report generator
Tab-separated (*.txt) and comma-separated (*.csv) text file formats
SAP B1 and Microsoft Excel comparison of capabilities
Essential Management Inquiries (in Accounting) : Information Technology and Computer Science LCCM Essential Management Inquiries (in Accounting)
Essential Management Inquiries (in Supply Chain) : Information Technology and Computer Science LCCM Essential Management Inquiries (in Supply Chain)
Good Information : Information Technology and Computer Science LCCM Good Information
Deriving Good Information : Information Technology and Computer Science LCCM Deriving Good Information Systems Thinking: In all things are raw inputs, transforming processes and useful outputs
Define what good information is
Identify your data sources
Identifying Data Sources (1) : Information Technology and Computer Science LCCM Identifying Data Sources (1)
Identifying Data Sources (2) : Information Technology and Computer Science LCCM Identifying Data Sources (2) Data (as transaction records) accumulate as the business runs
An ERP system is likely to have a central database in the back end (good source of internal data)
Others can include data provided by business partners, government, foreign exchange, stock markets, and market research (external data)
Can come in “universal” formats: spreadsheet (MS Excel *.xlsx, tab-separated *.txt, etc.)
Report Generators : Information Technology and Computer Science LCCM Report Generators Most ERP systems have built-in report generators
SAP, for example, has the query generator (right), the Drag-and-Relate and the Open Items List
Allows printing and export to “universally-acceptable” file formats
Processors : Information Technology and Computer Science LCCM Processors Spreadsheets are good processors of raw data in that they help to organize, group, sort, filter, compute and graph
Many people have under-utilized MS Excel, with arrays of functions and charting tools
Spreadsheets : Information Technology and Computer Science LCCM Spreadsheets Microsoft Excel is not the only spreadsheet application, and is not only found in PC’s
There is also OpenOffice Calc for the Linux system, Excel 2011 for the MacOS, and even spreadsheets for hand-held devices and smart phones
They can open their native formats as well as tab- and comma-separated text files
Transformation : Information Technology and Computer Science LCCM Transformation
Why use two applications? : Information Technology and Computer Science LCCM Why use two applications? ERP (SAP B1)
Facilitates transactions (and data gathering)
Centralizes data (keeps them in one place)
Enforces data integrity and security
Does not have more elaborate reporting Spreadsheet (MS Excel)
Weak security and not a good data store (but can be a data store)
Full complement of functions and charts
Support for many file types
Formatting capability
Aggregate Functions : Information Technology and Computer Science LCCM Aggregate Functions Spreadsheets have aggregate functions
These work on ranges of values, using the colon ( : ) to indicate unbroken adjacent cell ranges
Examples are COUNT, SUM, AVERAGE, MAX and MIN
ROUND (for rounding off) is not an aggregate function
These functions summarize data from groups or categories, indicating the how the business is doing
Charts (Graphs) : Information Technology and Computer Science LCCM Charts (Graphs) More often, graphs and charts communicate summary information better than number figures—even from a distance or when dealing with different people
When to use which:
Magnitude comparisons – bar or column
Distributions of the whole or contributions to the whole – pie
Progress over time or trends across time – line
Distributions across values and relationships between variables – scatter graph
References : Information Technology and Computer Science LCCM References SAP Business One (Basic Module: Logistics). Fast Track Solutions, Inc. 2007.
Oz, E. Management Information Systems, 6th Ed. Cengage Learning. 2009.
Post and Anderson. Management Information Systems.