Synergetic Data Systems Inc.

Scripting Off

Home
Products
  → UnForm   → sdOffice   → sdStor   → General   → MailCall   → dServe
Services
Support
  → UnForm   → sdOffice   → sdStor   → General   → MailCall   → dServe
Downloads
  → UnForm   → sdOffice   → sdStor   → General   → MailCall   → dServe
Request Info
Contact Us
Partners
  → Partner Links   → Partner Links

Newspages 2006 3rd Qtr Columbia

SDSI Columbia Forest Products Article

N E W S . P A G E S ◊ 2006 . Quarter . 3

SPOTLIGHT

Using sdOffice to Bridge Technologies:
Seamless Delivery of Data to the Desktop

INTRODUCTION

This issue of NewsPages spotlights a large corporate enterprise user of sdOffice, Columbia Forest Products (Columbia), headquartered in Portland, Oregon, the country's largest manufacturer of hardwood plywood and hardwood veneer products. You can visit their website at columbiaforestproducts.com .

Over a series of interviews with Columbia IT staff, three main themes surfaced which summarize Columbia's experience with sdOffice:

Extending the useful life of Columbia's investment in customized enterprise software,
Delivering control over data into the hands of users in the most efficient way possible, and
Accomplishing both of the above with an investment that paid for itself in 6 weeks.

Columbia accomplished this by automating report delivery via sdOffice: key enterprise accounting software reports are seamlessly output to Excel and delivered via Outlook email to the user. The investment payoff comes from providing this functionality by replacing a report mining tool that was at least five times slower in producing the same result, and without the same seamless integration.

sdOffice is a platform-independent, language-independent, client-server-based software tool, developed and marketed by SDSI. It gives programmers and developers the ability to integrate non-Windows applications with the Microsoft Office suite of programs, as well as other Windows-based programs. Using sdOffice to interface with Windows-based programs can enable productivity-enhancing and efficiency-boosting results which help streamline office management and control. For full product details visit the sdOffice product page.

The body of the ensuing article provides background and details of how Columbia implemented this system, and concludes with a summary discussion of the benefits Columbia realized by using SDSI's technology-bridging software tools for accomplishing these kinds of objectives.

Note that Columbia's experience and methodology implementing sdOffice reflect a focus on their particular needs. sdOffice can be implemented in a variety different ways, some very simple, some more involved.

Background

The Portland, Oregon data center operations for Columbia Forest Products serves a corporation with almost 4,000 employees across the U.S. and Canada. Brian Sherman, a senior systems analyst in Columbia’s IT department, heads a team of 4 programmer-analysts who specialize in providing support for their enterprise distribution accounting software, a customized version of Addon Software version 6.0, which runs on BBx Pro5 business basic language technology from Basis International. The Addon Software system is used by three of the corporation’s five major divisions, including the corporate headquarters in Portland.

About 60% of the Addon support team’s time is attributable to report and data delivery to users. The team’s main contact point with the population of users is with and through the divisional controllers and corporate controller.

Data-Users' Needs

Controllers are the analytical arm of an organization, and as a general rule, analysis depends on data. Their jobs revolve around getting good data and doing something with it to support the objectives of the enterprise.

“Our controllers use data to solve business problems”, is the way Brian Sherman puts it. "These guys are busy solving business problems and looking for opportunities to improve performance. Looking for cost savings, operating efficiencies, and doing trend analyses are all part of that process.”

And according to Brian's experience at Columbia, "Every controller looks at data differently". That's why having the ability to work with "raw" accounting system report data in a spreadsheet makes good sense. Especially when getting the data there is seamless.

Not only controllers and their accounting staff's need access to data. At just about every level of clerical, middle and upper management there exists a need to work with data captured in the databases of the enterprise accounting system.

In-house programmers and IT managers know the frustration of developing a “must-have” report program to meet a very specific desire to see data presented a particular way, only to find out later that it was used only once or twice and forgotten about, because the user’s needs changed. The cost to the enterprise of spending valuable IT resources on essentially wasted efforts is an expense every enterprise tries to avoid.

