OCQ – Quick Tour
This exercise will give you an introduction to Office Connector and its functions. In this example, you will:
- List General Ledger accounts
- Lookup related budget data
- Calculate the beginning balance
- Insert totals
NOTE - This tutorial example is designed to work with the Timberline Construction Sample Data.
Step 1 - Insert the Query
The driving force behind this example will be the list of GL accounts. We are going to use a query so the spreadsheet will automatically adapt as your account list changes.
- Launch Excel.
- Select cell B2.
- Click the Query Wizard toolbar button.
- Select the Timberline Construction sample data folder and click OK.
- If you have implemented security in Sage 300 Construction and Real Estate, then you will be prompted to log in. Enter your operator ID and password and click OK.
- The Select Table screen is displayed. Type the G key to select the GL Account table and click Next.
- The Select Fields screen is displayed. Select Account, Account Title, Current Balance and Current Period Activity.
- Click Finish to insert the query.
Step 2 - Lookup the Budget
Since budget amounts are not found in the GL Account table, we'll use the wizard to build an Office Connector function that will lookup the budget amount from the GL Budget table.
- Select cell F2.
- Type Budget for the heading and hit Enter.
- Select cell F3.
- Click the Lookup Wizard toolbar button.
- The Select Table screen is displayed. Select the GL Budget table and click Next.
- The Key Values screen is displayed.
- Budget Year - Uncheck Cell Reference and type 2011.
- Budget Type - Uncheck Cell Reference and type Approved.
- Account - Leave Cell Reference checked and type $B3.
- Click Next.
- The Select Fields screen is displayed. Select Annual Amount and click Finish to insert the TSLookup function.
- Copy the formula down. You can quickly accomplish this using the fill handle.
Step 3 - Calculate the Beginning Balance
Since we're including the current balance and current period activity, we might also want to see the beginning balance. We can do that by just using an ordinary Excel formula.
- Right-click the column header for column D and select Insert from the popup menu.
- Select cell D2.
- Type Beginning Balance for the heading and hit Enter.
- Select cell D3.
- Type =E3-F3
- Copy the formula down.
- Double-click the column divider between columns D and E to autosize the new column.
Step 4 - Insert Totals
To complete the solution, we will add grand totals to the bottom of the data.
- Type Ctrl+End to jump to the bottom row of the data.
- Select the cell in column D in the row below the last line of the data and type Totals.
- In the same row, select the cells in columns E, F and G.
- Click the Excel AutoSum toolbar button.
- Format columns E, F and G so that Excel displays the numbers correctly.
- Select the new Totals row and apply bold formatting.