Archive for May, 2019
I have written before about databases that take selection criteria from Crystal and then use the wrong index so that valid records are missing from the results. The solution is to write the criteria so those rules don’t make it into the SQL WHERE clause. Crystal can then apply that criteria locally so it is done correctly.
But today I ran into a similar problem that didn’t have a simple solution. I was creating a report to read Elliot data. Elliot is what used to be called Macola Accounting. We were connecting to a Pervasive SQL DB using an ODBC connection. We were trying to link the Item file to a second instance of the Item file to get a list of components for manufactured items. What we found is that when we joined the component ID from instance one to the part ID in instance two, the results would not return a single match between the two tables.
Looking at the tables separately showed the matching data was there. And when I tried to filter to a single PartID the results would not find that ID. This is when I realized that we had an index problem like the one I described above.
So I looked at the index tabs in the Database Expert and noticed that this table had two red index tabs, meaning there were two fields in the primary index. The tabs were on Item Number and Sequence number. We were linking from a table where there was a component item number but there was not a component sequence number. It appears that Pervasive SQL defaults to using the primary index for ODBC joins, even if the fields you are using for the join don’t completely match the fields in the index. So the link will fail every time. I even unchecked the option “use indexes or server for speed” to see if that would help, but it didn’t have any affect.
We were lucky that the table we were using had an equivalent view. We linked this view to itself and we were finally able to find matching records. I assume this worked because this view, like most views, are not indexed.
I solved another occasional mystery today.
Crystal allows you to put your groups in order based on the summary fields that exist for that group. This feature is in the “Report” menu under the label “Group Sort”. So if you group by customer and subtotal sales for each customer you can put the customers in order based on their sum. You can rank the customers this way in either ascending or descending order.
You are allowed to use any type of summary field for group sorting, and on occasion I have used a summary that is based on a date field. This could be the first (minimum) order date of for each customer or the last (maximum) order date for each customer. I have noticed that sometimes when I rank a group based on one of these date summaries that Ascending seems to behave like Descending or vice versa. For instance I might pick Descending and I expect the groups with the latest dates to be first. Sometimes they are and sometimes they aren’t. Before today I had never taken the time to figure out what was going on. Now I know.
If your summary is the Maximum of a date field, like the last order date for each customer, then setting the group sort to ascending or descending will behave in the expected way. Ascending will put the groups with the earliest summary dates first and descending will put the groups with the later dates first.
But if your summary is the Minimum of a date field, like the first order date for each customer, then group sorting for that field will work in reverse. Ascending will put the groups with later dates first and descending will put the groups with earliest dates first. To get the groups to go the way you want you just have to pick the opposite direction.
A couple of notes:
1) This doesn’t happen with a minimum summary of numbers or strings, just a minimum of dates.
2) You can get the same summary value as the minimum by doing the Nth smallest (with N = 1). If you use the Nth Smallest summary for group sorting it does the ascending/descending the normal way, not reversed like the minimum function.