Archive for the 'Formulas' Category



“Convert Null to Default” breaks the selection formula

Monday 18 January 2021 @ 1:15 am

Recent versions of Crystal allow you to automatically “Convert Nulls” in specific formulas. The setting is on the formula editor tool bar as a drop down.  You can change “Exceptions for Nulls” to “Convert Null Values to Default values”. Crystal then automatically replaces any null values encountered in the formula with an empty string [""]. This prevents your formulas from returning a blank whenever they encounter a null value.

This works great for formula fields, but not so great in the record selection formula. This problem is that this setting doesn’t get converted into SQL.

Let me give a simple example from the Xtreme sample data. There are 10 records in the customer table that have a null value for Postal Code. If I write a formula field that says:

if IsNull ({Customer.Postal Code})
then "Unknown"
else {Customer.Postal Code}

It will show TRUE for these 10 records. I could also write a formula that says:

if {Customer.Postal Code} = ""
then "Unknown"
else {Customer.Postal Code}

These 10 records will show blank at first, but if I set that formula to “Convert Null to Default”. Then it will also show TRUE for those 10 records. Now lets say I use this as my selection formula:

IsNull ({Customer.Postal Code})

That will pass to the WHERE clause as:

`Customer`.`Postal Code` IS NULL

and it will select those 10 records. But, if I write the selection formula like this:

{Customer.Postal Code} = ""

Even if I tell the selection formula to “Convert Null to Default”, the WHERE clause will be:

`Customer`.`Postal Code` = ''

There are no records that meet that criteria and so no records will be selected.  The key here is that using “Convert Null to Default” works within Crystal, but is not translated to SQL. That is why I avoid using this setting in the record selection formula.




IF THEN vs SELECT CASE

Thursday 31 December 2020 @ 2:39 pm

I had a customer today who wanted to write a formula that assigned a numeric value to each Employee. Normally I would write an IF THEN statement like this:

if {table.EmpNO} = 123 then 10 else
if {table.EmpNO} = 124 then 15 else ...

But the list of employees and values was in a spreadsheet with 4000 employee rows. A linked table wasn’t an option so I decided to do something I don’t do very often. I wrote the formula using SELECT CASE instead of IF THEN.

Normally I prefer to use IF THEN because you can use more complex conditions.  I also find that IF THEN reads more naturally. But in cases like this where you have a single field and LOTS of comparison values, the SELECT CASE pattern is more efficient. The pattern below reduced the total character count by 70% of an equivalent IF THEN structure:

Select {table.EmpNO}
case 123:10
case 124:15
...
default : 0

The first time I used SELECT CASE was when I had to translate a large spreadsheet grid into a formula. I wanted the formula structure to look like the spreadsheet so we could visually check the formula by comparing the columns. The spreadsheet grid had 30 rows (Types) and 20 columns (Sizes) so my formula ended up with 30 rows that looked like those below, except that each row had 16 more case instances where you see the ellipsis […]:

if {prod.Type}=1 then(select {prod.Size} case 01:03 case 02:03 case 03:05 case 04:06 ... default:00) else
if {prod.Type}=2 then(select {prod.Size} case 01:02 case 02:03 case 03:04 case 04:05 ... default:00) else

Note that I combined an IF THEN to determine the row (Type) with a SELECT CASE to determine the value for each column(Size).  I found this more manageable and easier to validate than 600 additional IF THEN loops.




One more note about Group Selection

Tuesday 10 November 2020 @ 11:16 pm

My last post talked about Group Selection and showed off some quirks. For instance adding a Group Selection criteria might cut your report in half – but the record count stays the same. So I was curios to find out how the functions Previous() and Next() are affected by Group Selection.

First, what do these functions do? Normally, when you refer to a field in a formula the values for that field will come from the current record. These two functions allow you to refer to the record before or after the current record. So, if I wanted to know the number of days between one order and the next I could write the following formula:

{Orders.Order Date} - Previous ({Orders.Order Date})

This would tell me the number of days between the two dates. But what if you are using Group Selection. Do these functions use all the records from Record Selection, or just the ones that meet the Group Selection criteria?

Say I wanted to limit a report to the records that were above average. I could calculate the average order amount for the entire report then use that total in the group selection formula like this:

{Orders.Order Amount} > Average ({Orders.Order Amount})

Adding this rule would cause about half of the orders to be eliminated from the report. But as we mentioned above those records are still in memory. They need to be because they are used to determine the overall average. So what happens if you write a formula like the one above that refers to the previous order date? Will it see all records, like the record count does, or only ones that meet the Group Selection criteria?

