Listing all formula changes with Notepad++

Monday 14 May 2018 @ 12:09 pm

I have written several articles about the usefulness of Notepad++. I often use it to write or test complex formulas and SQL commands. I have also used it to see the affect of a logic change, by comparing the output (text) of the report before and after a change. I have even created a custom language interpreter so that Crystal formulas written in Notepad++ look better than they do in Crystal’s formula editor.

This week I found a new use that I should have seen before. A customer sent me two different versions of a complex report and we weren’t sure which one to use. We needed to see the differences between the formulas in the two reports. So I exported both reports to the format “Report Definition”.  This export creates a text file that lists all of the major settings of the report, lists the objects in each section, and includes the text of all the formula fields that are actively being used by the report.

Once I had the two Report Definitions, I opened them up in Notepad++ and used the “Compare” add-in. This took me straight to the handful of formula differences. It was easy to show these differences to the customer to see which version he wanted to use.

If you want to try out the Notepad++ with the Compare plug-in you will need to start by downloading Notepad++ version 7.4.2.  This is the latest version that includes the plug-in manager.  Once you have Notepadd++ installed you can use the plug-in manager to install the Compare plug-in.  After that you can update to the latest version of Notepad++.

Column headings for cross-tabs

Tuesday 8 May 2018 @ 9:23 pm

I recently shared a method for adding a Title to a Cross-tab that spill onto horizontal pages. One of my readers shared a similar method that he had written up for his team.  This method allows you to add column headings over the row grouping fields.  He even includes a description of how to have these headings repeat on horizontal pages.   The approach combines two separate section settings.

See this PDF for a detailed explanation of the steps.

And, thanks to Ralph Wahlert of Catholic Health Services in Long Island for sharing his approach.

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 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.

No current Crystal Reports certifications

Monday 23 April 2018 @ 11:15 pm

I have never been a fan of the SAP certifications for Crystal Reports. But if you were looking to be certified in the near future, it looks like there isn’t currently an option. I read this comment in an SAP discussion on certifications:

“Exam C_BOCR_13 [for CR 2013) is retired and no longer available and we do not have any information on an updated version.”

Once an exam is retired you can no longer take it. And if there is no ‘updated version’ then there is no way to take an exam for CR 2016, at least not through SAP. If anyone hears about an updated exam, let me know and I will update this article.

Formula to group into 2-month periods.

Saturday 14 April 2018 @ 11:06 pm

A customer asked me to group the records in a report into 2-month periods. It sounded simple but there were questions to clarify, like:

Should the 2-month period start on the even months or the odd months?
Do you label the period based on the beginning of the period or the end?

This customer decided to have six 2-month periods in each year, with the label showing the beginning date of each period. The following formula will do that if you substitute your date field in the first line:

Local DateVar x := Date ({Your.DateField});
x:= x - Day(x) +1;
If Remainder (Month(x) ,2) = 0// 0 starts periods with odd months, 1 starts with even months
then Date(DateAdd('m',-1,x)) // Outputs date at beginning of period
else x;

As it is written, this formula will crete 2-month periods for Jan-Feb, Mar-April, May-June, etc. Changing the zero at the end of line four to a one will cause each period to start with an even month instead of an odd month, so the periods will be Feb-Mar, Apr-May … Dec-Jan. I expect most people will want to start with odd months so that you don’t have periods that are part of two different years.

In the example above the date returned by the formula is the first day of the period. If you prefer to return the last day of the period you can add the following additional line at the bottom:

Date(DateAdd('m',2,x)) -1 // Changes output to date at end of period

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.

Comparison of desktop-based schedulers (2018 update)

Tuesday 27 March 2018 @ 7:41 am

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

There are 10 active products in the list this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

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.

Printing “group continues on next page”

Tuesday 13 March 2018 @ 6:03 pm

Many Crystal course books will have a lesson on printing a “Continued” message whenever a group takes up more than one page.  This message usually prints at the top of each page in the group, except the first.  But below is a different approach.  It prints “Continued on the next page” at the bottom of pages when a group is going to go beyond the current page.  There are actually several ways to do this, but the one that works in almost all situations is below.  It uses 3 formulas, and these can be cut and pasted into your report:

The first formula goes on the Group Header (and can be suppressed):

BooleanVar Continued:= True

The second formula goes on the Group Footer (and can be suppressed):

BooleanVar Continued:= False

The third formula goes on the page footer:

BooleanVar Continued;
if Continued
then "Group continues on next page"
else ""

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.

«« Previous Posts
Recrystallize Pro

The Expert Series