Microsoft Access as a Reporting Tool

Need more than ad-hoc reporting? Need intermediate tables? Need faster reports? Need to mail report snapshots? Well, looks like you need to find out more about Microsoft Access. In my first in a series of ramblings about Access, here is a simple Why?Where?Who?. Click below to read the entire article and remember to post your comments! ;-)
Please click on "Read the rest of this article!" link below.

I like CP Report as much as anybody. Flexible, fast and very easy to use. But Access comes in a lot more handy in the following cases:

1. You need to get the same data and format it a couple of different ways into multiple reports.
2. The report will involve more than 10,000 rows and is very sluggish on CP Reports (swim! fishy! swim! scenarios)
3. You will need intermediate tables from different 'catalogs' to come together in a report.
4. You want to include data that is not stored in Costpoint.
5. You already own Microsoft Access as part of Microsoft Office and it's just lying there, unused.
6. You want to export your report to Excel with detailed, precise formatting, grouping, subtotals etc.
7. CP Report Macro language just isn't powerful enough for you.

You have to be warned here: Access is not as easy as CP Reports and does have a learning curve, but a few books and a couple of hours later, you may even be ready to write your first report. You will need help from your tech people! As always, use this information at your own risk and experiment before you put any reports in to a production environment.

Here is a roadmap for this article:

• Setting up connectivity to your Costpoint Database *ODBC* drivers.
• Know your tables.
• Definitions: LINKING TABLES; QUERIES; REPORTS; FORMS; MACROS

I am familiar with ORACLE here, but most of the details should hold good for any other Database Platform as well...

1. Setting Up Connectivity

Before you can start using Access to create your report, Access need to be able to see the Deltek tables. If you have Oracle or SQL Server, Access comes with built in drivers to connect to these databases. If you have any other platform, you should be able to get ODBC drivers from your vendor. Once you have the connection setup (this is fairly easy! Read the help documents! call tech support!) you can login to the deltek database and connect to specific tables. Access will setup a 'shortcut' to these tables so that you can use them transparently like access tables. A word of advice here: NEVER LOGIN THROUGH ACCESS AS THE DELTEK USER! Give yourself select right to whatever tables you need through SQLTalk and then connect as your usual user to the database.

2. Know your tables.

This you should already know from playing around with your reporting tools catalogs. Here are a few of the straightforward ones:

Information about GL: GL_POST_SUM, GL_DETL, FS_SUM

Information about AP: VCHR_HDR_HS, VCHR_LN_HS, OPEN_AP

Information about Billing: AR_DETL_HS, AR_HDR_HS

Of course, these can vary by system/version etc. If you are not sure about tables; how to get a certain report done, call Deltek Support (I know I have!), ask around on e-DUG.

3. Definitions:

Linking: Tables that you attach in a given Access database are like local tables in the database. You can use them to generate other local tables, write reports etc. As long as you login as a user who only has SELECT rights on the table, you cannot change any data in the table, which is a good thing!. When you delete a table you don't need in the database, you are only removing the link to the table. Try not to save passwords to tables in your Access databases. Linking also allows you to connect to databases in other enterprise systems and consolidate the data on your reports.

Queries: You can use wizards or interactively design queries in Access that are saved locally in the database. These allow you to take data from different tables, create intermediate tables in the local Access database etc. You can work with queries in SQL view to see exactly what they are doing and how they work. I strongly recommend that you spend some time trying to understand SQL, it will definitely pay off in the long run!

Reports: A Report is what you finally need. These can be based off of queries or local tables in the database. It is easiest to use a wizard to get all the fields you want on a report and then tweaking the report in design view to get precise formatting control. Reports can be nested within each other and are very flexible. Reports in Access 2000 with subtotals will export to an excel sheet with grouping a subtotals! You can also save Report Snapshots that can be seen you others who do not have Access using a free viewer. What I find most useful though, is the ability to save reports as Rich Text Format (.rtf) files which can be opened in a variety of word processors and will print exactly as you see them!

Macros: These add the finishing touches to the Access reporting structure. Automate all your monthly reports to be generated with one click! Run the same queries and reports over and over again by defining different filters for different departments! Export the reports automagically to the department directories! Keep a local table in the database with e-mail addresses and have Access e-mail the reports!

Forms: Forms allow you to setup complex prompt screens; remove the entire complicated access interface and have users select from drop downs and checkboxes to get their jobs done faster and easier.

