Keeping it Simple with Solomon IV
Vol. 2003 Issue 2
Publisher:
Jeff Cozens, Systematic Solutions
Inc.
Email: jcozens@systematicsol.com
Web Site: http://www.systematicsol.com
(C) Systematic Solutions Inc. 2000-2003
=====================================================
2. USING
EXCEL AND PIVOT TABLES WITH SOLOMON IV
=====================================================
With the
introduction of the MS SQL database you can use Excel 2000 or higher to read
the Solomon database. These tips only work for Solomon IV version 4.x and higher.
I am going
to illustrate the benefits of Excel pivot tables and perform an analysis with
your Solomon data. The business problem
I am trying to resolve is watching multiple revenue or expense accounts across
multiple departments (a department is defined as a unique sub account) across
the entire calendar year. I want to be
able to quickly change which accounts I am analyzing per this pivot table. This is a great tool for tracking critical
business expenses or key sales accounts.
Here is an
example of the report we will be developing:
In the
excel spread sheet above, when you see the black down arrows, you can click on
these arrow heads, and a combo box will give you all of the possible data
elements you can see. For instance, if I
clicked on the arrowhead next to Fisc Yr, I can show
2002 and 2003 data. Similarly,
this analysis tool if I clicked on the black arrowhead next to acct, I can
select any account number for analysis.
The data automatically refreshes as I make these selection changes.
Once in
the excel format, you can graph any of the results. Showing the interface with graphs is another
newsletter topic.
Before we
start, there are several administrative tasks to complete. First, after entering your Excel, click on
the menu choice called: Data, and confirm you have a sub menu called Pivot
Table and Pivot table chart wizard. The
Pivot table wizard was an option at the time Excel was set up on your
machine. Contact your in-house IT
support staff or locate your Excel cd and open a
support call with our company, to install this feature.
The next
administrative issue is user permission and password to access your Solomon
data in a read only mode. This issue is
more sensitive, and needs to be discussed with your Senior Financial person,
who needs to authorize your accessing of the data.
Assuming
these two issues are resolved, the next step is to launch Excel, then click
Data, then click on Pivot Table and Pivot table chart wizard. This will take to you this screen:
Select the
button called: External data source:
Click the button at the bottom called Next.
In step 2,
we are defining where to get the Solomon data for your analysis.
Click the
button called GET DATA; near the top of the screen shown above.
The screen
above appears, and using the vertical scroll bar, find and click with the left
mouse key, the ODBC connection to you Solomon Database. In the above example I am selecting the
demopremier60 database. You would select
you OWN company database.
Click OK
button after selecting, and this next screen appears:
On the
left section of the screen are data tables. We are going to be getting data
elements or fields from two tables:
Account and AcctHist. To select a field, click on the plus sign
next to the table and you see a list of fields.
Select these fields and one at a time, click on
the > field in the center of the screen to transfer the selected fields to
the right hand column called: Columns in your query:
From Table
Account: select Acct, descr
From Table
AcctHist: select Acct, Sub, Fiscyr,
Ledgerid, Ptdbal00, ptdbal01….ptdbal12
This is a
tricky part of the setup wizard. After
selecting all required fields, you will be clicking on the next button as
shown:
This next
button takes you to this screen:
Click the
next button on the new screen shown above.
This next
button takes you to this screen:
Click the
next button on the new screen shown above.
This next
button takes you to this screen:
This is
the last screen in the wizard. Here I
want you to save the query by clicking on the Save Query button (top right ).
In the file
name box, assign an appropriate name such as Gl_anal. This query can be used to shortcut recreating
this pivot table. (see
choose the data source screen above , and click on the tab labeled:
queries). Contining
on with this example, click on save button and return to the final wizard page.
Click
Finish button.
Now you
are back of step 2 of 3 for the wizard:
Click the
next button
Next we
finally get to click the Finish button and transfer pivot table data to Excel,
posting to a specific cell or a new worksheet.
I usually just except all the defaults and click the finish button.
Now
everything is in place to create your pivot table. The trick in this step is to select from the
pivot table field list a field and drag and drop this field to the appropriate
section of the pivot table, shown on the left side of the excel worksheet.
Take you time and drop fields
correctly as shown in the instructions:
The row
fields here section gets: 1st:
Acct 2nd: FiscYr
The Drop column
here section gets: 1st: Sub 2nd:
Ledgerid
Drop data
items here gets: Ptdbal00, ptdbal01,ptdbal02,……ptdbal12
Your pivot
table should start looking like:
Now I want
you to save this pivot table in excel. File , save as,
and assign a name and location as appropriate for your computer.
In the
spreadsheet we are going to clean up some labels. Go to column C and click on cell which says
sum of ptdbal00 (for instance C6).
Retype either Month 1 or the actual name of the month (i.e.
January). Repeat going down this column
for other month labels. Please note, you will typically not be using sum of ptdbal12 which
corresponds to the 13th period of the fiscal year.
Save your
changes again in the spread sheet.
Now the
fun begins.. There are five black arrow heads on your
pivot table. Click on black arrow head
and select the values you want to see. For instance, if you want to only see
the accounts which begin with 3xxxx, then select only those accounts from the
pull down combo list. After clicking ok
to you selections, the table dynamically changes.
Bravo, you
succeeded in your first Solomon Pivot table.
Image the future possibilities for analysis and graphs you can
create. Have fun and let me hear about
your successes.