My gut told me that this formula would include records that didn’t meet the Group Selection criteria, but I was surprised. The Previous() and Next() functions are apparently evaluated AFTER Group Selection. So the formula above will always pull values from the records that meet the Group Selection Criteria.




Quirks of Group Selection

Saturday 31 October 2020 @ 7:48 pm

When you add a filter to most reports you create a rule that applies to each record and determines if that record qualifies or not. Examples would be:

{table.state} = 'PA'  and  {table.Amount} > 50

This is called record selection and is stored in the Record Selection Formula. You might enter the rules using the Select Expert, but Crystal will convert those rules into the Record Selection Formula.

But sometimes you need to add criteria that applies to an entire group based on a group subtotal. For instance you might want to limit the report to states where the total amount for the state is over $5,000. This is called group selection and these rules are stored in the Group Selection Formula. The rule I just mentioned would look like this:

Sum ( {table.Amount} , {table.state}) > 5000

When the subtotal already exists on the report you can create the rule using the the select expert. You would choose the “Group” option instead of the “Record” option of the select expert. Crystal will add the rule to the Group Selection Formula.  Any type of summary operation can be used for Group Selection (Sum, Count, Average, Minimum, Maximum, etc).

Just keep in mind that using Group Selection comes adds a few quirks to your report.

1) Adding a group selection rule will shorten the report but the record count (shown in the status bar) won’t change. That is because the record count is determined by Record Selection before Crystal applies the Group Selection Formula.

2) Groups that don’t meet the Group Selection rules are eliminated from the report but will still show up in the group tree. The group tree is created before the Group Selection Formula is applied. So clicking some values in the group tree will not take you to that group.

3) Grand total summary fields will not be reduced by Group Selection. The Grand totals are created before Group Selection. This issue can be solved by re-creating your grand totals as Running Total Fields rather than as regular summary fields. Running Total Fields are evaluated AFTER Group Selection so they will be lower than the original grand totals.




When a reinstall won’t fix the formula editor

Thursday 22 October 2020 @ 8:04 pm

I took another dip into the registry today. I had to fix the formula editor toolbars for a customer (see below).

formula editor error

Two sections would not dock and the field tree on the left could not be made visible. We tried the field tree icon and right-clicks to allow docking. Nothing worked. I tried using the Toolbar Reset check mark in View > Toolbars. That didn’t work either. He even tried uninstalling and reinstalling Crystal, which also didn’t work.

But I remembered from my last post that the toolbars have lots of registry keys which store their settings and positions and I figured these were corrupted. And since re-installing didn’t solve things that meant the bad registry keys weren’t being replaced by the install. So we did another uninstall and then we went into the registry. Most of the Crystal registry entries had survived the uninstall. I was tempted to delete just the node for the formula editor, but we decided to play it safe and delete the entire node named “Crystal Reports”. It is found in this path:

Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\

We exported this node to a .reg file just in case we needed to restore it and then deleted it.  When we reinstalled Crystal Reports the formula editor went back to normal.

Today I did some more testing because I wasn’t sure if we needed to do the full uninstall.   I closed Crystal, then I went into the registry and exported the “Formula Editor” node. This is found under the “Crystal Reports” node mentioned above.  Once it was exported I deleted the “Formula Editor” node.  I then went into Crystal Reports and worked in the formula editor.  When I checked the registry the “Formula Editor” node had been recreated.  It wasn’t fully populated but it was there.  I went in and did a SQL expression and a custom function and this created more of the keys for that node.  In other words, it may not require a complete re-install to fix this part of the registry.  Crystal appears to create these keys, as needed, when they don’t exist. So if you have windows or toolbars that won’t behave, you can try this approach first.




Fields that change format when moved to a different environment

Monday 21 September 2020 @ 8:52 pm

I recently had complaint from a customer that a numeric field was changing format when moved from one PC to another. The format was fine when run on the developers PC but on the user’s PCs the format would change. I have seen this happen with both numeric and date fields.

The source of the problem is the format option at the top of the list called “System Default”.  If you leave a field formatted with this choice the report will use whatever format is set in the regional setting of the PC where the report is being run. Of course this means that a report might look different when run on another PC. If you don’t want the format to change from one PC to another then you should identify the specific format you want for numeric, currency, date and datetime fields.

Using a specific format should solve this 99% of the time. I have only seen a few instances where a report moved to another environment would have formatting changes despite the format being specified in the report. In those cases the final workaround is to write a formula to display the value. You can use Totext() to convert the field into text that is formatted the way you want. You use the text version for display purposes while using the original field for other functions like sorting and totaling.

