Tim Richardson

Melbourne, Australia

  • Increase font size
  • Default font size
  • Decrease font size

Building Accpac reports in excel: part 1

E-mail Print PDF

In my Accpac review I mentioned that I avoided using Crystal Reports. Instead, most of my queries and reports have been Excel spreadsheets. Typically, one tab is populated with data from Accpac, and then the report pages are pivottables. This allows users to drill down, summarise and work with data in a familiar environment. Basic queries also avoid any additional licences.

In this article, I will show an example: a report analysing cash transactions against bank accounts.

In next article, I will show a more advanced example: an aged receivables report, which uses an Excel macro which first connects to Accpac to update the aging tables, and then queries the result. This is a very interesting example because it shows the true power of automating Accpac with Visual Basic for Applications, a simple technology available to any one with Microsoft Office. 

Part A: Making sure you can query data

Assumptions. You have a System DSN with read access to the SQL server database, and we'll call this System DSN "database". I assume you are using MS SQL Server.

You have an Accpac database, which I will call SAUDAT.

1. Set up a System DSN connection. To keep things simple, I assume there is a System DSN set up called "database" which connects with the SQL Server hosting Accpac. The security can be trusted user; ask your IT administrator to put your selected users in a group allowing read access to the database. This gives the user visibility into all the data, but not the ability to change it. You may be uncomfortable doing this. There are alternatives, such as creating report users which have access to only some tables, and setting up the DSNs to connect with that user (this is how Accpac itself works), or completely restricting the ability to update data in the spreadsheet to trusted users.

2. Connect Excel to the datasource using MS Query. In Excel 2007, open the Data tab. and choose "From Other Sources", and then choose "From Microsoft query".

Choose the System DSN set up in 1. Turn off "Use the Query Wizard to create/edit queries".

The next step may be a security dialog. Keeping things simple, I will assume the current windows user has read access to the datbase, so we can just choose "Use Trusted Connection".

Next, you will be see Add Tables. If you ended up with the query wizard, cancel it, then you will see Add Tables.

Close Add Tables.

Under Edit, you will see Options. Go to the Options dialog, and turn off "Validate queries before saving or returning data". Sooner or later you will use SQL which MS Query doesn't understand, but which makes sense to SQL Server.

3. Enter a simple query

The SQL button on the toolbar lets you enter SQL directly. I tend to build my queries using SQL Server Managament Studio, and then copy and paste them into this dialog.

For this first example, paste this SQL:

(remeber, SAUDAT is my database name; use your own)

use SAUDAT;
select * from GLAMF

 

Note that the line "use SAUDAT;" is not necessary if your DSN specifically refers to the database you want. I put it there out of habit. However, it confused MS Query a little, meaning you can't graphically edit the query.

This queries your accounts table. You can now exit MS Query, returning the data to Excel. Take the default option of storing the data as a Table.

If you get errors about invalid SQL, check my instruction above about turning off SQL validation under options.

4. Some useful techniques for Excel and embedded queries

rename the sheet with the query data "data".

The query is embedded: right clicking on the returned data table brings up a context menu allowing you to "refresh". This executes the query again; a spinning globe icon near the bottom left corner is the feedback that the query is executing. You will need to refresh the pivot table as well.

Modifying the query: Go to the data tab. Click Connections. Find the query you want. If you have more than one query, select a query and click the text "click here to see where the selected connections are used". This will tell you the sheet linked to the query.

Once you have the right query, select it and click Properties, or double click the query name. This brings up a dialog box with two tabs. The query SQL is visible on the Definition tab, under Command Text. You can edit the SQL directly here, or choose to Edit Query.

 

Part B: A useful example: reporting on cash transactions

In this tutorial, I'll show a more complex query, and some other tips for working with returned data. The tips will need Excel 2007 or later, since I take advantage of some of the convenient features of Excel 2007's tables.

Start a new workbook, and proceed with the steps above until you are ready to paste SQL.

This is the query:

