Archive for the 'Tips' Category



No column headings on the last page (v2.0)

Tuesday 29 October 2019 @ 7:29 pm

Last month I wrote an article about suppressing the page header on the last page when there are no details. This is handy if your last page is a subreport, a chart or a cross-tab. After my newsletter went out one of my readers shared her approach to the same problem. She uses the group header of a dummy group, and sets it to “repeat” on each page.

Any time you have a Group Header you can set it to repeat on each page. One feature of  a repeating GH is that it won’t appear on the last page of a group, unless that page has at least one detail record.  I wrote about that behavior in another article long ago.  So the only trick is to create a primary group that includes every record in the report. Then you set this group header to repeat on each page and it behaves just like a page header, with the exception of not printing on the last page.

So how do you create a group that includes all the records in the report? You group on a value that doesn’t change.  If you have a DB field like “company” that doesn’t change you can use it.  But you can always create a formula that isn’t tied to any data fields.  My favorite dummy group formula is:

WhileReadingRecords;
"All"

The word “All” can be any value. Just keep in mind it will appear as the overall node of the group tree so you might want it to make some sense. The WhileReadingRecords function allows the report to see this static value as a recurring value, which makes it eligible for grouping.

Once you create the formula you use it as Group 1 in the report and then go into Group Options and check “Repeat Group Header on each new page”.  If you put your column headings in this Group Header they will appear on every page, but won’t appear on the last page (unless there are details printing on that page).

And thanks to Tina Nordyke, the DBA for Advocates for Basic Legal Equality, Inc for suggesting this method.




Table filters in the database expert

Tuesday 8 October 2019 @ 9:06 pm

This week I worked with two different customers, helping them locate data buried in a large system with hundreds of tables. In both cases I had to use the table filter method to help me find the correct tables. For instance, when we needed to find the vendors table we looked first in the V section of the table list. However, in the system we were using all of the tables names had a 2 character prefixes for a dozen or more different modules. It wasn’t clear which module might contain this table. So I added a filter to the list of table names shown in the Database Expert. This way only table names that contain VEND somewhere in the name would be displayed. It made it easy to spot the correct table.
To add a filter to the table list you need to go into File > Options > (Database Tab).

In the middle section you will see two boxes on the right, and the top one is for table filters. It is labeled [Table Name LIKE:]

In this box you enter the characters you are looking for with a percent sign before the characters and/or after the characters. This percent sign is a wild card symbol. So since we only wanted to see tables that contained VEND anywhere in the name we put in a filter like this:

%VEND%

If I wanted all tables that have TMP on the end I could enter a filter with only one wildcard, like this:

%TMP

The only downside I have seen when using these filters, is that it is easy to forget about the filters. It then takes a bit to figure out why the table list is missing or incomplete. So don’t forget to take the filter out when you are done.




Moving cross-tab numbers to Excel

Thursday 27 June 2019 @ 9:00 am

If you are trying to move cross-tab numbers into a spreadsheet, there is a short cut. You can simply copy and paste the entire cross-tab into your spreadsheet. Right-click in the upper left (empty) cell of the cross-tab and select “copy”.  Then switch to the spreadsheet and right-click in a cell and select “paste”. The cross-tab numbers should appear in the spreadsheet.

The only limitation is that the cross-tab has to fit on one page in Crystal. Fortunately, all recent versions of Crystal allow the page to be as large as needed. Go into “File > Page Setup” and check the option called:

“Disassociate Formatting page size with Printer Paper size. “

Then set the height and width to whatever you need to accommodate your cross-tab.

If you have an older version of CR (before CR 2008) the option above is missing. Instead you can use a PDF virtual printer like Cute PDF, and set a custom paper size so you have enough room.




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.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server