Level Four: ODBC Reports

Part 4 of Kilimanjaro's EXO Reporting series looks at ODBC Reports

Tuesday April 30th, 2013 Business Process Improvement, Services, Support, Training

In previous levels we looked at structured reporting where a report writer (consultant or super-user) defines the look and feel of a report. These reports have consistent or “fixed” logic of what they deliver. There are, however, situations where users cannot give a precise instruction on how to write a report and need to explore “raw” data. The reasons may include finding patterns, exceptions or calculating custom totals.

Why Excel?

Being part of Microsoft suite, Excel can natively connect to an SQL database using ODBC driver (ODBC stands for Open Database Connectivity).

As data in a database table is arranged as columns and rows Excel is a great fit. One would hardly find a better software to manipulate table data by hand.

In Excel an ODBC link is created via a Data Connection Wizard:

Wizard will then ask for database server name, authentication details, database you want to connect to and will offer a list of tables so you can select one. As this series is not meant to be very technical, details for each step can be looked up on the web or in Microsoft Help files. Kilimanjaro Support technician can assist with creating and troubleshooting an ODBC connection for you as well.

Presenting data in tables

The simplest way is to present data as it is kept in the database – a table. Once data is in Excel, it can be filtered and sorted. User can create additional calculated columns. All the work is saved with the file.

Data can be saved in the file too or loaded every time the file is opened.

Table data is useful when we need a list of records from EXO. Below is a listing of Debtor accounts data straight from DR_ACCS table.

Clicking on the drop-down button in the NAME field will produce a sort/filter dialogue like below:

Presenting data in PivotTable

PivotTables are a fantastic way to explore the data and calculate totals. I hope the reader had a chance to get familiar with these. If not – there’s plenty of information provided by Microsoft as well as third parties to facilitate your learning (see references section below).

Once data is pulled in Excel it will be used in the pivot table as usual. The key benefit of the ODBC pivot approach is that pivot table definition and layout stays saved in the file, while data refreshes as requested.

Pivot tables offer sort and filter options. They allow custom formatting and layout settings. Calculations can be done as straight aggregate functions (sum, average, min/max, count) or as percentage of parent total / grand total or can be a running total. Field names can be changed to be more human-readable.

Below is a PivotTable of Debtor invoices data straight from DR_TRANS table.

Limitations

It is possible to combine multiple tables without SQL – just by using VLOOKUP function in Excel. However, VLOOKUP over large tables is slow. It is against best practice to pull raw data and recombine it in Excel. SQL engine is way more efficient in the task.
Sometimes technical references / lookups are not human-readable. E.g. transaction types in debtors ledger are 1, 4 and 5. Unless user had prior experience with EXO reporting, they may need have numbers translated into 1-Invoice, 4-Payment, 5-Adjustment.
Pivot Table may become slow as data volume increases. There are also limits to how many records can be pivoted (this is dependent on Excel version in use).

One of the common solutions to those issues is to prepare data on the SQL server side and return it in a user-friendly fashion. This can be done via SQL Views – virtual tables that can join tables, perform scalar or aggregate calculations, enforce security and many more behind the scenes. One would need a good understanding of SQL language to be able to create an SQL view, so it may be best to ask for assistance from Kilimanjaro team.

The solution to reporting on very large data volumes that cannot be pre-crunched is using a special tool – SQL Analysis Services (OLAP concept, which will be covered in a future post).

If you have any enquiries, please email us at sales@kilimanjaro-consulting.com, or give us a call at 1300 857 464 (AU) or 0800 436 774 (NZ).