Archive for the 'Formulas' Category



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.




Free lesson in Cross-tab grid functions

Wednesday 8 July 2020 @ 8:57 pm

I just got a note from Bruce Ferguson, the developer of the Crystal Kiwi line of viewers and schedulers. He sent me a chapter from his Crystal Reports course materials dealing with the the “new” grid functions availabe for cross-tabs. He said I could share it with my readers.

Bruce and I have been corresponding for nearly 20 years and we were both working with Crystal Reports for several years before that. So to us, a feature that has been around only 12 years or so counts as a new feature.

So, if you want to learn how to do Cross-tab calculations that reference other cells in the cross-tab, you can download the 8-page PDF and give the lessons and excerises a try.

And thanks, Bruce.




Fun with looping logic

Wednesday 17 June 2020 @ 11:59 pm

I had a fun challenge today. A customer had a table with an odd structure. One column was a payment amount. It was followed by 40 different columns for various fees. He wanted the payment amount to be applied to the fees from smallest amount to largest amount until the payment was used up. The report was to show the balance for each fee after the payment was applied.

Normally this type of data would be vertical. There would be a separate row for each fee and only 2 columns (fee name and the fee amount). With that structure you could sort by fee amount and use a variables to apply the payment to the records in ascending order. But with 40 fixed columns I had to load the values into an array, and then put the values in the right order.

I loaded the fees into the array as strings, combining the amounts and fee names into one element looked this:

24.50=xyzFee

This allowed the fee name to follow the amount through the sorting process.

Once I loaded all these strings into the array I used my bubble sort formula to re-sort the array from lowest amount to highest amount. In the bubble sort comparison I used the Val() function to convert the strings to numbers. This put them in order by their true numeric value.

Then I wrote a second loop to apply the payment. It steps through the array, which is now in the right order, and applies the payment to the fees, one at a time. Each fee amount is reduced to zero while the payment value is reduced by the amount of each fee.  If  the payment’s remaining value drops below the amount of the next fee the remainder is subtracted from that fee and the payment is reduced to zero. Any fee amounts in the array beyond that point stay the same.

Last, to display the results, I wrote 40 separate formulas, one for each fee. Each of these formulas loops through the array looking for its specific fee description. When it finds the element with a matching description it uses the Val() function to convert that string to a numeric value and displays that value.  This is the relatively simple loop formula:

EvaluateAfter({@Build Fees Array});
stringVar Array Fees;
Local numberVar i;
Local numberVar fee;
FOR i:=1 to ubound(Fees)
DO (
if 'xyzFee' in Fees[i]
then Fee := val(Fees[i])
);
Fee

Not many people would think this process was fun, but I did enjoy mapping out a creative approach to a unique requirement. And it was less than 2 hours from the requirements to a validated report. The next time your report requirements are a bit unorthodox, keep  me in mind.




Giving the database what it needs

Monday 8 June 2020 @ 8:35 pm

Today a user asked me how to automatically select the prior month’s transactions from the database. The transactions didn’t have dates, but instead had two numeric fields for Period (month) and Year. They were using a formula to convert these two numbers into a date and then using that formula as part of the selection formula. That approach will work but it is usually slow. This is because the database (or the SQL Query) doesn’t know anything about the date formula in the report, so it can’t apply any date criteria before sending back the data.  Crystal gets all years and periods and then has to apply the criteria in the report.

A more efficient approach is to start at the other end. Instead of converting the DB fields to match the criteria values, we convert the criteria values to match the the database fields and let the database do the heavy lifting. In this case created three formulas:

// Prior Pd End Date
Maximum (LastFullMonth)

This gives us the last date of the prior month. From there I created two formulas that extract the year and the month from that date:

// Prior Pd Month
Month ({@Prior Pd End Date})

// Prior Pd Year
Year ({@Prior Pd End Date})

Then the selection formula can use these values to filter the DB fields, something like this:

….
and {Trans.Year} = {@Prior Pd Year}
and {Trans.Period} = {@Prior Pd Month}

This should allow the criteria to be passed to the WHERE clause of the SQL, and maybe even hit an index to make short work of finding the correct records.

The principle works the same if you are using a Date Range parameter.  You could use formulas to convert the parameter values so they match the database fields.  I wrote about this a few years ago in a similar case where the dates were stored as strings.




Highlighting a phrase within a larger field

Wednesday 27 May 2020 @ 1:58 pm

One of my readers shared a solution he recently found for highlighting a specific string within a larger field (like a comment, notes or memo field). To provide an example, I wrote a report against the Xtreme database and highlighted the word “graduate” the first time it occurs in the employee notes field.

There are two parts to his approach. First you write 3 formulas that split the memo field into three separate pieces: the target string, everything before it, and everything after it. Then you reassemble these three pieces by dropping them into a single text object. The target string formula can then be formatted separately so that it stands out within the text.

I used 3 additional formulas to make the process more generic. Here are the formulas I used:

//Target String - replace with your own target
"graduate"

//Target Start - replace Notes field with your Notes field
Instr ({Employee.Notes}, {@Target String})

//Target End
if {@Target Start} = 0
then 1
else {@Target Start} + Length ({@Target String}) -1

//Output Before- replace Notes field with your Notes field
if {@Target Start} <= 1
then ""
else {Employee.Notes} [1 to {@Target Start}-1]

//Output Target - replace Notes field with your Notes field
if {@Target Start} = 0
then ""
else {Employee.Notes} [{@Target Start} to {@Target End}]

//Output After - replace Notes field with your Notes field
{Employee.Notes} [{@Target End}+1 to -1]

The 3 “Output” formulas are dropped into the text object. Make sure the text object is set to “Can Grow”.

The process can be made more sophisticated if the target string varies in length, as long as you can clearly identify the character pattern that marks the beginning and the end of the target string. If you need help with that let me know and we can schedule a short session.

There is also an alternate approach where you embed HTML tags before and after the target string and then use Crystal’s HTML interpretation to change the format at runtime. However, this is limited to the HTML tags that Crystal supports.

Finally, thanks to Doug Weiner at Beacon Legal Software Services for suggesting this post.




Using the Picture() function

Sunday 12 April 2020 @ 8:37 pm

One of my former students sent me a question. He wanted to know the easiest way to add the dashes back into a Social Security number when it is stored as a 9 digit string. I was going to have him parse it into 3 pieces using substring brackets and then assemble those around the dashes. Then I remembered the picture() function. I don’t use it much, but it has been on my potential blog topic list for a while.

The picture() function allows you to insert characters and punctuation into a string so that it has a specific format. The two most common uses I have seen are adding the parentheses and dash back to the US Phone number: “(000)000-0000” or adding the dashes back to a SSN: “000-00-0000”. It will work with any consistent pattern.

To use the picture function you give it two arguments. The first is the field or string you want to format and the second is a pattern string. The pattern string has an ‘x’ for every character in the field. You then insert the other characters among the ‘x’s at the appropriate places. Crystal will insert those characters automatically at those positiions.

The picture function for the Phone Number would look like this:

Picture({table.phone}, '(xxx)xxx-xxxx')

The picture function for the SSN would look like this:

Picture( {table.SocSec} , 'xxx-xx-xxxx')

Note, that if the field is empty or short the formula will still show all of the inserted characters.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server