Archive for the 'Bugs and Errors' Category



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.




Flaw in XML Exports

Monday 22 October 2018 @ 11:06 am

I have a customer who had me create report with a complex layout and lots of optional sections. In addition to using the report itself, he wanted to export the report values into a format where they could be read by another program. We decided to use XML so that the program could search for specific field name tags and extract the matching values. When we started testing the program we noticed some discrepancies between the report values and the XML export values. It mainly had to do with variables that were accumulated in the details and then displayed in the group or report footers.

It took over an hour to identify the root of the problem. Even after fixing the problem I couldn’t explain it, so I created a very simple report to test it. It had one group, and the the following two formulas:

//Accum
WhilePrintingRecords;
NumberVar Accum;
Accum := {Customer.Customer ID}

//Display
WhilePrintingRecords;
NumberVar Accum;

I placed the Accum on the details band and hid that section. I placed the Display formula on a group footer. This exported to XML and showed all the Group Footer values correctly.

Then I split the detail band into A and B subsections. Both subsections were still hidden. The preview of the report looked the same, but when I exported to XML all the group footer values in the XML were zero. It didn’t matter if I put the Accum formula in Details A or Details B. Whenever the Details section was both SPLIT and HIDDEN, the Accum formula would increment correctly in preview but NOT for the XML.  If I used “suppress” instead of “hide” the Group Footer values exported correctly to XML. But even though the details did not appear in preview, they would now be included in the XML.

This behavior looks like a bug to me.  That means there may be other situations where variables don’t behave correctly in XML. So, if you are going to use XML exports, and you are using variables in your report, you need to test the output carefully to confirm that the variables export correctly.




Numbers that touch operator words

Friday 21 September 2018 @ 2:36 pm

I was shown an unusual formula today. It looked something like this:

if {Customer.Customer ID} = 14then 30else 0

Note the space missing between the numbers and the words THEN and ELSE. These two words didn’t turn blue so I figured they weren’t being recognized and would generate an error. Surprisingly, the formula saved without error and the number 30 showed up in the correct places. The reserved words were still being recognized correctly, even without the spaces.

I did some testing and found that any formula where a number is usually followed by a space and then an operator word will work the same without the space. I tested this in some older versions of Crystal and it worked the same at least as far back as Crystal v8.5 (2001).

The only reserved words I can think of that can follow a number are these operators: AND, OR, IN, TO, THEN, ELSE, MOD and DO.  These all behave the same way.  I can’t think of any functions that can directly follow a number. A space is still required if the number follows these words.

So if you ever find a formula like this and wonder how Crystal is handling the error, now you know.




Service Pack 3 for Crystal Reports 2016

Friday 7 September 2018 @ 11:39 am

I don’t usually rush to apply service packs (i.e. ‘if it ain’t broke…’). Usually the fixed items don’t apply to what I do. So when SAP notified me about SP3 for CR 2016 I was ready to ignore it for a while. But I read through the list of fixes anyway, just in case, and the second to last item was something useful. It corrects a scrolling bug in the formula editor so we can now use the mouse wheel to scroll up and down in long formulas. I decided to do the update right away since I write lots of long formulas.

But the update was a hassle. It starts with 5 minutes of extracting files and then immediately pops up a “Fatal” error:

“Fatal Error: At least one port in the range 4520-4539 must be opened for the installation to proceed. …”

So why would a service pack for a locally installed app require access to ports? After a few searches I found that this is a red herring error message. It also occurs on some full Crystal Reports installs. The keys to getting past this error on a full install are to:

1) Run the setup from the Root folder
2) Use “Run as an administrator”.

But the service pack is a self extracting compressed exe. Placing the exe in C:\ and running it as administrator didn’t solve the problem for me. What worked for me in both Win 10 and Win 8.1 was to:

1) Use an Unzip utility (e.g. 7zip) to extract the compressed files/folders into a new folder.
2) Put this new folder in C:\.
3) Run the Setup.exe in this folder as an administrator.

Even then there was another snag.  The install couldn’t finish because of a “suppressed reboot”:

“A reboot request was suppressed on the local machine.
Reboot the machine in order to proceed with the installation”