(note that this time I won't use the "use saudat;" of the query above.)

SELECT GLJEH.BATCHID, GLJED.BATCHNBR, GLJEH.BTCHENTRY, GLJEH.BATCHID, GLJEH.BTCHENTRY, GLJED.JOURNALID, GLJEH.FSCSPERD, convert(varchar(10),GLJED.transDATE,103) AS 'TRDATE', GLJEH.FSCSYR, GLJEH.SRCELEDGER, GLJEH.SRCETYPE, GLJED.TRANSAMT, GLJED.SRCELDGR, GLJED.SRCETYPE, GLJED.ACCTID, GLAMF.ACCTGRPCOD, GLAMF.ACCTDESC
FROM SAUDAT.dbo.GLAMF GLAMF, SAUDAT.dbo.GLJED GLJED, SAUDAT.dbo.GLJEH GLJEH
WHERE GLJEH.BATCHID = GLJED.BATCHNBR AND GLAMF.ACCTID = GLJED.ACCTID AND GLJEH.BTCHENTRY = GLJED.JOURNALID AND (GLJEH.FSCSYR='2011') AND (GLAMF.ACCTGRPCOD='0110')

 

This query links GL journal details with the accounts table. A key selection in the where clause at the end of the query limits it to account codes belonging to an account group. For the purpose of this query, this account group is all the bank accounts.

You can finish the query and let it return data. Store it as a table, and make sure the table begins in cell A1 (top left corner). Rename the sheet "data", as good practice.

We will make a pivot table based on this data. There are some extra columns to add to the data first, to help. I'm going to use formulas; it's possible to do all of this in the SQL query, but Excel 2007's Tables are convenient. When you add a formula column to a data table, it adds these formulas to new rows if the query returns more data, and if you add fields to your SQL query, it accomodates by adding columns to the table, therefore preserving the formula columns you added.

The final column returned by the data is the ACCTDESC column, in column Q. Name a new column "Date1" in column R (put this column heading in cell R1). Put the formula below in R2

=DATE(LEFT(tabledata[[#This Row],[TRDATE]],4),MID(tabledata[[#This Row],[TRDATE]],5,2),MID(tabledata[[#This Row],[TRDATE]],7,2))

This turns the Accpac date into an Excel date. There are other ways to do it; this is what works for me. I use ISO format for my dates, so I get formats that look like "2010-12-25" (Christmas Day), but the formula above simply returns an Excel date. Copy the formula to the whole column, if like me you have auto-fill turned off.

In column S I add a column called "AbsAmt" with formula:

=ABS(tabledata[[#This Row],[TRANSAMT]])

This is very easy to do in SQL, but simple additions to the query like this are very easy to do taking advantage of Excel's table functionality.

In column T I add a column called "Sign" with formula

=IF(tabledata[[#This Row],[TRANSAMT]]>0,"IN","out")

and in column U i add a column called "Nature" which has the job of marking transfers between bank accounts.

=IF(tabledata[[#This Row],[SRCELEDGER]]&tabledata[[#This Row],[SRCETYPE]]="BKTF","TF","")&tabledata[[#This Row],[Sign]]

Finally in column V I show the week number. I use a custom function which returns IsoWeekNumber  and I number using the Australian financial year, so my column heading is Weeknum with formula

=MOD(IsoWeekNumber(tabledata[[#This Row],[Date1]])-26-1,52)+1

but you can also use the standard weeknum function 


=weeknum(tabledata[[#This Row],[Date1]])

An example chart is shown here, and the spreadsheet can be downloaded from here. The data is randomised, and I zeroed all the bank transfers. The spreadsheet has macros, which may cause your anti-virus software to alert.

Cash Chart

 

My next article will show an AR report. This requires a more advanced technique, since we need to get Accpac to update data before querying the database.

I have here a scrapbook or cookbook of useful Accpac queries on various topics.

Therefore, this introduces the interesting concept of automating Accpac from within Excel using Visual Basic macros.

About the author

Tim Richardson is an experienced finance professional who differentiates himself by his versatility, his IT and system skills, and his people skills.He has an enormous range of leadership experience gained in the FMCG sector, online B2C, manufacturing and international supply chains, and he has worked in emerging Asia, mature Western Europe and transforming Central and Eastern Europe. His qualifications span the communication strengths of an Arts degree, the analytical skills of a Computer Science major and the professional skills of a Masters degree in Finance. His has built teams of all kinds of talent mix and lead them through change, and has a strong record in recruiting and developing people. His practical IT skills and systems background is very strong in maximising the value of information spread throughout the organisation, and his abilty to help experienced management focus on the issues and consequences of decisions enables him to add value very quickly. He is currently enjoying his CFO role with an Australian SME with a major international online presence.

 


Add this to your website
Last Updated on Wednesday, 22 September 2010 11:28