Columbia’s IT and managerial staff have addressed this situation by developing software that BOTH delivers the report data to the user's desktop AND gives the user control over the how the data is viewed and formatted.

Columbia accomplished this by automating report delivery via sdOffice: key enterprise accounting software reports are seamlessly output to formatted Microsoft Excel spreadsheets and delivered via Microsoft Outlook Email to the user.

Excel: Making Data "Come Alive" for Business Decision-Making

The MS Excel spreadsheet is THE de-facto standard business analysis tool, a preferred tool for "number-crunching" and "massaging data" in just about any field. “Slice and Dice” is another colloquialism used to describe the process of taking data streams from accounting applications and subjecting them to spreadsheet-based manipulation.

Cosmetically formatting, sorting, subtotaling, drilling down, summarizing, filtering, averaging, and pivoting are all examples of common spreadsheet operations employed to force data to “come alive” for business decision making.

The Report Mining Alternative

Prior to the adoption of sdOffice for the bridge between the Addon system and MS Excel, Columbia used a 3^rd party report mining tool, also referred to as a "report scraping" tool. Though this tool has its own proprietary set of data analysis features, Columbia was mainly using it as a bridge between Addon and Excel.

The problem was that the steps involved in going from Addon report output to Excel spreadsheet was a non-automated, 8-step process.

Output Addon report to ASCII file
Open report scraping tool
Find and load the ASCII file in the report scraping tool.
Find and load the data-parsing model in the report scraping tool.
Confirm that the model matches the data
Process the job to create the spreadsheet
Open the spreadsheet in Excel
Close report scraping tool

By using sdOffice the 8-step process was able to be reduced to a 2-step process, saving time, hassles, and possible user error.

Now the process is:

Output Addon report choosing the Excel option
Open the spreadsheet attachment from Outlook Email inbox

Any Report

At Columbia, any report program in the Addon system can be sdOffice-enabled in a matter of a couple of hours. After that, it is then permanently available in an Excel-output format with a single keystroke from Addon's report output option prompt.

Columbia initially developed a base set of routines, code-named “sdoBase”, to plug into any report program, providing streamlined access to the sdOffice client interface via gosub routines from the mainline report code. In this way the custom code is as non-invasive as possible.

When the user is prompted to confirm the running of a selected report, the embedded sdoBase routine presents the options for “R=Standard Report”, “E=Excel by Email”, or “B=Both”. Selecting “R” bypasses any sdOffice handling, and either “E” or “B” enables the sdoBase routines, sending formatted report output to an Excel spreadsheet and Emailing the resulting spreadsheet to the user via Outlook.

Integrated E-Mail Delivery of Spreadsheets Simplifies Admin

The delivery of the report via automatic and immediate Emailing of the resulting spreadsheet allows Columbia to house the sdOffice client software on a Windows server at the data center dedicated specifically to sdOffice and MS Office applications.

Resulting spreadsheets then get “served” to any user on the system, without needing the sdOffice client software to reside on individual PC’s in the wide-area network.

From an IT administration standpoint this greatly simplifies the infrastructure, maintenance, training and support of users.

Rewind to 2003:
124 Lines of Re-Usable Code, a reasonable server software license fee,
and a 30 X PAYBACK IN 3 YEARS

“Columbia’s investment in the purchase of the sdOffice server license and in developing the sdoBase code back in 2003 has to have been paid back many times over by now”, according to Brian Sherman.

We decided to make some ball-park estimates, based on some known factors:

An average of 100 sdOffice-generated spreadsheets per day.
An average time savings of 10 minutes per spreadsheet over using a report mining tool.
Industry standard average rate per hour including fringe benefits per spreadsheet user.
Cost of sdOffice server license.
8 programmer man-hours of sdoBase development.
2-6 programmer man-hours each to excel-enable 50 different reports

Total Investment	Less than $30,000
Annual Savings	Around $250,000
Initial Payback Period	SIX WEEKS
3-Year Payback	30 X
ROI	OFF THE CHARTS

NOTE that the cost of the sdOffice software server license accounts for less than 5% of the total estimated investment.

THE CODE: Columbia's sdoBase application interface to sdOffice

"sdoBase" is the code-name for Columbia's set of routines which standardize Columbia application programs’ communication with the SDSI sdOffice client.

