Using Crystal Reports with dBase Plus
Note where ever you see me refer to Visual dBase it can also refer to dBase Plus

dBase has a powerful native report writer, but some programmers prefer using other report writers such as Crystal Reports or R & R.  Crystal Reports can be used with Visual dBase via API or ActiveX.  API will provide just about everything you need to control Crystal.  ActiveX is easier to use and although it is not as powerful as the API method, for the most part will do more than a good job.  Below are my recommendations for using Crystal 8.0 and Visual dBase via ActiveX.  Some of the information I have picked up from other programmers from the dbase newsgroups, so I can not take total credit for the information.  If you are using Crystal 7.0 or 8.5, I believe the below methods will work as well.  I use Cyrstal 8.0.  Crystal 9.0 can work, but I believe it requires some adjustments.

1) Overview.
2) Creating a report using Crystal.

3) Installing the Crystal ActiveX in dBase.

4) Creating a dBase form that will generate a report.

5) Upgrading Crystal 3.0 reports from Visual dBase 5.7 to Crystal 8.0.

6) Download Crystal Sample Form & Report.

7) Additional Notes.


Back to Top

1) Overview.

Here is an overview of what you need to do to use Crystal with Visual dBase.

    1) Make sure Visual dBase & Crystal "Developer Edition" are both installed.
    2) Create a Report using Crystal, based on a dBase table.
    3) Install the Crystal ActiveX Component in Visual dBase.
    4) Create a Form in Visual dBase to call or generate the Crystal Report.



Back to Top

2) Creating a Crystal Report.

Before attempting to create a report I would set the Crystal Options so that the "Data Source" looks for dbase tables and indexes.  Crystal can work with many different file types, so setting it to look for dbase tables will make it a little easier to locate your data files.  Open Crystal Reports & Click on the "File Menu" & choose "Options".  Make sure that the data source has *.dbf & that the index has *.mdx.  You can see a sample in the below picture #7.  You only need to do this one time.


Picture 7

Now to create a basic report.  

    1) Click on the "File Menu" & choose "New".
    2) Use the "Report Expert" option & choose "Standard".
    3) Click on the "Database" button.
    4) In the list of yellow folders choose "Database Files" & then choose 
    "Find database file".
    5) Now click on the "Add" button & navigate to where your table 
    ".dbf" file is & select it.
    6) Now click on the "Add" button Again to actually select the file for 
    this report.
    7) Click on the "Close" Button.
    8) Click on the "Next" Button & choose the fields you want to place 
    on the report.
    9) Keep clicking on the "Next" button to choose the option of setting 
    groups, totals, selections & report styles.  When you are finished adding 
    the various options you want, click on the "Finish" button.
    10) Save the report file.
    11) Select the "Database" menu.
    12) Choose "Set Location" & choose "Same as Report".


Picture 8


Picture 9

You might check the "Options" in Crystal for setting personal preferences.  I usually have my options set so that "Snap to Grid is Off" & to "Not save the data with the report".  You want to make sure your table has an index & set Crystal to use Indexes as well.  You can experiment with the options to see what works best for you.  Crystal 8.0 will work with Visual dBase level 5 and level 7 tables, but not Visual dBase - "QBE's", "SQL's" or "DMD's".  If you need to add "Sorting" or an additional "Selection Formula", you can access these features from the "Report" menu.  Exploring the menus is a good idea to see what all you can do.



Back to Top

3) Installing the Crystal ActiveX in dBase.

Installing the Crystal ActiveX component in Visual dBase is very simple.

    1) Create a new form in dBase & remain in design.
    2) Click on the File Menu.
    3) Choose "Setup ActiveX Components".
    4) From the list double click on "Crystal Report Control".
    5) Click on the "Ok" button.


picture 1


picture 2

That's it.  The "Crystal Report Control" is now on the dBase Component Pallet and ready to be dragged on to any form.



Back to Top

4) Creating a dBase form that will generate a report.

Create a form in dBase and stay in the "form designer" for that form.  If the component pallet is not open, do a right mouse click on your form & choose "Component Pallet".  Then go to the "ActiveX" tab on the component pallet.  From the component pallet drag the "Crystal Report Control" on to your form.  Your form should like the picture below.


picture 3

After placing the "Crystal ActiveX Component" on you form, you will need something to actually trigger your report to run.  This can be done with an OnOpen event of a form or by placing a pushbutton on the form.  See the next picture #4 for one of my forms used in outputting Crystal Reports.


picture 4

From the "Component Pallet", you can also drag a "PushButton" from the "Standard" tab onto your form.  Select the pushbutton.  Do a right mouse click on the pushbutton and choose the "Inspector".  From the "Inspector" choose "Events" & then click on the "Wrench" button next to "OnClick".  The dBase editor will open & you can put in your code that will call your Crystal Report.  Here is a sample you can use to get started.

