OGSYS Support for version 180

Introduction:

This page contains the new 2^nd Generation SQL Report Library for OGWin's SQL Export. To access the 1^st Generation Report Library, click the link above.

The 2^nd Generation of SQL Reports for OGWin takes advantage of the add-in capabilities of Microsoft Excel. This technology allows for new features and benefits such as:

§ Database Connectivity settings are handled interactively. No more editing VBA Code to configure database settings.

§ Reusable entity selection dialogs reduce coding time and improve user interfaces. Items such as Company Codes, Well Codes, etc. can be chosen in searchable interfaces.

§ Expanding collection of commonly used functions. This further reduces coding times for OGSys staff as well as end user report designers.

§ Site Options Management - Individual Site Options are now maintained by the add-in. This allows OGSys to add more commonly used reports to the library without maintaining multiple versions.

Generation 2 Requirments

Second Generation SQL Reports for OGWin are exclusively written for the 1.8 (SQL) version of the database. These reports are not compatible with the older 1.7 (QDB) version. If you need to upgrade, please contact support@ogsys.com.

Setup Instructions:

The 2^nd generation reports depend on a Excel Add-In named OGSQLai.xla. This file must be in the same folder as the individual reports for the reports to work properly. OGSQLai.xla may be downloaded below. Once downloaded, OGSQLai.xla is configured using macros available in any 2^nd Generation SQL Report.

SQL Settings Configuration:

Configuring the SQL Settings tells the OGSQLai.xla add-in where to find, and how to authenticate to your SQL database. Once this configuration is made to the add-in, you need not repeat this process unless your environment changes.

1. Open any 2^nd Generation SQL Report (Enable Macros if prompted). LOE.xls will be used for this example.

2. Click Tools->Macro->Macro

3. Run the Macro "ChangeSQLSettings"

4. Enter your SQL Server information into the form:

5. Click "Test Connection"

6. Once the Connection is successfully tested, click OK to save the contents to the add-in. This will change the settings for all Generation 2 reports looking at this copy of OGSQLai.xla.

Site Options Configuration:

Configuring the Site Options Settings tells the OGSQLai.xla add-in what database specific options that your installation of OGWin has. This allows Generation 2 reports to properly query the database. NOTES: This does not affect the options maintained in OGWin itself. If your options differ between OGWin and the add-in, Generation 2 reports may not operate properly.

1. Open any site specific Generation SQL Report (Enable Macros if prompted). LOE.xls will be used for this example.

2. Click Tools->Macro Macro

3. Run the Macro "ChangeSiteOptions"

4. Select and De-select the options listed to match your OGWin options.

5. Click Save to save the contents to the add-in. This will change the settings for all Generation 2 reports looking at this copy of OGSQLai.xla.

The Files:

OGSQLai.xla - This file must be in the same folder (directory) as any Generation 2 SQL Report. Please see setup instructions above. Current Version: 1.0 07-Sep-05. If you are experiencing problems with 2^nd Generation Reports, please ensure you have the latest copy of this file.

Attention Internet Explorer Users: There appears to be a bug in IE where this file will be saved with an *.XLS extension instead of the *.XLA extension. If this occurs, rename the file to OGSQLai.XLA.

AFE2.XLS - A Line Format (FFML) aware AFE Report with Drilldown capability. This allows you to define the vertical format of the report using the power of OGWin's Report Writer, while capitalizing on the speed and drilldown capability of SQL. [AFE]¹ [MC]²

JADE18.XLS - A new and improved version of the classic SQL JADE report. New features include graphical entity and account selections. Please note that the column descriptions in this report are an artifact of the original client driven specification. The report is based solely on the General Ledger module in OGWin. Specifically: "JIB Mo/Yr" refers to the Journal Entry's (JE) Accrual Date. "Invoice Date" refers to the JE's Journal Date. "Service Date" refers to the JE's Cash Date. "Inv #" refers to the JE's Reference field. [MC]²

LOE.XLS - A Line Format (FFML) aware Lease Operation Expense Report with Drilldown capability. This allows you to define the vertical format of the report using the power of OGWin's Report Writer, while capitalizing on the speed and drill down capability of SQL. [MC]²

MCTB.XLS - A multi-currency Trial Balance. A standard Trial Balance with the functional currency values and historical their equivalents in separate columns. [MC]¹

TB18.XLS - A multi-company Trial Balance with Drill Down capability. [MC]²