This weeks puzzle:

A customer upgraded to Sage (PeachTree) v2015 last week. Everything seemed to work fine, but their most important Crystal Report wouldn’t run because it uses Sage custom functions, and those functions were not showing up in Crystal. The Sage software has a button to install these custom functions, but clicking this button didn’t change anything. Sage support couldn’t solve the problem so the customer called me.

I know that all Crystal custom functions come from DLLs and I assumed that this Sage DLL was either missing or in the wrong place. The customer asked Sage support for the name of the DLL so we could search for it. They sent him lots of information, but not the file name.
So the customer did a fresh install of Sage v2015 on a local PC, and everything worked correctly. So I opened up Crystal and could see the Sage custom functions listed together – along with the name of the DLL.  We searched for that file (U2Lpeach.dll) and found it in:

C:\Windows\Crystal.

We then checked that same folder on the server and found that the DLL was in that same folder. But yet when we opened Crystal on that same server the Sage functions were not visible.  Crystal had several other custom functions that were working, so I searched for those DLLs and found them in a different folder on the server:

C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\win32_x86

Since we knew that Crystal could read the DLLs in that folder, we copied the Sage DLL and pasted it in with the others. Once we restarted Crystal the Sage functions appeared. The difference probably has to do with a windows environment variable somewhere, but rather than mess with the server settings we decided that a duplicate DLL was workable solution.

Crystal added a feature 7 years ago, and this week I had to use it for the first time. I was working with a Pervasive database and testing how different join configurations would perform. After one change the connection started throwing an error that the SQL was invalid. The configuration in the LINKS window looked identical to another report that ran fine. But when I compared the SQL of the two reports I found that although the tables were linked the same, the SQL statements were adding the tables/joins in a different order. I needed a way to tell CR to do the same joins, but in a different order.

That is when I remembered a feature introduced in CR 2008. Whenever you have more than one join (3+ tables) you can right click in the background of the linking window and select “Order Links”. This allows you to specify the order in which the tables are incorporated into the “FROM” clause of the SQL statement. Normally the link order takes care of itself, based on the direction you drag the link lines. But in this case it appeared that the only way to get the SQL to work was to have the one Outer Join we needed as the very last join in the FROM clause. Sure enough, when I moved that link to the bottom of the order, the SQL ran without error.

My first call today had to do with a report that was working, but took 20 minutes to complete.  Other similar reports ran in a few seconds so I was asked to find the difference.  I noticed several added tables and a different table configuration which were likely places to start.  But the one difference that looked most promising was the number of join lines between the tables.  In the reports that ran quickly, every pair of tables had one additional join line between them.  And in all cases the field being joined was called SystemID.

Apparently, this software package allows you to maintain the data for several completely independent ‘systems’ in the same database.  This SystemID identifies the system of each record. The customer explained that since they only use one ‘system’ they didn’t think the link on SystemID was essential. And the report did seem to generate accurate data without the SystemID link, if you didn’t mind the wait.

I explained that the indexes that facilitate the joins between tables were all probably created with the SystemID as the primary key. So any join without a SystemId value couldn’t tap into the index.  Without the help of an index, the database would have to do a ‘serial read’ (record by record) to find the matching records.  Think of finding a topic in a book without an index.

So we added the extra line for SystemID between every pair of tables and tested the report.  The report ran in about 8 seconds. The lesson here is that even links that seem redundant can make a significant difference in the performance.

I learned a nifty trick from a customer this week.  They had a bar chart with names underneath each bar.  After each name was a number in parentheses, which was the quantity for that bar.

I was surprised that CR allowed a formula with a subtotal in it to be used as the ‘on change of’ in a chart.  Then I tried it myself and confirmed that it works. So lets take a simple example from the Xtreme sample data that comes with CR.  I created a simple report that was grouped by customer.  I then added a subtotal that showed the number of orders for each customer.

Before I added the chart I Continue Reading »
Chart labels that include subtotals