By appending this package of Columbia pre-developed in-house subroutines to the bottom of an Addon report-generating program, the program becomes sdOffice-enabled. Then, in the sections of Columbia application code which initialize the report, output lines to the printer, and finish the report, a branch to the appropriate Columbia sdoBase "parent" routine is inserted.

NOTE that it is NOT necessary to develop an in-house set of routines as another layer between the application and the sdOffice command interface. This simply made sense for Columbia since they were planning on embedding the functionality into a large number of programs.

Diagram A below shows the components included in the Columbia in-house-developed sdoBase package of routines.

There are 6 parent routines, 3 interface routines, and 15 utility routines. These routines have been programmed with a combined, very compact and efficient, 124 lines of “functional” code (remark, line label and return statements excluded).

The 6 parent routines get called via gosub from within the mainline Addon code. The 3 interface routines are the bridge to SDSI’s called application client programs for BBx. The 15 utility routines perform a variety of automation and delivery functions.

Note that when looking at the diagram, the three level 2 interface routines shown below,

sdofc_e.bb sdofc_o.bb sdofc_w.bb

are the SDSI-written sdOffice client command-processing programs for the BBx Pro5 language.

NOTE that sdOffice comes with client interfaces written for various different technologies. In the case of the BBx Pro5 language which Columbia uses, the client interface to sdOffice is via program modules which are CALL-able routines. The client interface programs are what perform the communications across a network using TCP/IP sockets, DDE, or Perl-based pipes, depending on the host operating system, and translate the sdOffice command structure into the native Windows COM language.

DIAGRAM A - Columbia sdoBase Program Hierarchy
	Level 1 sub or call invoked	Level 2 sub or call invoked	Level 3 sub or call invoked	# of Level 1 Code Lines	Remarks

	Parent Routines
1	sdo_init	sdo_newtab		7	Initialize excel sheet
2	sdo_newtab	sdo_e		9	Create new excel tab
3	sdo_write	sdo_output		4	Write one excel line
4	sdo_format	sdo_fixcols	sdo_e	6	Format one excel line
5	sdo_endtab	sdo_e		2	Tab format autofit, fitwidth
6	sdo_finish	sdo_e	call sdo.em	16	clean up and EMAIL sheet

	Interface Routines
7	sdo_e	sdofc_e.bb		6	MS Excel interface
8	sdo_o	sdocf_o.bb		4	MS Outlook interface
9	sdo_w	sdofc_w.bb		4	MS Word interface

	Utility Routines
10	sdo_output	sdo_e		3	Buffered line output
11	sdo_write_w_format	sdo_output	sdo_format	5	Write with format
12	sdo_setrow	sdo_e		3	Set specific row
13	sdo_insert_row	sdo_e		2	Insert a row
14	sdo_active	sdo_e		3	Activate specific sheet
15	sdo_fixsheets	sdo_e	sdo_active sdo_endtab	8	Clear unwanted sheets
16	sdo_fixcols			8	Format column range
17	sdo_clr			1	Clear sdofc responses
18	sdo_mt	call sdo.mt		4	Send Email notice
19	sdo_debuging			7
20	sdo_errtrap	call sdo.mt		8
21	sdo_email_err			4
22	sdo_load			9	Add memory resident
23	sdo_unload			4	Drop memory resident
24	prompt_routine			7	Prompt user

A Sample of Columbia's More "Interesting" sdOffice-enabled Applications

There are probably over 100 different reports in the Addon system which have been sdOffice-enabled. Most of these are the standard accounting system reports for inventory valuation, stock status, order status, order history, sales analysis, purchase order status and history, accounts receivable aging, general ledger history, trial balance, financial statements, etc. The day-to-day running of Columbia’s business depends on reliable and quick access to the information from the Addon system, and for the Columbia divisions that use the Addon system, sdOffice is a critical integrated part of the report delivery system.

For this article, we asked Brian and the Addon support team for a couple specific examples of some interesting and useful sdOffice-enabled applications being used, and we received back detailed information for two applications:

