Description

Create a report which presents sales data by year across the top in columns similar to an Excel layout. Can be exported to Excel and easily manipulated. Shows the annual sales results for employees who have entered Orders during the chosen period.

Concept

Crystal reports normally capture and present data in a linear fashion. So if you want to create a report showing sales for each salesperson for a series of years, these would be presented as rows. Sometimes you want the results in column form for a more readable presentation or for export to Excel where it can be manipulated more easily. Crystal's Cross-Tab report is the solution. The name is intimidating but it's really very easy to set up. This page is intended to help the first-time user with that process and describes how to set up a cross-tab report for sales results.

Cautions

Nothing to worry about. The worse that can happen working with Crystal is you lose time and don't get the report you want. The program draws data from the Cyrious database but does not alter tha database no matter what you do.

Steps

Step 1

Create a blank page in Crystal. How to access your database using an ODBC socket is described in another paper in this Wiki. Cyrious tech support can set this up for you very quickly if you do not have one. Expand the table list and click on the following tables: Account, Employee, Trasnheader. Right click on each of these table successively, select rename, and change the names as follows: Transheader→Order, Employee→Salesperson, Account→Customer. Note that this is not necessary to create the report but makes navigating the tables a little easier.

Step 2

Go to the Links tab in Crystal and clear all links from the table. Crystal has a command for this. Expand the Transheader table until you see the AccountID label and drag from that to the ID table in the Account table. Crystal will show a line linking the two. Click on that line and select "Left Outer Join". If you have done this correctly the blue line will change to show and arrowhead pointing to the Account table from the Transheader table. Next locate the Salesperson1ID label in the Transheader Table and d to the the ID label in the Employee table. Follow the same procedure as above to make this a left outer join connection. You are now ready to build your report. Click OK and you will be presented with the Crystal template "blank sheet" form.

Step 3

Place your cursor on the left hand side of the template where it says report header and right click. Select Insert Section Below. The result should be a new section named Report Header B. You may now use the top section Report Header A for the report title and other information and Report Header B is where you will place your CrossTab table.

Step 4

You are now ready to create the variables the report will use. In the Field Explorer on the right of your screen right-click on the Formula Fields, select New and name your formula "Salespersons Name". In the section for the formula itself, enter the code below: (If you did not rename the table as suggested above, the field names would be Employee,LastName, etc.) Save the formula. 

{Salesperson.LastName}+", " +{Salesperson.FirstName}

Step 5

Next rightclick on the Parameter Fields name in the Field Explore and select New. Name your parameter "Begin Date". In the setup form, make the parameter Type a Date parameter. Save it and recreate the same steps naming your second parameter "End Date." Do not forget to make the type Date. (Do not make these Date Time types for this setup.)

Step 6

Now click on the Insert Selection and find Cross Tab and select it. Insert it in the Report Header B section at the left hand margin. You will be presented with the Cross Tab wizard and have three entries to make. For the Columns entry select Order.SaleDate. For the rows select the formula @Salespersons Name. In the summarized fields select Total Price. Click OK.

Step 7

Next you need to create the selection criteria for how the records will be chosen from the database. In short, you are going to direct Crystal to select records with Order Placed Dates between the Begin Date and the End Date, which have a status of WIP, BUILT, SALE, or CLOSED but not voided. The actual code for this is shown below: //(You can use the wizard for this but the date selection requires some manual code entry. We do it this way because if you use the Between option for the dates, you must set the date as one day less and one day more than the period you want included to capture all the records.//

not ({Order.StatusText} in [“Voided”]) and {Order.SaleDate} >= {?Begin Date} and {Order.SaleDate} Go to the Preview Report command to view the results of your code. You will be asked for the beginning and ending dates and can select from Crystal's calendar. With these parameters, when you call this report from within Cyrious, you will have to enter the dates in the mmdd/yyyy format. (Cyrious has a way to access the calendar selection from within Cyrious but that requires a little more complicated coding which we have chosen to skip in this example since this works well.)

  1. For those who would like to use the ASG report as a template, it is attached here.annual_sales_by_salesperson.rpt

Source Contributor: steve gillispie, Acorn Sign Graphics Date: 12/20/2009 Version: Control 4._

You could leave a comment if you were logged in.