In Excel, you can grab data from an external database.  In Excel 2002, it is through Data/Import External Data.  In Excel 2007, it is through Data/From Other Sources.  I can connect to the Financial Edge database, but I am not familiar enough with the table structure to find the data I am looking for.

I want to build a spreadsheet that grabs the following data:

Account #    Description       Jan    Feb    Mar    Apr   etc    Total

410-10-17    Wages              $50   $70   $65   $45 

456-10-17    Office Supplies  $10   $20   $25   $30

etc

Then I can refresh the data whenever I want and get it automatically in Excel.  I then use that data for various analyses.  I can also filter by account number for manual reconciliations...it automatically grabs the GL balance for me, so I don't have to look it up each time.

Does anyone know the tables and fields where I can find this data?

Thanks for your help,

Mark

I am close, but not quite there yet.

The table "GLViewForGetAcctBegBalByPeriod" contains some useful information.  I grab the Account Number, Debit, Credit, GL7FiscalPeriodsID, and other data.  I then add a column to add the Debit and subtract the Credit to get the net activity for the month.  I also use the table GL7FiscalPeriods to convert the GL7FiscalPeriodsID to a date.

Using that data, I build a Pivot Table with Account numbers down the left side and month-end dates accross the top.  The net activity is in the body of the pivot table.  This gives me the chart described above.  At the end of each month, I can then look at the history of all accounts and question anything that looks too high or too low.

 Now for the problem - This only gives me data for years that have been closed.  That's useless.  I need current information.

 Does anyone know what table holds the current data?

Thanks

Mark

SUCCESS!

This worked for me. I’ve tried to write down step-by-step instructions. Hopefully I got it all. But until someone tries to follow these instructions and gives me feedback, I really don’t know if it’s understandable or complete. Until then, here’s what I know.

Don't get overwhelmed by this. You don’t have to understand SQL code (I don’t). It takes time, but it's really not that hard. Even if it takes 8 hours, you can save much more than that over the long haul. We use this for several things, but it is very helpful to have a Trial Balance in Excel for monthly reconciliations. We simply open the spreadsheet, refresh the data, and we have a trial balance with live data. It takes only takes us a minute. Then I can link reconciliations to that page to compare the detail to the GL. No more running reports and manually typing in GL balances. It’s automatic, and data entry errors are eliminated. I also use it to do trend analyses on income and expenses.

CREATE A QUERY IN FINANCIAL EDGE

1 - Create a Query in FE7 that you want to be able to see in Excel.

GET THE SQL CODE FROM THE QUERY

1 - Contact Blackbaud. They can open FE for you so you can see the SQL statement of any query you create.

2 - Copy the SQL statement from FE. Paste into Microsoft Word. There may be other tabs in the SQL statement representing temporary files. Copy & paste each one.

MODIFY THE SQL CODE

1 – The temporary files only exist on the day you create the query. The next day, FE creates new temporary files. So tomorrow, the SQL code will try to use temporary files that no longer exist. You must replace references to temporary files in the main SQL code with the SQL code from the temporary files. Here’s how:

2 – In Word, substitute the code you copied from the temporary tabs into the main SQL statement. Take the code from the temporary table tabs, surround it by parenthesis, and then replace each reference of the temporary tables with that block of code.

For example if this was your main SQL tab:

select A, B, Z from ATable Join TEMP as T on T.Z = ATable.Z
And your TEMP tab looks like this:
Blah, Blah, Blah
Then your result could look something like this:
select A, B, Z from ATable Join (Blah, Blah, Blah) as T on T.Z = ATable.Z

2 – Save the Word file

3 – You may need to tweak the SQL statement. Buried in the code were a few references to the temporary files (which you just replaced with SQL code in step 4). Since the temporary files no longer exist, the SQL report only works on the day you create the report. After that, it does not work. If you get this problem, call Blackbaud support. They can tell you how to fix it.

CONNECT EXCEL TO FINANCIAL EDGE

1 – In Excel 2007, open a new spreadsheet.

2 - On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.

3 - Click the Databases tab.

4 - Click <New Data Source>, and then click OK.

5 - The Create New Data Source dialog box is displayed.

6 - In step 1, type a name to identify the data source. (something like FE7, or Financial Edge, etc)

7 - In step 2, click a driver for the type of database that you are using as your data source. (SQL Server)

8 - Click Connect.

9 – the SQL Server Login dialog box is displayed. Select the server that FE7 is on, and add logon name & password if required.

10 – Click Connect.

11 – Select a default table for your table source – I left that blank

12 – Click "Save my user ID and password…"

13 – Click OK

14 – The Choose Data Source dialog box is displayed.

CREATE A QUERY IN EXCEL

1 - Click the Databases tab.

2 - Click FE7 (or whatever you named it in step 6 above)

3 - Click OK.

4 – The Query Wizard opens

5 – Select the Table you want (for example, click the + sign beside GL7ACCOUNTS)

6 – All columns within that table are now displayed. Select the ones you want in your report, and click the ">" sign for each one. (For this example, you can select them all)

7 – Click Next

8 – From here, you can filter the query to only get the data you need. For now, leave it blank and click Next

9 – Now you can sort the data. For now, leave it blank and click Next

10 – Return data to Excel or view in Microsoft Query. For this example, select Excel and click Finish.

11 – the Import Data dialog box opens. Select Table and click OK

12 – You now have the chart of accounts in Excel. Save the spreadsheet.

13 – you can open this spreadsheet anytime, right-click anywhere in the data, and select Refresh. This will give you a current list.

14 – Since we grabbed all columns, you probably have data you don’t want. You can always edit the query and remove those columns.

COPY THE FE QUERY CODE INTO YOUR EXCEL QUERY

1 – Open the new spreadsheet you just created.

2 – Save As – name the spreadsheet something new so you don’t lose your original query.

3 – Right Click anywhere in the Data.

4 – Select Table

5 – Select Edit Query

6 – The Query Wizard opens. Click Next on every tab except the last one.

7 – On the last tab, select "View data or edit query in Microsoft Query"

8 – Click Finish

9 – Microsoft Query opens

10 – on the toolbar, click on the SQL icon. The existing SQL statement will appear.

11 – Delete all the existing SQL statement.

12 – Open the Word document that has your new SQL statement

13 – Copy your new SQL statement into the SQL dialog box

14 – Click OK

15 – You may get a warning that the SQL code can’t be represented graphically. The code is just too complex. Click OK

16 – It will think…how fast depends on the complexity of your query, the size of your database, and the speed of your computer.

17 – When it is through thinking, it will display the results.

18 – Close Microsoft Query.

19 – The results are now displayed in Excel

20 – Save the spreadsheet.

21 - you can open this spreadsheet anytime, right-click anywhere in the data, and select Refresh. This will give you current data in Excel.

I can send you a copy of my SQL code. I don’t know enough about FE7 to know if it will work for you. But if it doesn’t, you can just delete the Excel spreadsheet…no harm done.