Crystal Dispatch site license for $5

Tuesday 30 June 2020 @ 12:03 am

I have written before about Crystal Dispatch, a client based viewer put out by APB Reports. It is a simple no frills viewer that covers all the basics and lets you run reports with one click. You can read more about how it works in my previous article, and even watch a video of how it works.

But what is new this year is the price. APB Reports is now offering an unlimited site license to a company for $5 (yes, five US dollars).  Adam Butt, the owner of APB Reports, said, “I want as many companies as possible to start using Crystal Reports, so hopefully this is a move in the right direction”.

So if you are thinking about deploying a locally installed viewer program in your company, this is one very low cost option to consider. For other options you can check out my annual comparison of client based viewers.





Are you ready for Crystal Reports 2020?

Thursday 18 June 2020 @ 4:13 pm

And now for some breaking news. It appears that SAP is starting to release Crystal Reports 2020 and Crystal Reports Server 2020.  Based on things I have heard the scheduled release of CR 2020 as packaged product is Early August.  I haven’t seen the software, but I read an article about Crystal Reports 2020 written by Pursuit Technology with some actual details. The big news is that it is the first 64 bit version of Crystal Reports.

One other significant change is that come January, Crystal Reports will no longer be available from resellers. The only place to purchase Crystal next year will be the SAP web site. This is part of a reconfiguration of the SAP partner program and will probably result in the elimination of many smaller resellers.

If anyone has more info to share on this topic, please let me know.





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.





Server-based scheduler comparison (2020)

Saturday 30 May 2020 @ 9:58 pm

I have just updated my comparison of server-based scheduling tools for 2020. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.

There are 11 products on the list this year and a few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.





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.





Page Header plus Page Footer is too large for the page

Sunday 17 May 2020 @ 10:06 pm

I had a customer call me recently about this error. Usually this error is quick to resolve.  It is almost always caused by a an object that “Can Grow” (e.g. subreport, memo field) in the Page Header. The customer sent the report to me with saved data and it opened fine on my PC. Both sections were empty and both sections were also suppressed. That made no sense to me. I assumed they had sent me a different report by mistake.

So we did a remote session and I confirmed it was the same report. When it opened on their PC the error appeared. After some poking around I went into “Page Setup” to check the page size and noticed that the bottom margin was set to 11 inches. The error message about the PH/PF turned out to be a red herring. The real issue was a combination of the margin setting and the selected printer. Once we entered a reasonable margin setting, the report behaved normally in their environment.

So if you get this error and can’t find anything that would cause it, you have one more place to check.





Repeating the first columns of a cross-tab for each value in the second column

Friday 8 May 2020 @ 11:44 pm

This is better illustrated with pictures. Recently a customer had a Cross-tab that looked like the image on the left below. But they wanted the first column to repeat next to each value in the second column, like the image on the right below.

cross-tab without repeating the first column

I found a relatively simple way to do this:

  1. I created a formula the combined the two column fields into one string, with a dash between them.
  2. I went into the Cross-tab expert and clicked the “Group Options” button for the first column field. I changed it to the new formula.
  3. I then clicked the”Options” tab and checked the option “Customize Group Name Field”.
  4. I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
  5. I went to the the last tab in the Cross-tab expert named “Customize Style” and highlighted the concatenated formula in the “Rows” box.
  6. I checked the option “Suppress Subtotal” (if not already checked) and clicked OK.

The cross-tab then looked like the one on the right.





Reading an Excel column as a true DateTime rather than a string.

Wednesday 29 April 2020 @ 10:43 pm

I have written before about data type changes in Excel. One article explained how to convert a column of numbers into character values so that they can link correctly to another column of character values.

Today I was asked how to do something similar with dates. Two spreadsheets were being linked on a date field, and Crystal was reading one field as a true date and one as a string date. I found a simple way to get Crystal to recognize the string as a date. I created another column that was simply the Date String column plus zero [e.g. =A3 + 0].  This turned the new column into a number representing the date. Then I formatted that column as dates and that allowed Crystal to recognize the new column as a date.

It surprised me to find that I had to format the field as a date. When it comes to dealing with strings and numbers the formatting is usually irrelevant, but apparently with dates the format is important. When I tried to skip formatting the column as a date, Crystal read the new column as numeric values.





The Crystal Reports label wizard

Sunday 26 April 2020 @ 10:36 pm

Every version of Crystal Reports in the past 20 years comes with a neat little label wizard that allows you to automatically set up about 30 common Avery label sizes. But there are a few things you need to know to avoid problems.

Tip #1) A report created using the label wizard can’t be changed to a regular report, and a regular report can’t have one of the label sizes automatically applied. You have to decide to use the label wizard when you first create the report. The wizard is found under the menu options “File > New > Mailing Label Wizard”.

Tip #2) The wizard lets you pick the fields you want, but it will put each field you select on its own line. So, if you create an address label and add the fields City-State-Zip, the wizard will put those fields on 3 separate lines. I add only the first field of each line while I am in the wizard. Then when I preview the report I can add the other fields, or write a formula to combine all of the fields that you need.

Tip #3) This label wizard always puts the label fields on subsections of the details band. If you want to print one label per group, don’t try to move the fields to group headers or you will make lots of unnecessary work. Instead you can make the detail labels appear as if they are at the group level by only printing the first detail label of each group.

To do this you:

  • Add the group to the report
  • Suppress both the Group Header and the Group Footer.
  • Create a running total field that counts the records in each group(reset ‘on change of group’).
  • Suppress the Detail sections or the Detail parent section with a condition that says:

{#running total} > 1

Only the first record (label) of each group will print. This allows you to use the wizard to get the layout automatically, but still print one record per group.





«« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server