Here are some examples:
Totext ({Date.Field}, 'yyyy-MM-dd') //converts a date into a string like 2020-09-21
Totext ({Number.field}, 0,"") // converts a number into a string with no decimals or commas




Get distinct count subtotals to add up to the grand total

Tuesday 8 September 2020 @ 11:36 pm

I just had a customer ask me about their distinct count grand total and why it didn’t match the sum of the subtotals. This is common. When you add up distinct counts at the group level they often won’t add up to the grand total. This is because of what a distinct count is designed to do.

A distinct count summary will count how many different values there are in a column. So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level. And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer. But it is very likely that this grand total is not going to match the sum of the doctor subtotals. This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.

But there are special cases where the grand total needs to be the sum of the subtotals. If you find yourself with this requirement the solution is simple. You create a formula that concatenates the group value with the field used in the distinct count. (In the example above you would combine the doctor with the patient.) Then you do a distinct count of that formula. This will give you the same values at the group level, but it will prevent the grand total from combining values from different groups.

This technique is article #25 in my Expert Techniques volume 1.




Converting String and Numeric Dates to DateValues (part 2)

Sunday 16 August 2020 @ 10:02 pm

Last week I wrote about using IsDate() and DateValue() to convert common date strings into true date values. This week I am using that same method to update some formulas on my formulas page. The advantage of this method over the original formulas is the extra validation provided by the IsDate() function.

But there are one or two extra steps needed to use DateValue() to replace the original logic in Formula #2 and Formula #3. The DateValue() function needs separators like dashes or slashes. When character strings have no separators you need to add them using the Picture() function. If the field is a numeric, you need to convert the field into a string first, and then use Picture() to add the separators.

If the field we are converting is a string with values like this: 20201231

Then the formula would look like this:

Local stringvar z := {Table.TextDate};
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)

But if the value looks the same but is stored as an 8-digit numeric, then we need to add an extra step:

Local NumberVar y := {Table.NumericDate};
Local StringVar z := Totext (y,0,"");
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)




Converting a character date to a true date

Monday 10 August 2020 @ 11:50 pm

I run into many situations where dates are stored as numbers or strings. These need to be converted to true date values before they can be used in date calculations. I have posted formulas on my site that convert common numeric and string values into true dates.

One of the challenges when converting dates is dealing with invalid values. These will generate an error when you attempt to convert them into dates. One way to validate a string input is with the IsDate() function. This checks and see if a string value can be converted into a date before doing the conversion. It is usually partnered with the DateValue() function which converts any valid date string into a true date value. The formula would look like this:

If IsDate ({String.Field})
then DateValue({String.Field})

I did some experimenting with this today and I was surprised at how many different patterns can be converted using these functions. The general rule is that the string needs 2 or 3 separate parts in one of these 5 patterns:

Month-Day-Year
Year-Month-Day
Day-Month-Year
Month-Day (assigns current year)
Month-Year (assigns day as 1)

The year can be 2 or 4 digits, except for Month-Year pattern which MUST have a 4 digit year.
(2 digit years >= 50 are assumed to be 19xx, while those < 50 are assumed to be 20xx)
The month can be a number, the full month name or the 3-character abbreviation.
The separators can be a slash, a dash, a space or a comma (or any combination).

This gives you over 400 different patterns that can be directly converted into a date. Here are just a few common string examples that will all return the date value 12/1/2020 once converted:

12/1/2020
December 1, 2020
2020-Dec-1
1-12-2020
Dec 2020
12/1

For my next post I am going to redo formulas #2 and formula #3 on my formulas page so they use this method.




Objects highlighted, but only in design view

Saturday 25 July 2020 @ 6:16 pm

I like to highlight key objects in my reports. For instance I highlight small subreports that might be confused with regular fields, or that might not be noticed at all. I also highlight formulas that involve variables as a reminder that they shouldn’t be deleted or moved. (Usually a specific location is required for variables to work correctly.)  I will sometimes highlight several formulas with the same color so that I can quickly see which formulas work together. This is simple when the formulas are suppressed or when they are in a suppressed section, but sometimes the object is visible. In that case the object highlighting should only be visible in design mode and not in preview mode. Here’s how you do that.

Most Crystal formatting properties have a formula condition button [x+2] to the right of the normal controls (check mark, drop down, etc).  This allows you control that property based on parameter values or data values. When you use a condition formula there is no need to set that same property using the normal controls.  If you set a formatting property both ways the normal control setting will apply to design view while the formula will apply to preview.

So say I would like a field to have an aqua background in design view but no color in preview. I would first format that field by selecting aqua from the drop down. Then I would go into the condition formula and type: CrNoColor

The field will have an aqua background color in design view but no background color in preview.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server