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.





Selection based on two different records in the same group

Sunday 16 September 2018 @ 8:38 am

I was visiting Tek-Tips.com today and found an interesting question to answer. A user was having trouble counting sales orders that had items with a certain comment. He wanted to group and count the occurrences by the item ordered. It sounded simple, but the item number was in an “item” record while the comment was on a separate “comment” record. When he filtered by the comment there would be no item numbers and if he grouped by item number the comments would be in a different group. He needed to get the two records into one record.

To solve a problem like this you need to add the sales order table to the report two separate times to create what is called a “self-referencing join”. The second time you add the table, Crystal will give the table a slightly different name (an alias) usually by adding _1 to the end of the table name. Then you treat the two instances as if they were two separate tables.  In this case you could think of the first table as the “item” instance and the second as the “comment” instance.  You link them based on the Sales Order and then add two filters, something like this:

{SalesOrder.LineType} = "Item" and
{SalesOrder_1.NComment} = "Your specific comment"

Note that the two rules above reference one field from each of the two table instances, bringing the two separate records together as one. From there you can group by item number (from the first table) and count the number of records within each group.  Here is a link to the tek-tips question which has a screen shot of the data.

Update 10/3/2018:

Doug Weiner at Beacon Legal Software reminded me to mention that you can always rename (change the alias) of any table used in a report.  Just go to the database expert and click on the table name, then right-click to get ‘rename’ or hit F2 on the keyboard.  You will be allowed to type any alias you want for that table.  This is especially helpful when you use the same table twice, so you can assign names that help you remember which instance is which.  These names are just for the report and don’t affect anything in the database.





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.





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.





Crystal Reports versions over time (2018)

Sunday 12 August 2018 @ 9:41 am

I do this chart each year based on my newsletter subscribers. It shows the version of Crystal Reports that they were using when they signed up for the newsletter. Over time, it shows which versions have staying power and how quickly new versions take hold. I have included numbers for 2018, even though the year is only about two thirds over. Those numbers will shift a bit in next year’s chart when 2018 is complete.

The chart shows that over 25% of my 2018 subscribers are still using Crystal Reports XI (circa 2005) which is version 11.x. Last year I was concerned that some of these users might really be on CR 2011 (version 14.o) since those do get confused.  But when I Emailed some of those people they all said that they were using the old XI version. For some of them, upgrading past Crystal Reports XI would be a major undertaking, because all the newer versions are limited to the .NET runtime. Customers that have built an application around the older runtime engine would have to rebuild the Crystal components of that app in order to upgrade to a later version of CR.

Version of Crystal Reports used by my new subscribers each year





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.





RPT management utilities for 2018

Friday 27 July 2018 @ 12:57 pm

I have just updated my comparison of RPT management utilities for 2018. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list includes 9 tools:

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source Updater by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
.rpt Inspector 3 Professional Suite by Software Forces, LLC
.rpt Inspector Online by Software Forces, LLC (new)





Cloaking Groups

Tuesday 24 July 2018 @ 7:49 pm

There are times when you want a group level to be optional. Maybe you want to give the user a choice between having 1 group level or 2 group levels in a report. While it is easy to suppress Group Headers and Group Footers for group 2, this doesn’t make the group level go away. Group 2 will still put the records in order, and will take priority over any sorting you have specified. A group can’t be removed based on a parameter, but you can have the same effect by ‘cloaking’ the group. Even though it is still there, it has no effect on the report.

To cloak a group you first create a parameter with 2 choices. For example, our user has a parameter called {?Group choice} that allows them to select the number of group levels:

Group by State only
Group by State and City

You set Group 1 to use the field “state”. Group 2 is set to use a formula that says:

If {?Group choice} = “Group by State only”
then “All”
else {table.City}

So, if the user picks “Group by State and City”, then the second group will be the field “city”. But, if the user picks “Group by State only”, then Group 2 will be one big group. Since all the records in Group 2 have the same value, the details will sort as if there were only one group.

So what do you do with the Group 2 Header and Footer? You suppress these sections with a suppress condition that says:

{?Group choice} = “Group by State only”

Now the Group 2 has no effect on the sort, and it is invisible.

A more complex example is when the user wants to see the Top 10 customers but wants to choose if the Top 10 is based on the Average or the Sum of the amount. There is no option for this in the Group Sort Expert. The workaround is to create a parameter so the user can choose either “Sum” or “Average”. Then create two cloaking formulas like the one above, using the Customer field and opposite IF conditions. Group on both fields and set the Group Sort for one group to the average and the other to the sum. Add suppression logic to the group headers and footers to completely cloak one group or the other.





First day of the calendar quarter

Wednesday 18 July 2018 @ 9:53 pm

Finding the the beginning date for most date periods is simple. For example:
First day of this Year: Minimum (YearToDate)
First day of this month: Minimum (MonthToDate)
First day of Last month: Minimum (LastFullMonth)
First day of this week: Minimum (WeekToDate)

But there isn’t a comparably simple way to get the first day of the current calendar quarter. So if you ever need that calculation you can use the following formula:

Local numberVar x := Month (CurrentDate);
Date (
Year(CurrentDate), //Year
if x > 9 then 10 else
if x > 6 then 7 else
if x > 3 then 4 else 1, //Month
1 //Day
)

If you want the beginning of the quarter related to a field other than today’s date you can substitute a database field, formula or parameter in the two places that say “CurrentDate”.





Next Posts »» «« Previous Posts
Jeff-Net
Recrystallize Pro

The Expert Series