How to create a Crystal template for a new report to pull select data from the Cyrious database. This example is intended less so prospective Crystal report writer will be able to recreate this particular report than 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 users can find other cose 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 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. For help and information on doing that see Using Crystal With Cyrious. 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 prgram (our examples use Crystal 2008 but earlier versions are almost identical in the fundamentals). Select New blank report. You wil 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 Cyrioous 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, you click Next in the Database Expert and you are presented with a “canvas” showing how the tables are “linked”. The is the critical step in accessing Cyrious data and in using Crytsal. 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.
2. 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 labelled 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.
3. 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 radion 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.
4. Your tables show now be properly linked and you are 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 labelled Field Explorer. If you don't, go to the Vuew 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 draw it to where you want it in your report. When you click on the 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 databse 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.
5. 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 labelled 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 teh 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 alotted 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.
6. 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”.
7. Set the 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 substiuting Transdetail.GoodsItemID with Transdetail.GoodsItemCode= “Delivery”.

<code> {TransHeaderUserField.Shipment_Date} >= {?Begin Ship Date} and {TransHeaderUserField.Shipment_Date}