VBA: Visual Basic for Applications. Yes, this is not listed above because you don't really need it to make Access an excellent reporting tool, but knowing VBA will make your work a lot easier! VBA is a subset of the Visual Basic programming language that can be used to control access. It is the next step of evolution from Macros and is what I use to get most of my reports and analysis done. It is easy to learn with a good textbook. With the Developers Version of Office 2000, you get a license to distribute a 'Runtime Engine' for Access which will enable you to write complicated reports and install them on machines that don't have Access!

Next time: Lets write a simple report!

Talk back: Tell us what you use for your complicated reports? Do you end up keying your information into excel spreadsheets? Have you tried using FileMaker for reporting? After all, we're here to find out what the rest of the group already knows!

Read the rest of this article!

My Deltek Wish List

These are not the small wishes, these are the BIG ones which would completely revolutionize how we use the system. You have your own you say? Well what are you waiting for! Click on the Comments link and post your feedback!

Here are the big 5!

Please click on "Read the rest of this article!" link below.

1. Expose Costpoint *Insert your favorite product here* objects:
Accounting System from the User Interface. Imagine your own form for creating projects, with only the fields you need! This is an ERP system; it should be more customizable on the user end. Want to create 1000 projects from a spreadsheet? Create it through Visual Basic, or my personal favorite, Python. Write your own preprocessors! Build your own web based front-end for a Deltek screen in hours! Integrate enterprise systems in days! WOW! There are no limits! I want to see Deltek listed on this VBAEnabled Software page!

2. Web Based Oracle Style Support:
I login to my Support account on the web, write a detailed description of my problem, upload screenshots of errors etc. What could be easier? Deltek Consultants team up; browse through problems; focus on their strengths to help each other out. I can keep track of all my open requests. Look up my history to see how we fixed this problem earlier (before we applied the upgrade! how often do you have the same problems after upgrades?). No more: You Call; Leave Message; They Call Leave Message Miss You; You Wait ad infinitum. (To give Deltek some credit, the present system isn't bad, but this could be sooo much cooler!).

3. Support Open Source/Free Databases:
Their not as good as the commercial databases, but their out there and could use more support. Imagine free databases customized for Deltek software. No more SQL Server/Oracle licenses based on users/processors etc. This could make Deltek software much cheaper to implement! Want to check out open source databases? Look at PostgreSQL (most likely candidate)
or the upcoming release of the completely free (price and source) Interbase.

4. Linux based clients:
Three words: "Illegal Function Call". I have clients crashing all the time in windows leaving ghost users, corrupt tables and some very frustrated users. Yes, we could probably migrate to Windows 2000 but that means new hardware, more resources, additional license costs. Would would it take to port some of the Deltek applications to Linux? Free; Stable; Open Source.

5. More Documentation
The documentation we get is fine. But it could be better. Half the support calls I make would be unnecessary if I could get more information about the system; not how to use it, but how it works... Maybe even a user discussion site where I could talk to other people who use the same module. Perhaps find out how they do things when they run into problems... oh! wait! Looks like this has already been done at e-DUG!!
Let us see if it helps!

Read the rest of this article!

First post

This is my original post from around 06/17/2000 kept here for historical reasons!:

When did this happen you ask? You're probably one of the first people to notice this site! Read on to find out what's going on:

What is this about?
I have been planning to put this site up for some time now, but never got around to it. As a financial systems analyst I have searched the web many times for information about Deltek but haven't found anything other than the company web site. I wanted to join a user group but there aren't that many Deltek clients around St. Louis and I want to do more than meet people once a month and try to remember what they did and where they worked. And now, it's happened...

...I've spent a lot of time working with Costpoint and other Deltek products and I have a lot of ideas I would like to share with other users out there. So here we have it - the web based Deltek User Group where you can meet other people who are using Deltek products, discuss technical problems, announce process ideas, comment on new functionality, request new features or just read what other people have to say.

[Editors Note: I am no longer in St. Louis, MO]

How does this work?

Anytime you come up with something that you feel the rest of the community would be interested in, just go to the submissions page and fill out the form it will be posted on the front page and everybody will have a chance to read it and add their comments to it. If you would like to write an editorial you can submit that too! If you have a question about a business practice, or anything else that you might want to bounce off your peers before implementing, all you have to do is post it to the e-Deltek User Group!

This site is NOT AFFILIATED IN ANY WAY TO DELTEK SYSTEMS. This is strictly a user site run by a user for other users! I got my inspiration from www.SAPfans.com. After all, Deltek is an ERP software!

Read the rest of this article!