Budget Sales Analysis - an unattended, automated weekly sales report that Emails 21 different salespeople their sales results for the week by customer and budget category, and then Emails a consolidated spreadsheet to senior management.
Wells Fargo CCER Interface - an interface to the expense reporting file for the corporate purchase card which automates the general ledger journal entry posting for about 180 of the 400 corporate card users.

Each is described below.

Budget Sales Analysis

Launched by an automated process every Saturday morning. Email recipients for the detailed spreadsheet are each one of the 21 salespeople, just seeing their own results. Email recipients for the consolidated spreadsheet are selected senior management personnel and other personnel. A data file contains the recipient list for both versions of the report. The salesperson spreadsheet has 3 tabs: year-to-date, period-to-date, and special products. Each tab has left-hand columns for salesperson name and customer name, then 3 columns for customer totals: To-Date Sales, To-Date Budget and Percent of Budget. To the right of those columns are 9 sets of the same 3 To-Date columns listed previously, one for each Budget Category. The special products tab has a slightly different format. As new budget categories are added the spreadsheets dynamically adjust themselves for the new columnar data. The excel subtotal command is used to generate subtotals, which is especially useful on the executive consolidation report to collapse and expand the rows to drill down in the level of detail being viewed.

Wells Fargo CCER Interface

CCER stands for “Commercial Card Expense Reporting”, and it is Wells Fargo bank’s system for providing VISA card clients with more detailed expense analysis on corporate charge-card purchases. Columbia has employees all over the world using this service. The system allows the card client to map product and service expense categories to internal general ledger account numbers. Using this system assists the accounting department in speeding up entry and reducing errors when creating GL transactions. About 400 employees at Columbia use the corporate card, about 180 of them currently at divisions which use Addon, generating about 1800 transactions monthly. The accounting department uses the monthly spreadsheet received from Wells Fargo for various management reports and purchase trend analyses, and then a copy of the file is FTP’d to the network sdOffice server. A front-end Addon program was developed to prompt for various header and date values needed for the GL journal entry files, as well as the GL account offset for the postings. Then the program processes an sdOffice-based routine that reads through all the rows in the spreadsheet, validates GL account numbers, and posts the expense postings directly into the Addon GL journal entry file, along with the balancing entry at the end of the run. A standard Addon journal entry register can then be reviewed and audited, and then updated to Columbia’s general ledger.

THE BOTTOM LINE

IT decisions about the proper way to deliver data from back-end business software databases to the users who need to work with it can benefit from a knowledge of different software tools that exist which can bridge technologies. Columbia's use of SDSI's sdOffice technology, which provides a bridge between back-end accounting databases and Microsoft's Office automation suite of programs, has extended the useful economic life of a key Columbia asset. Their Addon Software system, originally installed in 1988 and customized extensively over the years for Columbia's unique and specific business requirements, is now in its 18th year of use, and according to Brian Sherman, not expected to be replaced in the foreseeable future.

It may be true that decisions to replace aging software assets frequently come down to the question of ease of access to valuable data. At Columbia, it is clear that Brian Sherman and the Addon support staff have come up with an elegant, seamless, non-intrusive way to deliver back-end business data to users who can immediately go to work on it inside the familiar interface of the number-one business analysis tool.

This has been done using sdOffice, an intelligent SDSI tool with the inherent functionality and price-feature economics which allowed them to make a sound investment with a remarkable payback period when compared with using an industry-leading report mining software tool.

We would imagine that the Columbia controllers that Brian Sherman and his staff work so closely with are happy for that.

The Addon software support team at Columbia Forest Products
data center in Portland, Oregon.

Left-to-Right :

Brian Sherman, Priscilla Coleman, Greg Browne, Carla Johnson, Mark Wardell

THANK YOU to Columbia, Brian and team for spending the time with us to share their experience with our product.

Look for this spotlight feature in every issue of the NewsPages quarterly E-Letter.

For full product details visit the sdOffice product page.

UnForm and CirrusPrint are registered trademarks of SDSI. General and MailCall are trademarks of SDSI. Other product names used herein may be trademarks or registered trademarks of their respective owners. SDSI uses Google analytics and conversion tracking on some site pages. Site design by ZiMDesign.