The Crystal Reports Underground News - Volume 2017.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2017:
** Web based deployment options compared (2017)
** Reduced prices for the "Expert" series
** My library of Crystal Reports materials
** New web portal from ChristianSteven Software
** ReCrystalize releases ReCrystalize Server web portal
** Let me create your Crystal Reports
** Group-specific subreport that only hits the database once
** Renaming image objects
** Open enrollment Crystal classes in Frederick, MD
** Putting a watermark under lines or boxes
** Adding comments to formulas
Gems from the Archives:
Setting section height numerically May 2006
Printing "Continued on Next Page" July 2006
Web based deployment options compared (2017)
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 also web delivery of Crystal Reports. These third party products allow your users to run and 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 features 10 products, three of which are being listed for the first time:
Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
RVweb – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
ReCrystallize Server – a server-based web viewer
Report Launch – a bridge between BO server products and server based applications
RapidStack – Web Portal service built around Business Objects Enterprise
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. This year there are several new lines in the matrix. They show which tools provide workflow/BPA support, Web APIs, password management and menu localization. If you have any feedback to share on these tools I would be happy to hear from you.
Reduced prices for the "Expert" series
Starting this month, the prices for the "Expert" series of educational PDFs have all been cut in half. So if you were hesitant, maybe the new prices will make it easier to try them out.
Check out the new prices below:
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 ($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.
New web portal from ChristianSteven Software
ChristianSteven Software has released a new web portal product in 2016 called IntelliFront BI. It can run Crystal Reports, SSRS Reports and MS Power BI dashboards. It also allows you to create and run proprietary KPI reports and scorecards.
As a web portal, it allows users to run reports on demand. But it can also run them automatically, either on a fixed schedule or based on data driven events. This allows you to automate business processes and workflows.
It even includes a web service API for integration with other tools and systems. The license is $50,000 which includes unlimited users, unlimited servers and unlimited cores. They offer a free live demo for those who are interested.
ReCrystalize releases ReCrystalize Server web portal
ReCrystalize has just released a simple web portal called ReCrystalize Server. It allows you to make your Crystal Reports available on an IIS server. Users can browse the reports which are stored in folders. They can then open and refresh the reports through their browser. ReCrystalize Server will prompt the users for any necessary parameters, or you can set up links to run reports with fixed parameters embedded within the URL.
The cost is $995 per server with an optional $500 per year for support and maintenance. For more information you can read about ReCrystalize Server on the ReCrystalize website. There is even a live demo to show you how it works.
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.
Group-specific subreport that only hits the database once
Group-specific subreports are always a last resort for me, because they hit the database multiple times and usually that slows things down. Sometimes, though, they are necessary.
I recently had to create a report with about 100 groups, and each group needed a subreport that ran a separate query returning a very large dataset. I was concerned about the time it would take to hit the database 100 extra times. I was trying to find a way to read the data just once for all the groups and yet still have each subreport provide group-specific data.
I knew that when you move an un-linked subreport from the report header to the group header it only refreshes in the first group and is simply repeated for every other group. If I could fool Crystal into thinking that my subreport was the same query for all groups, then it should only run once. So I changed the SQL in the subreport to return the data for all groups, and then added a parameter to select one group value at a time. I found that when I placed the parameter in the Record Selection Formula Crystal would refresh the subreport with every group. But if I put the same parameter in the "Saved Data" selection formula, which forces it to be evaluated locally, the subreport would not refresh for each group. I still get group-specific data for each group, but only one hit to the database.
Renaming image objects
This was the first time I found a practical use for renaming a report object, a feature that has been available since 2002.
Every object placed on a report is given a unique "object name". These names appear in the Report Explorer (View > Report Explorer) which lists all of the objects and where they are placed. The default object name for fields is the name of the field with a number after it. The number makes each object name unique, since the same field can be placed on the report multiple times. Graphic objects are also given object names. For instance, a series of lines would have the default names of Line1, Line2, etc.
In a report I did last week I needed to show the status of each transaction by displaying a small image that represented each status. This required stacking several images in the same place and suppressing most of them so that only one was visible at a time. The challenge was that once the images were stacked it was very difficult to determine if the correct one was selected. All the object names were Picture1, Picture2, etc.
Fortunately, you are allowed to modify the object name, as long as it is unique. To rename an object you right-click on the object, select "Format Object" and go to the common tab. The object name is at the top. I renamed each image with the status it represented. Now when I select an object in the Report Explorer I know which one it is and can apply the correct suppress condition.
Notes:
1) Lines and boxes do not have a common tab, so you can't change the object names.
2) The Report Explorer was introduced with version 9 and that is the first version that allowed us to view and edit object names. If you open a report from CRv8 or older the object names will be more generic (Field1, Field2, etc). However if you copy and paste one of these objects while in a later version the newly created object will be named like current objects.
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. Call for details.
Putting a watermark under lines or boxes
Crystal will allow you to layer or superimpose images and text. Sometimes this is done intentionally using a faint image which creates a watermark effect behind the text. But if you are using lines or boxes with your text, the watermark won't behave the same way. For some reason, text objects can be set to appear "in front of" an image or can be "moved to the back". But Crystal lines and boxes will always appear "behind" an image. So if you put a watermark image behind a section that has both text and lines, the text can be moved to the front and will appear but the lines will always be hidden behind the image. Here are the 4 workarounds:
1) Use an empty text object with a border instead of a line or a box.
An empty text object can have a border on one side to make a line or on all four sides to make a box. And because this is not a true line or box object it will still give you the option to keep it in front of an image. The downside is that borders only come in one thickness which is thicker than the hairline people often want for their lines and boxes.
2) Put the lines and boxes in a subreport.
For some reason, if the image is in the main report and the lines/boxes are in a subreport, the lines/boxes can be kept in front of the image. Some users put both the text and the lines/boxes in the subreport. Of course subreports add another layer of complexity. And if the subreport requires a repeating query it can be a real performance killer.
3) Use an OLE object for the lines or boxes
You can add an OLE object like an Excel spreadsheet or a Word Doc, and draw your lines and boxes in the OLE document. The OLE object can be moved to be in front of the watermark image and then it will be visible.
4) Modify the image itself to include the lines and boxes.
OK, this is punting, but it works in cases where the lines and boxes are always in the same place relative to the image.
Adding comments to formulas
Most programming languages allow you to add comments in your code. This is useful to explain a calculation or to document changes. Creating these comments usually involves some special punctuation at the beginning and/or end of the comment which tells the program to skip over those lines. There are several different syntax patterns that indicate comments and these vary by language.
In Crystal Reports formula syntax you insert a comment by adding two forward slashes [//] at the beginning of the comment. Crystal will ignore those slashes and also ignore anything to the right for the rest of that line. This means you can add a comment in the middle of a line like this:
The {table.code} = 'abc' //code 'abc' is for special cases
then 'special'
else "
Or you can start a line with two slashes and Crystal will ignore the entire line. In either case the commented text will turn green to show that Crystal recognizes the comment.
But one warning, I don't recommend that you put any comments in the selection formulas (record or group). The reason is that the selection formulas can be rewritten by the select expert. Any time you use the select expert to change the criteria, Crystal will regenerate the selection formula from scratch and all the comments will disappear. So if you need to add a comment to the report criteria you can write that criteria in a separate formula field, and include the comments in that formula. Then you can use the formula field as part of the selection criteria.
Gems from the Archives:
Setting section height numerically May 2006
Printing "Continued on Next Page" July 2006
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 2017 by Ken Hamady
All rights reserved - Republishing this material requires written permission