Archive for the 'Tips' Category



A simpler approach to address blocks

Thursday 14 March 2019 @ 9:33 pm

One of my favorite parts of writing this blog is when people read a post and then send me an alternate approach that teaches me something new.  Like today.

Last month I shared a formula for creating an address block that will automatically remove blank lines. Today one of my readers showed me how he does this with a text object. He uses a formatting property called “Suppress Embedded Field Blank Lines”.  I had never seen this option before so I quickly checked my version of Crystal. There it was in the formatting properties of text objects (not fields). I thought I might have missed this because it was a recent feature, so I started working backwards through the different Crystal versions to see when it appeared. I stopped when I found it in CRv8.5 which is nearly 20 years old. So much for missing a recent feature.

To use this feature you add a blank text object to your report. You then ’embed’ fields by dragging each field over the text until you see a hash mark. This indicates where the field will be embedded in the text, even in the middle of a sentence.  When the hash mark is in the right spot, you release the field and it becomes embedded into the text object at that point.

To create an address block you would add all the address fields into a text object and hit <Enter> between each one so that each field is on it’s own line. At first, any empty fields will create a blank line in the block. But if you go into Format > Text> [common tab], and check the property mentioned above, these blank lines go away automatically.

This may not work in every situation, but it is much simpler then the formula approach I posted last month.  And thanks to Duane Fenner, an Accounting Support Specialist at LTi Technology Solutions for sharing this with me.




Applying a formatting condition to multiple fields

Thursday 28 February 2019 @ 9:59 am

If I want to remove the decimals of several fields at once you can use CTRL-click or a cursor lasso to select all the fields, then go to the toolbar and hit the “reduce decimals’ button. Each click will remove one decimal from all of the selected fields.

You can do something similar when you want to apply a formatting condition formula. For example, if you want to turn negative numbers red while leaving positive numbers black. The font color condition formula looks like this:

if currentfieldvalue < 0
then CrRed
else CrBlack

To apply this formula to one field you select that field and then select the menu items “Format > Field”. On the “Font” tab you click the condition formula button next to font color. It will usually look like the top one of these buttons:

Once inside you paste in the formula above and then click “Save and Close. The formula button should turn red and look like the middle button above. This means the condition has a formula. When you click OK the negative values for that field will turn red.

Note that the formula doesn’t refer to a specific field, but to the function “CurrentFieldValue”. This function is only available when you do condition formulas and refers to the value of the field you are formatting. The advantage is that the same logic can be used on any numeric field and the condition will be exactly the same, rather than each field having to have a different formula that mentions a specific field.

If you want to apply this formula to several fields at once you could select that group of fields and then select the menu items “Format > Objects”. Like above, you go to the “Font” tab, click the [x+2] and paste in the formula. When you click “Save and Close, then then click OK all of those objects should have that property.

One thing to look out for when you are formatting multiple fields at once is a purple condition button (bottom example in the picture above). This only appears when you try to format multiple fields at once. This tells you that some/all of these fields already have a condition and that not all of them are the same. If you click a purple condition button it will show you a blank formula. If you put in a new formula you will overwrite any existing logic and all of the selected fields will end up with the new condition.




How link direction can affect performance

Saturday 23 February 2019 @ 1:33 pm

I have written before about links that tap into indexes and how they can speed things up. Especially when you can hit ALL of the fields in the index.

This week I was troubleshooting a report that took 2 hours to run and found a similar case. The report was from a Sage/MAS accounting application. I saw a link between invoiceHistoryHeader and InvoiceHistoryDetails where it took 2 fields to make a unique match.  I checked the index tags for the primary key (red colored tabs) and and found that there were 3 fields in the details table primary index while the header table had only 2. Since we only had two of those fields to use for linking I wanted to make sure the link went from the details to the header, so that the link would completely hit the index. From the arrangement of the tables that appeared to be true, but when I hit “Auto Arrange” I could see that the join actually started with the header and went to the details.

Reversing the join allowed the report to complete in 7 minutes. Still slow, but a huge improvement over 2 hours.




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.




«« Previous Posts
Jeff-Net

Recrystallize Pro

The Expert Series