Excel and ODBC For Reporting

[elo]

So is anyone out there using Excel (with ODBC) for reporting? Even for simple reports, Excel affords a lot to the user (Pivot Tables, Charting, auto-filtering for interactive refinement, further computations auto-filling down next to the data, etc.)

I've started playing with a pretty simple Excel and some Visual Basic for Applications (VBA) macros that allow you to specify the DSN and Query in your workbook.

The FM ODBC driver is VERY tetchy about what it will take as input compared to SQL Server ODBC driver, but if you can live without OUTER JOINS (not supported) you can make some interesting interactive reports in Excel that are live to the database.

The relevant Visual Basic Code to grab the DSN and Query from your Excel workbook is:

Public Sub ResetQuery()

    Dim qt As QueryTable

    Dim ws As Worksheet

    Dim nm As Name

    

    

    Set ws = ActiveWorkbook.Sheets("Data")

    Set qt = ws.QueryTables(1)

    

    qt.SavePassword = True

    

    

    ' get the DSN

    Set nm = ActiveWorkbook.Names("DSN")

    

    qt.Connection = nm.RefersToRange.Value

    

    

    ' get the query

    Set nm = ActiveWorkbook.Names("Query")

    qt.CommandText = nm.RefersToRange.Value

    

End Sub

And a refresh queries/pivotable script to link to the Workbook_Open event:

Public Sub RefreshAll()

    Dim qt As QueryTable

    Dim pt As PivotCache

    Dim ws As Worksheet

    Dim old As Boolean

    

    

    For Each ws In ActiveWorkbook.Worksheets

        For Each qt In ws.QueryTables

            old = qt.BackgroundQuery

            qt.BackgroundQuery = False

            qt.Refresh

            qt.BackgroundQuery = old

        Next qt

    Next ws

    

    For Each pt In ActiveWorkbook.PivotCaches

        pt.Refresh

    Next pt

    

End Sub

Also, with some experimentation I've found that unstored calculations that use the FMPro List function across a Many-to-Many relationship can flatten data, e.g. all students in class A, into a single field nicely. More generally unstored calculation fields can be used to approximate OUTER JOINS at the expense of cluttering your FM database with a lot of "gunk" fields.

[berny]

Hi elo,

sounds interesting...

I am currently looking into measurement graphs dependent on data in a database. Any ideas?

Thanks,

berny

[elo]

This would totally work if you can construct a SELECT query from the table to get what you need.

Say you have a table Measurements which have say a timestamp and a value.

You could:

SELECT mytimestamp, value FROM Measurements

as your query

And then graph the data worksheet contents in excel.