The Crystal Reports Underground News - Volume 2016.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2016:
** Web based deployment options compared (2016)
** Alternatives to Crystal Reports
** My library of Crystal Reports materials
** Crystal Reports 2016
** SAP "Idea Place"
** Let me create your Crystal Reports
** Troubleshooting a missing record
** Keyboard shortcuts in the Formula Editor
** Open enrollment Crystal classes in Frederick, MD
** Failed to Export the Report (PDF)
** Reset 'locked' SQL in Crystal Reports.
** Gems from the Archives
A formula that finds Easter for any year
An improved formula for getting the report file name
Web based deployment options compared (2016)
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. 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, including CR Server, itself:
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.
Alternatives to Crystal Reports
After reviewing a few more BI products, I have decided to focus my comparison of Crystal alternatives on true reporting tools rather than the broader category of BI tools. My guiding question is still this:
I found that several of the leading BI tools provide primarily high level summary and/or visualization. But, they don't have the ability to create day-to-day operational forms (invoices, purchase orders, custom reports). I create these every day in Crystal Reports. So I have dropped some of the tentative columns in my original grid and replaced them with two more true reporting tools, Jasper Reports and Cognos Impromptu. I also hope to add Xtra Reports by DevExpress.
The two new columns have been started but are not completed yet. I was able to fill in the rows that describe each tool's basic approach, but I don't know all of the detailed features that each supports. If anyone has a working knowledge of these tools and is willing to fill in some of the feature rows, that would be a great help.
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.
Crystal Reports 2016
I just read a "what's new" PDF from SAP that talks about Crystal Reports 2016. The new features are not earth shaking but it is nice to see that product development on the stand-alone version isn't completely idle. From what I read the new features are:
- A vertical alignment control with a condition button (my "functions to nowhere" will get used after all)
- Conditional formatting controls with condition buttons for line and box properties. One control is for the style of the box which will allow the box to be conditionally suppressed.
- A formula function that retrieves descriptions stored with parameter fields
- Some new native drives for Oracle, SQL Server and a few SAP databases.
I haven't upgraded my own software since 2008, but this list would make an upgrade worthwhile for me. The release is tentatively set for the first half of next year. The full PDF is here but it covers all SAP/BO products. Crystal Reports starts on slide 97.
And thanks to Andrew Baines of Pursuit Technology for sharing the PDF and his own blog post with me.
SAP "Idea Place"
I haven't mentioned SAP's "Idea Place" in a couple of years. This is a site where you can suggest product features or improvements. Once an idea is submitted, people can vote on them and add comments. Once an idea has 20 votes SAP considers that feature and then responds (eventually). SAP just recently responded to Idea #D1146, creating an XML-based file format as an alternative to the proprietary RPT format. This was submitted five years ago and SAP responded in late December. The response is that they will NOT be implementing this feature.
On a positive note, two of the new features being planned for CR 2016 (vertical alignment and conditional formatting for lines/boxes) are in the top 10 "most votes" ideas, so maybe SAP does listen.
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.
Troubleshooting a missing record
I had two different cases in the past week where a customer wanted to know why a specific record was missing from a report. I used the same process in both cases and thought I would share it. So say a report should include Order 273 and it doesn't.
The preliminary step: missing or hidden?
Before we start we want to make sure that the 'missing' record is really missing and not just hiding in the report. Go into the select expert and add a new rule that says [ {Order ID} = 273 ] and then refresh the report. When it is done look at the record count in the status bar at the bottom of the screen. Do NOT look at the report totals or anything else for this step. If the number in the status bar is not zero than the record is not missing, it is hidden. It might be suppressed or it might have been grouped in an unexpected place, or it might be filtered out using group selection, but it is already in the report. If the record count is zero then you have a missing record and can start the process below.
Selection Criteria or Inner Join:
There are really only two things that can prevent a record from being included in a report, an Inner Join or the Selection Criteria. So if Order 273 is not in the report then there is either an inner join to a table with no matching records, or Order 273 does not qualify on one of the report's criteria. So we need to back these things out until the record shows up. Go into the selection formula and cut out or comment out the entire formula. Then add the test rule that I mentioned above:
{OrderID} = 273
Refresh the report with that criteria and see if the record count in the status bar shows more than zero records. If it does then the problem is in the selection formula. Leave the new rule in place and add one of the original rules at a time back into the selection formula. See which one makes the record count drop to zero. If you find a suspect rule, put back all of the other rules and see if Order 273 still shows up. This way you know for sure that only one rule was the problem.
If you have removed all of the rules in the selection formula (except for the test rule) and the record count is still zero, there is one more place to check. Criteria can sometimes hide in the group expert if you use specified order. If all of the groups use ascending (A) or descending(D) order you can skip to the "Joins" step below. But if any groups show "S" for "Specified Order" go into the Options button for that group and check the "Others" tab. An others setting of "discard" could eliminate Order 273. Change this to say "Leave in their own groups", refresh and check the record count.
If after all that the record count is still zero then the problem has to be an inner join. Go into the Database Expert (Links tab) and hit the "auto-arrange" button. If the table used in our test criteria is not on the far left, then reverse any joins that come out of the left side of that table. To reverse a join, right click on the line, select reverse join, and then hit "Auto-Arrange" again. Repeat this process until the table that contains the test criteria field is on the far left. Then take all the join lines and change them to Left Outer Joins. Now the record should show up. To figure out which join is the problem you can start changing the Left Outer joins back to inner joins, one at a time, and refresh each time. Start with the joins that come out of the far left table. Then change the joins that go from those tables to other tables, and so on. When the record count goes back to zero, that will be the table with unmatched records.
And, if you need help applying these steps, you can always give me call to schedule a session.
Keyboard shortcuts in the Formula Editor
My computer career goes back to the first IBM PC, long before the mouse was introduced. That makes me a big fan of keyboard shortcuts. I find that many tasks go faster when I don't have to lift my fingers off of the keyboard to grab the mouse. For example, I usually cut, copy and paste from the keyboard using Ctrl+X, Ctrl+C and Ctrl+V.
So I was very pleased to learn some new keyboard shortcuts that are specific to the Crystal Reports formula editor. I knew these two:
Ctrl+A : standard 'select all' selects the entire formula
Ctrl+S : save the formula (and close formula editor)
But I did not know these three until one of my customers pointed them out:
Alt+S : save the formula (without closing the formula editor)
Alt+M : comment / uncomment selected lines
Alt+C : check the formula
The Alt+S is one I have been looking for so I have already added it to my toolbox. And, thanks to Laurie Weaver, a developer at Wyse Solutions, for sharing this this idea.
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.
Failed to Export the Report (PDF)
I had a user ask me about this error. He was using Version 11.0.0.895 (the original release), so I suggested that he apply some updates or check for font problems. He did some digging and found that if her removed Windows Update KB3102429 the problem went away. That led me to this discussion on the same topic which nails down a font problem. Since XI is still widely used, and since this might affect other versions, I thought this might help someone else.
And thanks to Josh Pals, Controller at United Gear and Assembly, Inc., or sharing his find.
Reset 'locked' SQL in Crystal Reports.
I was helping a customer troubleshoot a misbehaving report. He could add criteria to the report, but when he removed the original criteria from the selection formula the it was still applied. So I looked at the SQL that CR was generating and found that it was not being updated based his our changes. After a few more tries my customer asked me about an unfamiliar button below the "SHOW SQL QUERY" window. Only then did I noticed the extra button that said RESET. I had not seen that button in about 10 years, but I now knew the problem with this report.
This report was originally created in an early version of Crystal, probably v8.x. In these early versions you were allowed to tweak the FROM and WHERE clauses in the automatically generated SQL. This is no longer allowed because now we can create SQL Commands. Back then, when you modified the SQL it became 'locked' and Crystal would no longer update it. If you added more selection criteria the new criteria would not be incorporated into the SQL. Instead it would be applied after the data came back from the database. When you wanted to revert to automatically generated SQL you would hit the RESET button.
In current versions of CR the RESET button is not needed so it doesn't normally appear. But it will appear automatically if you open an old report that has locked SQL. This allows you to eliminate the use of an obsolete feature. So I checked the SQL to see if the original tweaks were still essential. There were no UNION queries, subqueries or filters built into outer joins. So I reset the SQL and the RESET button disappeared. After that the report behaved like a normal report.
Gems from the Archives
A
formula that finds Easter for any year (March 2005)
An
improved formula for getting the report file name (March 2005)
Removal instructions
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
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 2016 by Ken Hamady
All rights reserved - Republishing this material requires written permission