The Crystal Reports Underground News - Volume 2020.07
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for July 2020:
** RPT management utilities for 2020
** Are you ready for Crystal Reports 2020?
** My library of Crystal Reports materials
** Crystal Dispatch site license for $5
** Updating a database table from a Crystal Reports formula
** Let me create your Crystal Reports
** Giving the database what it needs
** Objects highlighted, but only in design view
** Individual Training with an expert.
** Free lesson in Cross-tab grid functions
** Fun with looping logic
Gems from the Archives
Using unlinked tables (2010.01)
Using the parameter "batch" interface toolbar (2010.01)
RPT management utilities for 2020
I have just updated my comparison of RPT management utilities for 2020. 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
Are you ready for Crystal Reports 2020?
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.
My online library of Crystal Reports materials:
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the "Library" page of my site.
Crystal Dispatch site license for $5
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.
Updating a database table from a Crystal Reports formula
One of the things that makes CR such a powerful and flexible tool is the ‘ecosystem’ of third party tools that has grown up around Crystal Reports. I was reminded of this last week as I worked on a project using the Cut Light UFL by Millet Software. The Cut Light UFL allows your formulas to do more than simply add columns to a report. It includes 130 additional functions that let you interact with the operating system, other applications and even SQL databases (among other things).
In this case I was helping a customer create a report that reads data from one database, does some complex calculations and then writes the results of the calculations to a table in a data warehouse. The function we were using lets our formula launch a SQL INSERT statement to add a row to the table. We had done this before with smaller tables, but this time the INSERT involved a table with dozens of fields, and we ran into a limitation on the size of the query that Cut Light could process. I asked Millet software about the limitation and within 2 hours we had an updated UFL that could handle SQL statements up to 250K characters.
If you want to learn more about giving superpowers to your formulas, you can see my annual review of User Function Libraries (UFLs). If you want to get an overview of ALL of the third party products available for Crystal Reports, you can see my product LINKS page.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Giving the database what it needs
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.
Objects highlighted, but only in design view
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.
Individual Training with an expert
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
Free lesson in Cross-tab grid functions
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
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.
Gems from the Archives
Using unlinked tables (2010.01)
Using the parameter "batch" interface toolbar (2010.01)
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2020 by Ken Hamady
All rights reserved - Republishing this material requires written permission