Consultancy and Programming
Technical Pages
About Us
Advice, tips, techniques and downloads for Visual Foxpro developers.
Although Visual FoxPro has a very capable report generator, many developers prefer to use a third-party reporting tool instead. One of the most popular of these is the venerable Crystal Reports. In this article, we'll take a look at what CR has got to offer the VFP professional, and the implications of using it with Foxpro data.
I am happy with VFP's own report generator. Why should I switch to Crystal Reports?
Put simply, Crystal Reports is considerably more powerful than Visual FoxPro's own reporting tool. VFP can handle most reporting tasks, but you sometimes need to resort to tricks and kludges to make it do what you want. Crystal Reports, on the other hand, has a lot of advanced functionality built in and is generally considered easier to use. Even taking account of the many enhanced features that were introduced in VFP 9.0, CR is still the more powerful tool.
Here are some example of things which CR can do much more easily than VFP:
These are just a few examples. In many cases, things which are awkward to do in VFP can be done in CR with a simple command or menu choice.
Does Crystal Reports work with VFP data?
Yes. CR provides three ways of accessing Visual FoxPro tables:
Can I base my report on local views rather than tables?
Yes. CR doesn't know or care if the data is coming from a table or a view.
If I distribute reports to my users, will they need to install Crystal Reports in order to view or print them?
No. As a VFP developer, you will probably want to integrate the reports into your applications. If you do, your users won't need CR itself in order to view or print the reports, but they will need certain run-time files - more about this later.
If you prefer to distribute free-standing reports, it is possible to "compile" them into EXE files that you can distribute independently of CR. This ability was available in Crystal Reports 7.0, but was dropped in later versions and is no longer supported.
Is it possible to deploy reports on an intranet so that users can view them in their browsers?
Yes. There is a component called the Crystal Web Component Server (CWCS) that lets you do exactly that. This component comes with the Professional and Developer editions of Crystal Reports. You install it on a web server, then copy your report files (RPT files) to a dedicated directory on the server. You must also give your users a viewer program - you'll find this included with the Professional and Developer editions. In fact, there is a choice or viewer programs, aimed at different browsers and configurations. For Internet Explorer users, the ActiveX control is usually the best choice.
Once you have done all this, your users will be able to view any of the reports on the server simply by navigating to them within the browser. When they do, they will see a report preview window which shows the report exactly as you designed it. Note that this is the actual report, not an HTML rendition of it, so you won't lose any formatting. The users will be able to refresh the report and print it, but they will not be able to alter its layout in any way.
I want to print my reports from within my VFP application. Is that possible with Crystal Reports?
Certainly. In fact, you can add the full functionality of Crystal Reports to your VFP apps. There are several ways of doing that. The recommended approach is to use something called the Report Design Component (RDC).
The RDC is an ActiveX Automation server which exposes the CR object model to your application. Essentially, it lets you perform any task programmatically which the user can do within CR itself. For a start, you can open a report, refresh it and print it. More interestingly, you can modify its contents and layout at run-time. For example, you could let the user choose the fields to display, the fonts and colours, the sort order, the record selection criteria, and quite a lot more.
Just about the only thing which the RDC cannot do is preview the report on the screen. That's because the RDC itself has no user interface. To preview the report, you need to use another component, known as the Report Viewer control. This is an ActiveX control which you drop onto a VFP form. The control will display the report preview at run time, and also let the user refresh and print the report (unless you stipulate otherwise). It is highly customisable, so if you don't like the way it behaves, you can easily change it.
To use the RDC and the Report Viewer control, you will need Crystal Reports Developer edition.
Using the RDC, is it possible to run reports which are not tied to any particular data source? I'd like to decide the data source at run time.
If you are using natively-accessed tables (that is, Fox2X tables; see above), you only need to alter one property to point the report to a different data source. With ODBC and OLE DB data sources, you change two properties to switch to a different table or view, either in the same DBC or a different one. What you can't do is to programmatically switch between natively-accessed tables and ODBC / OLE DB data sources. However, that's rarely a problem.
I have heard that Crystal Reports comes with its own programming language. Is this an alternative to Visual FoxPro?
CR has a so-called formula language, which is used to create calculated fields, and also to control the behaviour of the report at run time. You can use it, for example, to dynamically control the grouping in the report or to apply conditional formatting to the report's data.
Although the formula language can add a great deal of intelligence to a report, it is not really an alternative to using VFP or the Report Design Component. The formulae are actually part of the report, and are evaluated as the report is running. The RDC is used to invoke CR features and to make global changes to the report before it is run. The two techniques are largely complementary.
In fact, CR has, not one, but two distinct formula languages: the original Crystal syntax and a newer Basic-like syntax. You can use whichever one you are more comfortable with. You can even use them both in the same report (but not in the same formula).
We already have some reports created in CR. These prompt the user for parameter values every time they are run. Is it possible to hide this prompt and to provide the required values from my VFP application instead?
Yes. This is one of the many things that you can do with the RDC. In general, report parameters are not particularly useful when you integrate the report into an application, since the application can itself provide the equivalent functionality. If you have an existing parameter-based report, the RDC will let you pass the values programmatically so that the users won't see the prompt.
In Visual Foxpro, the destination printer sometimes gets "hard-coded" into my reports. Will I have the same problem with CR?
As you probably know, VFP's report files can store the name of the destination printer, as well as the paper source, orientation, number of copies, and several other settings. Once this information gets into the report file, it cannot easily be overridden at run time. The usual method is to hack the FRX file, which is far from an ideal solution (for further details, see the Foxstuff article, Controlling report settings at run time).
Crystal Reports does not have this problem. In general, the report will go to the Windows default printer on the user's system, although the user can select a different printer interactively if necessary. Alternatively, if you are using the RDC, you can programmatically specify the destination printer, paper source, orientation, and so forth. This is just a matter of setting the appropriate properties or parameters.
How easy is it to export my reports to Microsoft Word, Excel, HTML or PDF?
Very easy. The Export menu supports all those formats and several more. In most cases, you can choose to save the exported report as a document or to open it directly in the parent application. If you are using the RDC, you can also export your reports programmatically.
According to the documentation, it is possible to design a report within the development environment rather than having to launch CR itself. How do I go about doing that?
You can't - at least, not in VFP. This is a feature of the RDC, but it only works with Visual Basic.
On the other hand, it's an easy matter to provide a link from the VFP development environment to the CR report designer. One way to do that is to store your Crystal report files (RPT files) under "Other files" in your VFP project (be sure to exclude them from the build). Once you've done that, you can open a file in the report designer simply by selecting it and clicking the Modify button.
Is it possible to programmatically create a new report from scratch within my application?
Yes. The RDC has a method which lets you create an "empty" report, and further methods for populating the report with fields, text, etc.
Do I need to distribute any special run-time files if I use the RDC? What are the licensing issues?
There are certain DLLs that you must distribute with your application, some of which need to be registered on the user's system. If you are using VFP 6.0 or below, the Setup Wizard will handle that task for you - provided you know the names of the files in question. With earlier versions of CR, this was relatively straightforward as there were only three or four DLLs to worry about. With more recent versions, the number of DLLs has grown considerably, and it can be hard to find out which ones you need to distribute and whether they need to be registered. Things are easier if you're using a professional installation tool like InstallShield, as all the required files are available in merge modules.
If you own Crystal Reports Developer edition, you have a licence to distribute the required DLLs without royalties. As an exception, if your application lets the user create new reports from scratch, you need to obtain a special licence from Business Objects, and this does involve royalty payments.
How easy is it to learn the Report Design Component?
The RDC is an ActiveX Automation server. If you have used ActiveX Automation (also known as COM Automation) with Word or Excel, you will be off to a good start. If not, you will need to spend a little time learning the concepts. Having done that, you need to get to grips with the CR object model, which can be quite daunting. However, you don't need to learn the whole thing in order to get started.
Unfortunately, the RDC is not very well documented - at least, not from a VFP perspective. There is a help file and a manual (in PDF format), but these don't do a particularly good job of explaining the RDC or the object model. They do include some sample code, but this is entirely in Visual Basic. In fact, the entire documentation is based on the assumption that you will be using VB as your programming language.
What about third-party books?
There are many good books available on Crystal Reports, although they are aimed mainly at end users or, occasionally, at VB programmers. For some specific recommendations, please see our books page.
How good is Business Objects' support for Visual FoxPro developers?
It's not at all good. For the most part, Business Objects' support staff are knowledgeable and helpful, but they know nothing about Visual FoxPro. The same is true of the documentation and of the web-based support service. In fact, the company appears not to recognise the existence of any programming language except Visual Basic. When posing a question to the support staff, it is best to frame it in VB terms if possible.
How much does CR cost and where can I buy it?
The US list prices for Crystal Reports are $195 for the Standard edition, $495 for the Professional edition, and $595 for the Developer edition. Upgrade prices are also available. (Remember, you'll need the Developer edition if you want to use the RDC.)
Although you can buy CR direct from the Business Objects subsidiary in your country (see the Business Objects How to Buy page), it's usually cheaper and easier to purchase it from your usual software dealer or from an on-line source such as Amazon.
See also:
Mike Lewis Consultants Ltd. June 2001. Revised January 2006.
FoxStuff is maintained by Mike Lewis Consultants Ltd. as a service to the VFP community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).
The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.
© Copyright Mike Lewis Consultants Ltd.