Archive for the 'Formulas' Category



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.




The number of business hours between two DateTimes (updated)

Thursday 26 March 2020 @ 10:19 pm

A customer recently wanted to adapt my “Business Hours Between” calculation for their environment. The main challenge was that their business day ends early on Friday. My formula doesn’t currenlty support working days that are different lengths, so they paid me to redo the formula so it would work for them. Once I had their version I decided to take it a bit further and create a new “Business Hours Between” formula for my web site.

This new version lets you separately specify a business start time and business end time for each of the 7 days of the week. It also includes additional logic to deal with events that start and/or end outside the business day (e.g. on a weekend, a holiday or after hours). The formula is now about 70 lines long. Fortunately, you only need to make changes in the first 20 lines or so. Here you can specify:

  • The field to use for the BeginDateTime
  • The field to use for the EndDateTime
  • The Start and End times assigned to each day of the week that you consider a business day
  • The list of Holiday dates which can be for multiple years

The output is a numeric value in minutes which you can use in subtotals and grand totals. You can also write a separate formula to divide this value by 60 to get the value in hours as a decimal.

If you want to show the value in HH:MM format you can use the “Elapsed Time String” formula on my site to convert this value into that format. Remember to multiply this formula’s result by 60 since the input for the “Elapsed Time String” formula is seconds.

If you need help implementing this formula or any of my formulas you can always call to schedule a short consult.




Cross-tabs can total formulas that you can’t normally total

Tuesday 10 March 2020 @ 1:44 pm

One of my first 10 blog posts explained why some formulas could be totaled and others could not. Two of the things that prevent a formula from being summarized (totaled) are if the formula itself refers to a subtotal, or if it uses the functions Previous() or Next().

But I was reminded recently that both of these types of formulas can be summarized in a Cross-tab. Take these two formula examples:

// Rebate:
if Sum ({Orders.Order Amount}, {Customer.Customer Name}) > 25000
then {Orders.Order Amount} *.05
else 0

//Days Between Orders:
if {Customer.Customer Name} = Previous({Customer.Customer Name})
then {Orders.Order Date} - Previous ({Orders.Order Date})
else val({@null})

If I wanted to do a grand total of my rebates or an average of the days between orders I wouldn’t be able to use normal summary functions.   Even Crystal running total fields won’t work with these. In most cases people would resort to using variable to accumulate these totals. However, both of these formulas can be summarized using a cross-tab. You could do a simple cross-tab with a single cell to show the grand total and no row or column fields.  Or you could do breakdowns by other fields.

Not only does this save you dealing with variables, but a cross-tab can put these totals on the first page (Report Header), while variables will only be complete on the last page (Report Footer).  One more reason to use my favorite objects.




Charting based on a parameter value

Saturday 29 February 2020 @ 10:51 am

I have had several customers over the years who wanted to add a fixed line to a bar/line chart. This is typically some type of target or standard that they want to compare the actual data. If this value is in the report’s query results, then it is pretty straightforward. But what if the user wants to be prompted to enter the value to be used in the chart? Crystal won’t let you add the parameter directly to the chart. Even if you put the parameter value in a formula, Crystal might not allow that formula in the chart.

The reason is that parameters come before the data is read. This means that they are evaluated “BeforeReadingRecords” by default. Values that come before the data is being read are not eligible for grouping, sorting, totaling or charting. But if you put the parameter in a formula you can change the evaluation time of the formula to “WhileReadingRecords”. This will make it into a column of values. And even though they are all the same, they will be available in the chart expert as a value to be summarized. Something like this:

WhileReadingRecords;
{?YourParameter}

Then in your chart you can either summarize it by using a Maximum, or checking the “Do Not Summarize” check mark.

If you need to do this and have trouble then call me to schedule a short consult, or Email me your report and explain your requirement.




“New Page After” on group 2 orphans group footer 1

Tuesday 18 February 2020 @ 8:24 am

If you have two group levels and you put a “New Page After” on Group Footer 2, you will find that Group Footer 1 gets orphaned onto it’s own page. To fix this put a “New Page After” on Group Footer 1. Then instead of putting the same check mark into Group Footer 2 you use the following condition on the “New Page After” property:

{Group1.DatabaseField} = Next ({Group1.DatabaseField})

Of course you use your own database field from Group 1 in the formula. This prevents Group 2 from doing a page break on the last record of the group. On that record the next value for Group 1 is different, so the page break is handled by Group 1.




Grouping times into half hour periods

Saturday 25 January 2020 @ 9:37 pm

When you create a group in Crystal using a Date field you get grouping options for different periods like by day, week or month. If the field is a Time or DateTime you get options like by Hour, by minute or AM/PM. But there isn’t an automatic option to group on the half hour.

Below is a formula that will divide all the time values in an hour into two groups. For example, it will turn all time values between 6:00 and 6:29 into 06:00. It will, and will turn all time values between 6:30 and 6:59 into 06:30. You can use this for creating groups, charts or cross-tabs.  Note that the leading zero will keep the groups in the chronological order.

Totext (Hour({@time}),'00') &
(if Minute({@time}) < 30
then ':00'
else ':30')




Sage 50 GetPeach() functions in Windows 10

Monday 9 December 2019 @ 7:38 pm

For some reason the combination of Sage 50 (PeachTree) and Windows 10 generated many calls from customers.  Most of these calls were for reports that used the special GetPeach() functions and that stopped working.  These functions have come with Sage/PeachTree for years. I was told by several Sage consultants that these functions would no longer work in the current versions of Sage 50.  But one of my customers ended up solving the problem on his own and has allowed me to share what he found (he didn’t want to be cited).

So here is how he got these functions working again:

  1. A system PATH variable must be assigned to C:\Program Files (x86)\Sage\Peachtree. This folder contains the DDFs (data dictionary files) for the interface between Peachtree and Crystal Reports and other special functions. Without these DDFs, any effort to run a report containing GetPeach() functions will fail with the error message “The Specified Module Could Not Be Found”.
  2. In a single user environment U2LPeach.dll and the associated .ini file (U2LPeach.ini) must be included in the folder C:\Windows\Crystal. This is the default location the files are placed by Sage 50 when data functions are updated.
  3. If operating in a terminal services environment, U2LPeach.dll and the associated .ini file (U2LPeach.ini) must be included in C:\Users\\Windows\Crystal.
  4. When U2LPeach.dll is loaded correctly, the .dll will appear in the list of dlls loaded by Crystal Reports. This list can be found by going to “Help > About Crystal Reports” and clicking “more information”. Alternately you can edit any formula and look in the additional functions node for the GetPeach functions.

If you are having problems with Sage 50, and the above doesn’t help you, let me know. I have several colleagues who are Sage 50 specialists so we should be able to resolve it for you.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server