But it wasn’t clear how to do this. My only options were “retry” and “cancel”.  So I cancelled, did a manual reboot and then ran the setup.exe a second time.  That allowed the SP to install and now I can scroll through my formulas.  It was just much more work than I expected.

Update 10/2/2018:

One of my readers just pointed out their own blog post about installing CR 2016 and having the same exact issues.   Had I seen that post when writing this one my post would have been a link to his.




Using a “display string” turns off HTML interpretation

Sunday 29 April 2018 @ 11:59 pm

A customer was deploying my calendar report for multi-day events when he noticed an issue in CR that I had never noticed. This particular calendar report takes advantage of several less commonly used features of CR.  It appears that two of those don’t play nicely together:
1) The Display String property (overrides the current value and displays something else)
2) HTML Interpretation (applies any HTML formatting codes found within the field value).

The customer told me that if he tried to change any of the formatting properties he would lose the HTML interpretation. Also, the entire paragraph tab would disappear so that he could not re-activate HTML interpretation. So I did some testing and found the following related to these two properties:

1) Whenever you format an object and add or change the display string formula (“common” tab) Crystal will reset the HTML interpretation property back to ‘none’. It will also no longer show you the “paragraph” tab which prevents you from re-activating HTML interpretation.

2) You can comment out the display string and save it. This brings back the paragraph tab. You can then re-activate HTML interpretation. Finally, you can go back to the Display String formula and take out the comment marks. This will give you both features at the same time.

3) But the next time you then change ANY formatting property (other than adding or deleting the comment marks in the display string) Crystal will again reset the HTML interpretation back to ‘none’. You have to make your formatting changes, save them, then go back in and comment out the display string so you can re-activate the HTML interpretation.

4) If you comment out the display string formula and save it, but then switch to the paragraph tab without clicking the OK to exit the “Format Field” dialogue, the paragraph tab is not only reset but it won’t even display default values. Where you normally see zeros the boxes will be blank. And the radio buttons for “Reading Order” will both be blank. However if you click OK, and then use Format > Field to get back to the paragraph tab, it will show all of the default values.

5) If you select multiple fields, and use “format object” to format them all together, the behaviors described above do not appear.  You can still see the ‘paragraph’ tab even after changing the display string. The HTML interpretation doesn’t get reset.  Thanks to Guillaume Boucher of W3COM.fr for pointing this out to me.  This explains why I was able to develop all 3 calendar report variations and never notice this issue.

Very strange.




New SQL Server OLEDB provider supports TLS 1.2

Saturday 7 April 2018 @ 4:30 pm

If your organization handles sensitive information on the web (Credit Card info, HealthCare info, etc) you are probably using the latest TLS protocol (1.2). And if you are connecting to MS SQL Server through OLEDB, you may have had trouble connecting recently. This is because the SQLOLEDB provider and the SQL Server ODBC driver are no longer supported in TLS 1.2.

At the end of March, Microsoft released a new OLEDB Driver (MSOLEDBSQL) that does support TLS 1.2.

Thanks to Lyle Hardin of Foslyn LLC for sharing this info and links.




Trouble with the suppress condition for a line

Thursday 8 March 2018 @ 5:50 pm

I had a bit of a mystery this past week. A customer wanted to suppress a series of vertical lines in the page header, based on a condition (a field being null). SAP just added a suppress condition button for lines and boxes in the latest version of CR, CR 2016, so I figured it would be simple.

But when I put the condition formula in for one of the lines, it did not suppress when it should. I applied the same condition to the next vertical line and it worked as expected, but the first line did not. If I deleted and redrew the line it worked fine, but nothing I could do would get the original line to work.

So I spent a few minutes trying to figure out what was different about the line that didn’t work. I found that while the top end of this line looked like it was in the page header, it was actually a hair higher in the report header. The report header was suppressed.

Normally, suppressing or hiding a section has no affect on formulas. It does affect some other things like subreports and cross-tabs. A hidden/suppressed section will prevent subreports in that section from running. To get a shared variable from a subreport without seeing the subreport requires that we use a ‘stealth’ subreport.  Also, a hidden/suppressed section will prevent a cross-tab from doing any of it’s calculations. But formulas are often put in suppressed sections and they are not normally affected. The suppress formula for a line or a box is an exception, apparently.

