The Crystal Reports Underground News Volume 2014.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2014:
** Web based deployment options compared (2014)
** Save $100 on PDF-eXPLODE
** My library of Crystal Reports materials
** Cut Light brings VLookup to your formulas
** Crystal Reports 2013
** Let me create your Crystal Reports
** Grouping by multiple values in one record (multi-value field)
** Converting a scientific notation string
** Open enrollment Crystal classes in Frederick, MD
** Auto-linking tables by key.
** Subreports in a Sage/Mas environment
Other recent blog articles:
** Limitations in the Crystal Reports .NET runtime
** Troubleshooting from Screen shots
Gems from the Archives:
Reporting on Data that Isn't There (Volume 2002.08)
"Cloaking" a group in a report (Volume 2002.10)
Web based deployment options compared (2014)
There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The "official" options from SAP are Crystal (Reports) Server and BO Enterprise. You can read about these on the SAP website. But there are other, less expensive products out there that many users never see. These are third party products that allow your users to view reports from a browser. You can also centrally manage your report deployment from a browser.
I have created a page on my blog that lists and compares these products, and I update it every January. This year the list includes 9 products and 2 are new this year. I have even added CR Server to the list to make it easier to compare the official product, and I have also added CSS Portal by Christian Steven. So the complete list is:
Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
CSS Portal with CRD – – a traditional Web portal
Visual Access Report Server – a traditional Web portal
Ripplestone– a traditional Web portal
RVweb – a traditional Web portal
rePORTAL – a traditional Web portal
RV for Windows Pro – a server-based viewer
Report Launch – a bridge between BO server products and server based applications
The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (PDF) that shows some of the specifics for comparison, including prices. If you have any feedback to share on these tools I would be happy to hear form you.
Save $100 on PDF-eXPLODE
Would like to click "print" and have each page of a report automatically E-mailed to a specific person? If so, you should take a look at PDF-eXPLODE. Normally I talk about Email "bursting" when I review scheduling tools, but PDF-eXPLODE takes a different approach. It installs as a printer driver and all you have to do is "print" the output to PDF-eXPLODE. PDF-eXPLODE will then burst the report into individualized PDF documents and email each page to the Email address on that page. It only takes a few tweaks to the report to make the bursting work.
And, because PDF-eXPLODE behaves like a printer, the report can be run in Crystal or in any software that can run reports. This makes it easy to use PDF-eXPLODE with enterprise software running embedded reports. Often these reports can't be run outside of their environment, which means you may not be able to use a desktop scheduler for bursting. But these reports can still be printed and Emailed out through PDF-eXPLODE.
To help promote the newly released PDF-eXPLODE v4, 3000AD SYSTEMS is offering a $100 discount on each desktop license or single-user terminal server license. You can get up to $200 off for 3 or more users in terminal server or CR Server environments. To claim your discount use the promo code "HAMADY", which is valid through 3/31.
My complete 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 ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Cut Light brings VLookup to your formulas
A few months back a user asked me if there was a way to use VLookup() in a Crystal Report. At the time, I could not think of a way. But recently Millet Software has added this ability to the Cut Light dll. Cut Light adds a number of new functions to your Crystal formula editor and this newest one is called GetXLSOutput() which allows you to tap into calculations stored in a spreadsheet.
In general, this function allows you to capture a report value and use it to fill in a specific cell in a spreadsheet. The function then checks for a value in another call in that spreadsheet and returns it to the report. Obviously, this allows the value that you bring back to be a calculation that depends on the value that you sent to the spreadsheet.
The most common scenario I see for this would be when the spreadsheet has some VLookup tables and logic. The function would allow you to tap into the logic maintained in the spreadsheet. If you come up with a clever use for this, please let me know.
Crystal Reports 2013
I have been asked a couple of times this week about Crystal Reports 2013, so I guess it is time to post something. Personally, I have not upgraded since CR CR 2008 (v12). When CR 2011 (v14) came out a couple of years ago, I downloaded the trial and found that it was virtually identical to what I had. There were 3 new features that I mentioned in my blog, but none of these were worth the cost of the upgrade to me. SAP also made it clear that they do not plan to develop new features for the standalone version of CR. They are devoting their energies to the version of CR that is embedded into Business Objects Enterprise.
So when SAP released CR 2013 I wasn't expecting many changes. In fact I am not aware of any changes. And since SAP is describing this as a 'rebranding' of the product as opposed to an upgrade, I don't think I will spend much time looking for new features. One clue is that CR 2013 is version 14.1 while CR 2011 was version 14.0. If anyone finds any new features in CR 2013, please let me know.
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.
Grouping by multiple values in one record (multi-value field)
One of my customers asked me if there was a way to group records by product code. The tricky part was that each record contained several product codes entered as a list like this:
A101, B201, C301
Fortunately, I have run into this requirement before. Once time it was primary and secondary sales reps stored in two separate fields in the same record. Another was a list of characteristic keywords stored in a "multi-value field". The challenge is getting Crystal to show one record in multiple groups at the same time. Normally this isn't possible. But I have found a technique that allows this to happen by duplicating the original record as many times as needed and then assigning each duplicate to a different element from the list. It takes some work so if you need to use this it might make sense to call for some support. But here are the steps:
1) Determine the maximum elements that can appear in a single record. In the sales rep example there were always 2. In the product codes example we had to allow for up to 65. You can pick any number but there are trade-offs. If you pick too small a number you run the risk of missing elements when a list goes beyond that number. But higher numbers will slow down your report more than lower ones. This is because the first thing the report has to do is multiply the number of records that you start with by that number.
2) Find or create an 'inflator' table. Assume the maximum number is 10. So you would need a table that has 10 consecutively numbered record. It could be 1 to 10 or it could be 57 to 67. Any consecutive series of numbers will work, as long as those records can be relied on to be there without changing. You can even use your selection formula to filter these records. If they aren't numbered 1 to 10 you will use a formula to adjust them so that the formula returns 1 to 10. For example if I had 57 to 67 my formula would subtract 6 from the raw value and I would get 1 to 10.
3) While you are in design mode, add this table to the report. Do not link it to other tables and ignore any Crystal Reports warnings you get. With no link you will create a "cross-join" so that every current record in the original report is repeated 10 times. If your database driver won't run without a join, you can try a 'not equal to' join type. Use two fields that will never match as the join fields. Before you preview, add any selection criteria needed to limit the new table so that it returns only those 10 records. If you add the consecutive field to the report you should get 10 times the number of records when you preview. If you need a formula to turn these consecutive values into 1 to 10, create that here.
4) Create a formula that counts the number of values in the multi-value field. Assuming that the list is divided by comma the formula would look like this:
//@CountFormula
Count( Split ( {Your.ListField} , ', ' ) )
5) Add a selection criteria so that the inflator field is less than or equal to the count formula you created in number 4. So now the number of duplicate records for each original record will be equal to the number of elements in the field. A selection formula like this will work:
{inflator.key} <= {@CountFormula}
6) Write a formula field to extract one element for each duplicate record. You want the first element on duplicate record #1, the second on duplicate #2, etc.
It will look something like this:
if {@CountFormula} >= {inflator.key}
then Split ( {FieldName} , ';' ) [ {inflator.key} ]
else ""
Now you can group on this last formula or use it in a cross-tab.
Converting a scientific notation string
One of my customers was reading data from XML. He ran into an amount field stored as a string and noticed that some of the values had been converted to scientific notation. They looked something like "1.234E+6?. He asked if there were a way to convert those back to the original number. After a bit of playing around I came up with the following, which works with most styles of "E" notation that I have seen.
Local StringVar Input := Replace ({@input}, '+', '');
if "E" in Input
then val(Input) * 10^ val( Split (Input, 'E')[2] )
else val(Input)
If the field doesn't have an "E" in it the formula returns the value of the number. It should also work if the E is followed by a plus, a minus or nothing or even if the plus or minus is at the end instead of right after the E.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my March classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines and dates.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.
Auto-linking tables by key
Whenever you have multiple tables in your report you need to specify the linking fields that connect the tables (the join). Crystal will often take a guess at the correct links using "auto-linking". In recent versions of CR you can auto-link two different ways, "by name" or "by key". CR tries "by name" as the default. This works when the linking fields are the same name in both tables and when there are no other fields that have the same name between the tables. In my experience it correctly links the tables one out of three times. It more often adds extra links that break the join. Just because Crystal has created a link you can't assume that the link is correct.
But some databases are configured with primary and foreign key relationships that are visible through the database connection. These databases allow Crystal to auto-link "by key" pretty reliably. I tested it recently while working with a customer and was surprised that it could get enough information from the database to correctly link the tables. So how can you tell if your database supports this? This test will let you know:
1) Open a report that uses this database and that has at least two tables.
2) Open the linking window and click "Clear Links" on the right side.
3) Select the "By Key" option in the upper right.
4) Click the "Link" button.
If CR can't retrieve the information from the database it will generate a warning message. Otherwise it should create the appropriate links. Even when it works you are wise to verify the links are correct by checking the output from both tables on the report.
Feel free to let me know what your experience is with this.
Subreports in a Sage/Mas environment
I was working with a customer today, troubleshooting a report reading MAS 90. The report had a subreport at the detail level and he said that the full report could take up to 24 hours to run. I tested the subreport and found that the selection formula was not being converted into a WHERE clause in the SQL of the subreport. So every instance of the subreport (thousands) had to do a serial read of the entire table. The subreport seemed pretty simple so my first approach was to recreate it and test it as a stand alone report. It took only a few seconds to run and I could see the WHERE clause in the SQL. But as soon as I inserted the new version to replace the existing subreport the SQL would no longer generate a WHERE clause.
So then I studied the relationship between the main and the subreport. I found it was possible to eliminate the subreport and link the subreport data directly to the main report, as long as I didn't add any filters to the new table. This configuration would still require a serial read of the table, but it would only need to do one serial read, not thousands. The report ran in a few minutes.
But later I started to wonder if anyone else had run into this quirk. A little web sleuthing turned up a 2009 document listing MAS 500 Best Coding Practices. And this little nugget was in point #9:
"Avoid subreports, most cases the constraint can't be passed
as the where clause of the subreport query."
So I wasn't imagining things. I don't know if this 'feature' is still part of the current product. But if you are a MAS user with slow subreports, you might want to check your WHERE clause. And if you need help finding an alternative configuration, give me a call.
Other recent blog articles:
Limitations in the Crystal Reports .NET runtime
Troubleshooting from Screen shots
Gems from the Archives:
Reporting on Data that Isn't There (Volume 2002.08)
"Cloaking" a group in a report (Volume 2002.10)
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2014 by Ken Hamady
All rights reserved - Republishing this material requires written permission