Archive for the 'Tips' Category



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.




Setting section height to a specific number

Monday 31 December 2018 @ 12:26 am

The section height in Crystal is an analog setting, not a digital setting. In other words you can’t go into the section expert and set the height of a section to exactly 1.25 inches. You have to go by the ruler on the side and make the adjustments visually. But if you know the secret, there is a way to force the section to be exactly the size you want. It relies on the fact that field objects have both a size setting and a position setting that you can set digitally. Here are the steps:

1) Make the section smaller than your target size.
2) Place an extra field near the top of that section.
3) Right click on the field and select “Object Size and Position”.
4) Set the “Y” property for this field to zero which puts the field at the top of the section.
5) Set the height property for this field to the desired height for the section.
6) Click ‘OK’
7) Delete the field.

When you click OK, the object will grow vertically to the desired height, forcing the section to grow. And, since the object is starting at position zero the section will be exactly the same height as the object. This makes it easy to create many sections that are all exactly the same height.




SQL Server dates show up as strings

Wednesday 14 November 2018 @ 4:57 pm

Before SQL server 2008 all date values were stored as DateTime values, even if you didn’t need the time portion. Starting with SQL Server 2008 you could a column either as a Date (with no time) or a DateTime. But I have noticed, recently, that anytime I create a field with a “Date” type, Crystal sees the field as a string instead of a date. So even though I usually don’t need time values, I typically create my table fields and calculations as DateTimes. That way Crystal can format the fields with date options and do date calculations in the report.

But one of my customers recently asked me about this. She found that this only happens if you use the SQL Server ODBC driver. Apparently, the SQL Server Native Client doesn’t convert date fields to strings. So I did a test by creating two DSN’s to a test database and a test table. One DSN uses the SQL Server ODBC driver (10.00.17134.01) from 2018. The other uses the SQL Server Native Client 11 (2011.1102100.60) from 2011. Sure enough, a report using the Native Client maintained the date value as a date, while the ODBC driver converted it to a string.

Then I read this page where Microsoft now recommends using OLEDB:

When I first tried OLEDB I saw two providers. They gave me the same two results as above, which told me that these providers were using the same two drivers I had just tested with ODBC. That is when I realized that the article was talking about a newer OLEDB driver. I downloaded and installed this driver but if you aren’t careful it is easy to miss it in the list of providers. It looks very much like the old one. The only difference between the new one and the old one is that the new one uses the word “driver” while the old one uses the word “provider”.

Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
Microsoft OLE DB Provider for SQL Server (SQLOLEDB)

The name in parens is what you see under connection “properties” in Crystal’s “Set Datasource Location” window. When I used the new MSOLEDBSQL driver I got date values.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for letting me know this behavior was driver related.




Improving report performance with a subreport?

Friday 26 October 2018 @ 9:54 am

In most cases, subreports are a last resort. Typically they slow things down by adding an extra query to the process. But this week I found that moving some tables to a subreport actually sped things up.

The data came from the fundraising software Raisers’ Edge, which uses data exported to an MDB. The customer had designed a new report and found that it ran for over an hour without completing the query. Nothing looked wrong in the structure so I did some troubleshooting. I started with one table and then added the other tables a few at a time to see which table was the problem. All was fine until I reached the last 17 tables which were all linked back to a single table. We only needed one record from each of the 17 tables and they all had about 500 records.

I was able to add the first three tables without issue, but beyond that the report would slow down more with each table added. It only took a few more tables to realize that we couldn’t add all 17 tables to the report and expect it to complete. I double checked the links, confirmed the indexes were in place and still couldn’t find any cause for the slowdown.

Finally, I removed those tables from the report and created a subreport that included just those tables. I also included the table that linked them all together. The subreport ran instantly both on it’s own and when inserted in the main report. My guess is that the MS Access engine was struggling with the number of joins, so splitting them into two separate queries made it more manageable.




Crystal Reports FAQ on the SAP website

Thursday 30 August 2018 @ 5:41 pm

I just stumbled across a FAQ on the SAP website that has some useful information. It was written in 2016 but the information still seems to apply. Many of the answers are links to other pages, like the link to the trial versions or the links to the service packs. I already had most of this information, but learned at least one new trick from the FAQ page.

Retrieving your License Key from the registry:

With older versions of Crystal you could go into Help > About and grab your complete license key.  This was helpful if you were changing hardware or installing on a second PC. With more recent versions you have to go into the license manager and you can only see a portion of the key. I recently had to track down a license key and wished I knew how to extract it from the PC.  Today I saw that 0ne of the tricks in the FAQ is how to find a full license key by searching the registry, using the portion of the key you can see in the license manager.  I’ll be ready next time.




A temporary change that expires automatically

Saturday 25 August 2018 @ 8:55 pm

Sometimes I need to make a temporary change to a report. For instance I want to might want to skip invoicing one or two customers for a week or two. So I will put a rule in the selection formula that eliminates them from the report. However, I have a tendency to lose track of these changes. Several months later I will realize that these customers have not been getting invoices.

So I have started putting in changes that are time limited. That way, I don’t need to remember to reverse the changes. For example, if I want to hold off on invoicing two customers for the next few days I could add something like this to the selection formula:

. . . and (if DataDate < Date (2018, 8, 31) then not ( {Cust.ID} in [‘ABCD’ , ‘EFGH’] ) else True )