So, if you enter a condition formula to suppress a line or a box, and if that line/box has it’s top in a suppressed or hidden section, then the suppress condition will be ignored.  This isn’t true at the bottom of the line/box, only at the top.

One more mystery solved.




How to fix the error “A loop was evaluated more than the maximum number of times allowed”

Sunday 17 December 2017 @ 11:17 am

A customer recently asked for help with a formula. She was creating an index at the end of a report to show page numbers for each item within the report. Her formula used nested FOR loops to put the elements of the array in alphabetical order. It was similar to this handy formula that I was given once to do a “bubble sort”:

WhilePrintingRecords;
stringvar array MyValues;
numbervar i;
numbervar j;
stringvar temp;
FOR i:=1 to ubound(MyValues)-1 DO (
FOR j:=1 to ubound(MyValues)-i DO (
if MyValues[j] > MyValues[j+1] then (
temp := MyValues[j];
MyValues[j] := MyValues[j+1];
MyValues[j+1] := temp;
)));
join(MyValues,",")

The problem we had was that the formula would work fine for small arrays, but would generate an error on a larger array. The error would say:

“A loop was evaluated more than the maximum number of times allowed”

The maximum number of loops in a single formula evaluation is 100K, so usually this error means that you wrote an infinite loop. But in this case it was legitimate.  Because an array can be up to 1000 elements, and we are doing one loop within another, this formula could theoretically generate up to one million loops. A correctly done bubble sort for 1000 elements should max out at about 500K, well over the limit.

To stay under the limit on longer reports, we separated the two loops. With the formula above we wait until the end of the report and do the sorting all at once. Instead I added a sorting loop to the formula that adds each element to the array. This way we sort the array as the elements are added. And because this formula evaluates each element separately, the maximum number of loops at one evaluation would be just the size of the array.  Problem solved.

But in doing my research on this I learned something new.  I learned that 100K loop limit isn’t a hard limit. You can override this limit to make it both smaller and larger as needed. All you have to do is start the formula with a line like this:

option loop 200000;

This would double the limit from the default of 100k to 200K, making more nested loops possible.  Crystal let me set the number at one trillion without complaining, although I didn’t actually try to create a loop that ran that many times. I still prefer to avoid nesting loops when possible, but this might solve a problem some day.




Microsoft updates break spreadsheet driver (and a possible workaround)

Tuesday 17 October 2017 @ 10:26 pm

Two customers contacted me in the past few days about the following Microsoft updates.

  • KB4041676 for Windows 10
  • KB4041681 for Windows 7/8

These updates broke reports that use the ‘classic’ Microsoft JET drivers to read ‘classic’ xls spreadsheets. ‘Classic’ here is a euphemism for ‘ancient but still working’, like my workhorse Office XP from 2002.

Today I got a similar error when I tried to help a customer import a spreadsheet(xls) into an MS Access database (mdb). MS Access failed with the following error:

“The wizard is unable to access information in the file….”

So I tried to read the same spreadsheet from Crystal Reports using the DAO connection method, and got this error:

“Unexpected error from external database driver (1)”

I checked and found that KB4041681 had been installed on that workstation.

The official Microsoft temporary solution is for us to use a different “OLEDB provider” in place of JET, but that only works if you can use OLEDB and/or  choose your provider.

So I did some research and found a post from 2008 where someone was getting this error message. They asked why they could only read xls files when those files were already open in Excel. If the file wasn’t open in Excel he would get the same errors I mentioned above.  I figured it couldn’t hurt to do a quick test so I opened the file in Excel and then tried to read it from MS Access.  It worked for MS Access imports and also worked for Crystal Report connections. Both would work as long as the xls file was open in Excel. Both would fail with the errors above when the file wasn’t already open in Excel.

This isn’t a great solution, and hopefully Microsoft will fix the issue in the near future. But in the meantime this workaround might just help someone meet a deadline. If you try this, let me know if it works in your environment.

Update 10/19/2017

One of my readers pointed me to the dll that causes this error and how to roll back just that one dll.  I now see that this is one of several solutions found in the MSDN discussions.  See the links below for more information:

MSDN blog article: Unexpected error from external database driver

MSDN forum thread: ODBC Excel Driver Stopped Working…




«« Previous Posts
Jeff-Net

Recrystallize Pro

The Expert Series