How To Create A New Crystal Report

How to create a Crystal template for a new report to pull select data from the Cyrious database. This example is offered to provide examples of how a report is created which can be extrapolated to the creation of other reports specific to the user's need. After a little familiarity with the Crystal program and how Cyrious uses and links its tables, writing custom reports is no more difficult than creating Excel spread sheets or formatted Word reports, with the exception that you will need to use a little code for which in most cases users can find other code close enough to copy and modify. Also, Crystal has a Help menu and a lot of tools to point you in the right direction.

We were asked to create a report which would show how many jobs has been shipped in a specific period through UPS, our carrier, and the total amount invoiced for those jobs, on a job by job basis. Since we utilize Cyrious' interface program with UPS, the information is in UDF fields in the database. When shipments are closed at the end of the day, the UPS Worldship program exports back to Cyrious by job the weight, the tracking number and the UPS charge for the package. This information is stored in Transaction UDF fields. The amount invoiced is calculated in the Delivery product. The task was to retrieve this information and present it in a Crystal report, accessible through the Reports menu in Cyrious.

This “how to” assumes you have established an ODBC connection and are familiar with how to find your Cyrious database. In working with the Crystal-Cyrious interface be prepared for a little trial and error. Cyrious has made the interface about as easy as it could be; but it may take you a few “go-arounds” before you are comfortable getting all the settings and pointers correct. You can not do any harm to your database or other Cyrious functions as you can not modify the data through Crystal. You can only access it.

1. Open your Crystal Reports program (our examples use Crystal 2008 but earlier versions are almost identical in the fundamentals). Select New blank report. You will immediately be presented with a screen to select your database. If you have previously used Crystal to access Cyrious, the link will be in your list. If not, you will go to the “Create New Connection” list and click on “ODBC. That will bring up a screen with a list of possible types. Control Sequel will probably be at the top. Do not click on that but instead the radio button under the list “Find File DSN.” Select the DSN Connection you have created for the Cyrious database. You will need your password Cyrious provided you to access your database through the DSN connection.
2. When you have successfully found the Cyrious storedata database, if necessary, expand the dbo list to see all the Cyrious tables. This will create a long list of table names and these are the accessible tables from which you may draw data for your reports.
   
3. If you have done everything right you will have a screen from the Crystal Database Expert which contains a long list of tables on the left and a blank canvas on the right. For this report, select Transheader, Account, Transdetail, and Transheader User Field.

The Cyrious tables:
Transheader contains most of the top level data about each estimate, order and purchase order.
It is usually the "parent" table in reports.
Transdetail contains the information about what is in an order or estimate - ie the line item information or pointers to other tables which contain even more specifics.
TransheaderUserDetail contains the UDF fields for each transaction.

1. After selecting the tables, your Crystal screen will look something like the page shown above in the Screen Shot. [It will not be the same as the example below as this was taken from the setup for a different report.] You click Next in the Database Expert and you are presented with a “canvas” showing how the tables are “linked”. Cyrious like other database programs stores its information in different files depending on the type it is and then links those separate files to each other, usually with fields with an ID label. In most cases you can figure out how they link by the names of the fields in each database list and the name of the table. For example, in the Transheader table is a field called AccountID. That filed is linked to the field with the same name in the Account Table, as shown below. Crystal tries to link the tables for you but is always wrong. So, the first thing you do is find the button, “Clear All Links” and select that and remove the pre-set links. Now you are ready to link the tables. You can also change the link for a specific table by double-clicking on the line and selecting “remove link” or “remove all links” for that table only.

1. Link Transdetail with the Tranheader Table: For this report we want to find the records which have the Delivery Product to extract the invoice price and then match that with the UPS charge. So, although a little unusual, we make the Transdetail table the parent table. When we find a record with the Delivery Product, we then want to know which Order it goes with. So out first link is to the Transheader table where that information is found. To do that we click on the field in the Transdetail Table labeled TransheaderID and then drag our mouse over the First field in the Transheader table called ID. A line will show that the tables are connected.
2. Link Transheader with the TransheaderUserDetail Table. Next, we need to retrieve the UDF values for the Order which has the Delivery product and this is done through the Transheader link. Click on the ID field in the Transheader table and drag your mouse over the ID field in the TransheaderUserDetail table. The link line will appear. However, this will be a different kind of link. For your previous table you wanted a link where every Transdetail record has a matching Transheader record. If those two conditions are not met, Crystal does not return any record. This is called an Inner Join and is what Crystal sets for you as its default. For this link you want Crystal to return your Transdetail record and your Transheader information even if it doesn't find matching UDF records. This is called a Left Outer Join and should be the “Join” you establish for almost all your links. (If in doubt, always use Left Outer Join) Double click on the blue line connecting the two tables and you will get a selection box showing you the Join options. Click the radio button for left outer join. You should see that the line is now ended with an arrowhead pointing at the TransdetailUserField table. If for some reason the arrowhead is pointing at the Transheader table, right click on the line and choose reverse direction.
   
