Archive for January, 2019



Connecting to QuickBooks data from Crystal Reports

Monday 21 January 2019 @ 9:02 pm
QuickBooks is a popular accounting package and it is used by several of my customers.  But the QB database is not an open database.  This means that connecting Crystal Reports to QB data is not simple or free.  Here are the main products that allow Crystal Reports to connect to a local or online install of QuickBooks.

QODBC by FlexQuarters is an ODBC driver.  It reads the data from the QB database in real time.   It works, but I have found that is often slow depending on the tables used. The price ranges from $149 for a single user to $499 for a server license. I have written about QODBC before.

QODBC for QB online by FlexQuarters is similar to the ODBC driver above, but it allows you to connect to QB Online data.  It is priced by month, $14.95 for a single user.

QuickBooks Online ODBC Driver by CData.  Connects directly to QB Online data.  This driver costs $299 per year, which covers a single workstation.

QQube by Clearify extracts a “data warehouse” copy of the QB data.  You run your reports against the extracted data which is organized into logical components.  This approach makes for fast running reports, but the data is only as up to date as the last extract.  The price ranges from $450 for a single user to $1,595 for a server that supports up to 10 concurrent users. QQube does not currently support QB Online data.

FinJinni by GypsiBI extracts either a local or cloud “data warehouse”. Again this is fast but the data is only as up to date as the last extract.   You run your reports against the extracted data which can include both desktop and online QB data.  The price starts at $999 for a single user and up to 5 companies.  Another $500 will expand to either 10 companies or 6 users.




Downloading the Xtreme sample data

Sunday 13 January 2019 @ 11:19 pm

Starting with CR 2008, the Crystal Reports sample data was changed from an MDB to several XML files. Fortunately, the Xtreme MDB was still available for download, along with the associated sample reports. I linked to this download from my blog and website so that students with newer version of CR could still do the lessons in my course materials. That link worked for about 10 years.

Today I learned that this link has finally died. I will try to find another link to download the file. Until I find one the link will point to a copy on my own site.




Minor changes can have a major impact on performance

Monday 7 January 2019 @ 11:54 pm

Over the holiday break I had a customer contact me about a report that had just started taking a very long time to run. The first place I looked was in the record selection formula where I found this in the second line:

{Orders.OrderDate} -1 in LastFullWeek

I suspected that this was the problem. To confirm I had him send me the original report that ran in the normal time. Here is what the original said:

{Orders.OrderDate} in LastFullWeek

Apparently the requirement for the report had changed from the prior week starting on Sunday to the prior week starting on Monday. That minor change causes Crystal to completely drop the date rule from the automatically generated SQL. This means the database will send back ALL dates and Crystal will have to apply the date filter locally. I had him try this instead:

{Orders.OrderDate} in Minimum(LastFullWeek) +1 to Maximum(LastFullWeek) +1

Both version 1 and version 3 return the same results but version 1 adjusts the field while version 3 adjusts the comparison values. Version 3 will make it into the SQL WHERE clause while version 1 will not.

The same problem happens when you use a function on the field. Here are two common examples I see:

Date ({Orders.CreateTimeStamp} ) in ...

Round ( {Orders.Amount} ) = ...

If the report performance is fine than these examples can stay, but if you need to speed up the report then these should be written without the functions, so that they are incorporated into the automatically generated SQL.




Jeff-Net

Recrystallize Pro

The Expert Series