In English this says, ‘if this report is refreshed before 8/31 then don’t include these two customers, otherwise ignore this rule’.

Eventually I will notice the rule and take it out but I don’t have to worry about when, because the rule turns itself off automatically. You can put a similar time limit on any change that can be driven by a formula.




Why is the group tree ODD sometimes?

Thursday 9 August 2018 @ 6:15 pm

When you preview a report in Crystal the left side of the screen should show you the “group tree”.  This lists all of the groups in the report.  It also allows you to go directly to the first page of any group, just by clicking on that value in the tree.

But a few times a year I work with a report where the group tree is in “Only Drill-Down” mode (ODD). In this mode, every entry in the group tree is accompanied by the drill-down indicator (a magnifying glass).  Clicking on an entry no longer takes you to the first page of that group but instead it takes you to a drill-down tab for that group. To get to the correct page for a group I have to do a search.

It is a minor irritation so I have let it go for years.  It just never seemed worth the time to figure out why some reports do this. But I figured it had something to do with the Hide/Suppress properties of the Group Header (GH) and Group Footer (GF). This week I got an ODD report from a customer, and so I decided to test all the combinations and see which ones were ODD.

I found four rules that control this behavior:

  • If either the GH or the GF is visible you get the normal group tree.
  • If both of those sections are suppressed you get the normal group tree.
  • If both of those sections are hidden you get the ODD behavior.
  • If one of those two sections is hidden and the other is suppressed you get the ODD behavior.

I can’t explain the reasoning behind this pattern (or even the purpose for the ODD behavior) but at least now I know how to change it when I see it.




Crystal Reports new features by version

Friday 15 June 2018 @ 11:25 am

My customers use a wide variety of Crystal Reports versions.  Just this past week I fixed a report for a customer using CRv8.5, which was released somewhere around 2001. There have been 8 newer releases since then and most have included new design features. Sometimes I forget which versions are needed to do certain things. Often I have tried to update a report for a customer, only to realize that my Plan A won’t work in their version. Whenever I need to check when a specific feature was introduced, I refer to the front page of my site which has links to my reviews for each new Crystal release. These articles include the list of new features for each version. But, this still means scrolling through through several pages to find each feature.

To make the process simpler I have created a grid that lists all of the new features added in the past 15 years, roughly 50. For each of the features I show when it was introduced, and which versions that support that feature.  Not only will this help me identify which features I can use for specific customers, it will also help me answer questions about the value of upgrading. I can point a customer to the grid and they can quickly see all the features that have been added since their version.  They can then decide if those features are worth the cost of the upgrade.

You can check out the grid on my site.  The features listed in blue are the ones I use the most.




Using “Order Links” to improve performance

Wednesday 21 March 2018 @ 9:19 pm

I have written once before about using the “Order Links” feature of the database expert. In that article I used “Order Links” to help me prevent a SQL error in the pervasive database engine. This past week I used the same feature to improve the performance of a report.

This report had many tables but there was one, the Dept table, that provide the primary filter for the report. The user would select a specific department each time they refreshed. But when I looked at the SQL generated by CR, I noticed that the Dept table was the last table joined into the data set. In my mind, that meant that Crystal was bringing in thousands of linked records that would eventually be discarded because they were linked to the wrong department. If the SQL could apply the filter up front we would greatly reduce the number of linked records we would generate, and that would speeds things up.

So I suggested to the user that they go into the “Links” tab of the Database Expert and right-click to find the “Order Links” option. I had them move the Dept join to the top of the joins list and they immediately saw a dramatic improvement in performance. It may not work in every report or with every database (this report was Oracle based), but when there are lots of tables and the report is slow, it is another option to try.




Lasso multiple objects without selecting lines and boxes.

Monday 26 February 2018 @ 1:02 am

One method for selecting multiple objects in Crystal (like most Windows programs) is to “lasso” them. To lasso objects in Crystal you click and hold your mouse button in an empty spot and then drag the cursor to expand an orange rectangle. When you release the mouse button, all of the objects inside (or touched by) the orange rectangle become selected objects.

The problem I often have with this method is that drawing objects (lines and boxes) often get selected as well. For instance, if you lasso a group of objects that are inside a box object, that box will often be selected. Usually this happens if you select the highest object in the box, even if you don’t touch the box itself. If your lasso box touches or surrounds line objects those lines are also selected.

Often I want to select a column of numbers for alignment or formatting and I want to skip over the lines. Before now my options have been to lasso the objects and then carefully deselect the lines and boxes by holding my [ctrl] key and then clicking those objects. Or I could skip the lasso method altogether and select that group of objects by clicking on them individually while holding down the [ctrl] key.

But this past week I discovered something I had never noticed before. If you use the lasso while you are in preview mode, the lines and boxes are NOT selected. I had created a grid of 40 numbers (4 across and 10 down) and had separated the rows and columns with horizontal and vertical lines. Plus there was a box around the perimeter. To select all the numbers individually in design mode would have taken 40 clicks. The lasso would take 13 clicks to deselect all the lines and the box. But while in preview I could select all the numbers in one lasso and none of the drawing objects were selected.

This made it easy to align the columns or change the format for the numbers as a group.




«« Previous Posts
Jeff-Net

Recrystallize Pro

The Expert Series