Sunday, June 18, 2000

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.

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!


Post a Comment

<< Home