function PUSHBUTTONPRINT_onClick
    local nResults, ax
    ax = form.activex1.nativeobject
    && you might use a variable here instead of "test.rpt"
    ax.reportfilename="test.rpt"
    && set the size & location of the window
    ax.windowheight = 350
    ax.windowwidth = 550
    ax.WindowLeft = 50
    ax.windowtop = 50
    && place other various options
    ax.WindowShowPrintBtn = True
    ax.WindowShowPrintSetupBtn = True
    ax.WindowShowCloseBtn = True
    && You can place a checkbox on your form to allow the user 
    && to choose where the report will go "to the screen or to the printer".

    if form.checkboxprinttoscreen.value=.t.
       ax.destination = 0 && preview
    else
       ax.destination = 1 && printer
    endif
    && now print the report
    nresults = ax.printreport()
Return


picture 5



Back to Top

5) Upgrading Crystal 3.0 reports from Visual dBase 5.7 to Crystal 8.0 for Visual dBase "32bit".

I have seen a few newsgroup posts in the past describing upgrading VDB 5.7 to Visual dBase "32bit".  Usually it is said that you should rewrite your Crystal Reports.  On the Visual dBase "32bit" CD are utilities to convert Crystal "RPT" files to Visual dBase "32bit" "REP" files.  And if you are using "QBE'S" there is also a utility to convert them to datamodules.  I personally have not had a lot of luck in using these conversion utilities, probably because I have "Calculated Fields" in my "QBE'S" and a lot of my reports were based on "QBE's", but also probably because I did not have the patience to see it through. 

It does take a little time, but it is possible to convert your 3.0 Crystal files to 8.0 Cystal files.  It definitely saved me some time.  If my reports were based on "QBE'S", then I would output my query to a table and then changed the report source from the "QBE" to the table "DBF".  To do this open up your report in Crystal 3.0.

    1) Click on the "Database" menu.
    2) Choose "File Location".
    3) Click on the "Set Location" button.
    4) Replace the "QBE" file with the new "DBF" file name.
    5) Click on the "Same as Report" button as well.
    6) Click on the "Done" button.
    7) Save the report & close.

Then in Crystal 8 I would do the following to upgrade (Crystal 3) to (Crystal 8):
    1) Load the old Crystal report in Crystal 8
    2) Go to the "Database" menu
    3) Choose "Convert Database Driver".
    4) Change the driver to "pdbbde" or optionally "pdbxbase"
    from the "pdqqbe.dll" that Crystal 3.0 used.
    5) Save the report file, close it & reopen it.
    6) Choose "Database" menu again.
    7) Choose "Verify database" & let it fix up the report.
Just save the report & thats it. It should work using ActiveX in Visual dBase without any problems.  Now if you have some calculated fields, you will be prompted to fix them and you'll have to learn a little about the Crystal Syntax versus the dBase Syntax.  For example - if you need to reference a field in your Crystal formula it would look something like:   {CLIENT.CLIENT->FIRST}+{CLIENT.CLIENT->LAST}


picture 6



Back to Top

6) Download Crystal Sample Form & Report.

cprint.exe    Self extracting file.

This zip files includes:

    1) animals.dbf & animals.mdx
    Note that I removed the binary field "for faster downloading".
    2) cprint.wfm
    3) ctest.rpt

Download & unzip the cprint.exe file into a temporary directory.  Then open up Visual dBase & run the "cprint.wfm" form.  You can open up the "cprint.wfm" form in the editor & look at the code to see how it works.

There is also a "Crystal Report Viewer Control".  Here is sample from "Vic McClung" that you can download and try.  The nice thing about this control is that you can actually place your report on a dBase Plus form, so that the report looks even more integrated and customized for your specific application.  An Excellent example:    cprint2.wfm     Download the cprint2.wfm file and just run it.  You have the option of selecting any Crystal Report.



Back to Top

7) Additional Notes.

There is a lot of information on using Crystal Reports "Ver 8" in the developr.hlp file located on the Crystal CD.  Just do a search for activex or api.  It will also tell you which files you need to deploy.  If you are using "Install Shield Express", you will need to copy & paste those needed files into the deployer & set them to go to the "Windows" directory.  Also you can use a variety of methods to actually selecting the data for the report.  You can use the activex method itself:

 "ax.selectionformula = "{temp.CUST_ID}='TGU028'"
 && or use a variable
 ax.selectionformula = M->TEMP

Most of the time, I do all the work before the report runs.  Visual dBase can select records real fast by using indexes & keys.  I'll use a key to select just the records I need & then output those records to a temporary table that the report will use as its source.  This works real well.  You can have a lot of options here.  You can use sql or you can even use your old qbe files to create the temporary report file.  The qbe file may save you time at the moment, but the sql file will be faster and better for the long run.  Note also on your form that the "Crystal ActiveX Component" still displays when the form is running, so I usually hide it or place it somewhere off the form where it can't be seen.  Also don't close your form or the report will close as well. 

This is just basic information and there's a lot to learn.  I hope this will help some of the dBase programmers that wish to use Crystal get ahead start.  This information assumes that you have some knowledge with dBase Plus or Visual dBase and that you know how to use tables and queries.  

I welcome any comments:  richard@maplesoft.net  But if you really want to learn how to use Visual dBase go to the dbase newsgroups.

Go to the dBase website:  www.dbase.com

Go to my website: www.maplesoft.net

Go back to the Top

Go Back to my dbase homepage.