3. Your tables show now be properly linked. [The screen shot above shows how they appear in Crystal's Links page after you have selected Auto Arrange and double-clicked on each table to minimize it.] This screen shot has the UDF table expanded so that you can view the fields as they would appear on your screen. Note that the link is from the TransheaderID to the TransheaderUserField table ID.
   
   This “arranged” screen is an easy way to check that your links are setup up correctly. Note that all the arrows are pointing from the main table to the subordinate tables, the graphic portrayal of Left Outer Joined tables. If the arrow is pointing in the wrong direction – e.g. to the Transheader table, you can double-click on it and select “Reverse Direction”.
   
   You are now set to prepare your Crystal report. Click OK and you should be returned to the blank page with only a date field. You should see a column on your right labeled Field Explorer. If you don't, go to the View menu and select that. This is one of the wonderful features of Crystal. All your available fields are there and to use them in a report, you simple click on the field and drag it to where you want it in your report. When you click on the “Field Explorer” tab, it will pop out and you will see a list of the categories of Data. Top of the list will be Database Fields, followed by Formula, SQL Expression, Parameter, etc. The database fields are where you will find the Cyrious fields grouped under their table name. Since you have linked the tables, you can select each of these fields in any order you want as if they were all coming from one table.
   
4. Select the Database Fields For your Report: We selected OrderNo from the Transheader table and draw it to its location in the row marked “details”. When you do that you will note that its label comes with it and is placed immediately above the field name in the row marked Page Header. There is a row above that labeled Report Header. If you are familiar with other layout programs like Word or even Excel you can guess what these rows do. The stuff in Report Header will appear only once. The stuff in Page header will appear on the top of each page, and the details stuff will be the content of each page. The label won't be what you want in your report but it's editable so don't worry about it at this point. As you drag the field to its location you will see that Crystal has allotted it a lot of space-the width of the box you are dragging. You don't need that much for your order number; so click on one end and narrow it down to about the size of a six digit number. Next select Description and drag that beside the OrdeNo field. Leave the size as is for now but it will probably need to be widened before you are through. Next in the Transdetail table select the TotalPrice field and place that where you want it, resizing to about a 3 or 4 digit number. Finally, from the UserDetail table select the Shipment Date and Total_UPS_Charges.
5. Set Parameter Fields. Next you will set up the fields through which you exchange information from your Cyrious program to tell Crystal what to do. This is done through Crystal Parameters. So, we right click on the Parameters category and select New. This brings up a box where the first thing we do is choose a name. We name this “Begin Date” as it will be the beginning of the period we want to search in. Next we set the Parameter type to Boolean (Yes/No).. We then Save the Parameter and repeat this process to create a second parameter called “End Date”.
6. Setthe Record Selection Criteria or the rules by which Crystal will extract records. This is done with the Formula Selection Expert feature found under the Report Tab or by clicking on the icon on your toolbar. We entered the following formula: You can see how we set the date criteria to include all records between those two dates. We also tell Crystal to select only records which have the “Delivery” Product which in our system has an index ID or number of 1148. You can get that by putting this field beside the GoodsItemCode on your Crystal Details line and seeing what the number is for your product. We could also have used the name by substituting Transdetail.GoodsItemID with Transdetail.GoodsItemCode= “Delivery”.

{TransHeaderUserField.Shipment_Date}>= {?Begin Ship Date} and
 {TransHeaderUserField.Shipment_Date}<={?End Ship Date} and
 {TransDetail.GoodsItemID} = 1148

1. You are now set to Preview your report to see whether it is actually producing what you hope it will. To do this go to the View, Preview Screen. If everything is done correctly, you will be presented with a menu to select your Begin and End dates. Enter those and click OK and you should see a page or more listing the Order Number and other information you placed in your details line.
2. It may not be very pretty yet; but if you have got a report listing the data you are seeking, you have broken the back of it. Save your report, We suggest creating a folder on the server where your Cyrious program is running called Custom Reports and saving it there. Remember the location and the name so you can now link it back to Cyrious.
3. Link Cyrious to Your New Crystal Report. Go to your Cyrious program and click on Setup/Reporting Setup. You will see a list of report categories. This report goes on the Main Menu. We suggest creating a new category, if you don't have it, called something like “Your Company Reports”, so that your users will know what you've added and what is original Cyrious. Whatever you do, click on the category where you want your report and click on the Menu Selection at the top which says “Add”. Select New Crystal Report. Enter the Name of the report and then where it says File Location “On Drive”, browse to the location of your file and select it. Click Save. Your report is added to your Cyrious Reports and you should be able to find it in your Reports menu and Preview it to see what you have just created in Crystal. If your Cyrious setup was done correctly (you may need some code added to your Cyrious shortcut to use this feature), you can now make a change in Crystal and then when you select Preview to refresh your report, see the effect of what you do there immediately in your Crystal Report.
4. In a separate How To Page we will describe some of the formatting commands you can use to make your report look more professional and to calculate and present the sums of columns and the count of records.

Contributor: Steve Gillispie
Date: 723/2009
Version: Control 4.3_