RETURN TO TABLE OF CONTENTS Designing A Crystal Report You've heard how powerful and flexible Crystal Reports is for all sorts of reports. Unfortunately, so has your boss, and his boss, and all the way up to the highest muckity-muck. They will ask you for all sorts of reports, with no more detail than: ``A profitability report " on the latest new product ``A forecast" of sales based on previous new product introductions ``A return on investment" projection for a possible capital expenditure ``Efficiency figures" on the new hires in your department ``The latest cost figures" from a new software package ``A P&L of the Widget Department" ``Commission reports"   If you create one effective report, you be asked for more reports, with fewer details about what is needed. And... of course... your special bonus:  a shorter deadline! This article is a real-world illustration of how to: Layout the Report Find the Data Pick the Data Links for report that satisfy everyone, including yourself.   Setting the SceneIt's the parking lot at 5:30 pm. You're headed home from a long, hard day. Your boss, Alfred, runs up to your car and excitedly gestures for you to roll down your window.   Alfred:  I'm glad I found you. I need another report. You:  OK. Alfred:  I want a sales report by Sales Rep.  It should show sales for each rep's customer, so I can see how much they bought last month. (You dig around in your car, find a napkin from a drive through, and scribble notes on it.) You:  Do you want it monthly, with only last month's stuff or do you want more than one month on the same report? Alfred:  We just want the sales for the month before. I don't think we want more than one month's of sales activity on the report.  See, we're using this report to figure the sales rep bonuses.  You:  You want me to include the bonus calculation on the report? Alfred:  Yeah, right. That's the whole idea. You can do it, right? You:  How are the bonuses calculated? Alfred:  Well, it depends... You must take into account the rep's quota and whether or not he made it.  If he didn't make it he doesn't get any bonus. If he doesn't make quota, he just gets his base pay, no bonus, no penalty.If he makes quota, the bonus is a percentage of sales above quota.  Now, here's where it gets a little tricky. The percentage used to calculate the bonus changes by how much over quota the rep went.If the rep got above quota by as much as 10% then the bonus percentage is 5% of the above quota sales dollars.  If they sold between 10% and 30% above quota then he gets at 10% bonus.  If he sold more than 30% over quota he gets a 20% bonus. If he does double the quota or more he gets 20% plus an extra $5,000 Achiever's Award. Got it? (Alfred jogs happily off to his car.) You:  I'll get on it first thing.  Goodnight. (Kicks tire. Gets in car.) Layout the Report The report should include formatting decisions and define any necessary formulas.  Get this approved before you go much further. It will save you buckets of time. Don't worry about where the information in this report is coming from, just use descriptive terms for the data. You don't need data file names now. Just layout what needs to be included in a clear and understandable pattern. Design your formulas. Put details about formulas here or on another page. Formula details are noted in black above. If your company uses particular fonts or layout styles, include them in the layout. We assume 1 page per sales rep.This way, the report can be sent with the bonus check. Find the DataThe data file structure that is available for the report is shown below. The 1st field in each table is the key, primary, index field of the file. Now, decide which fields you will need and what data files (tables) have the fields (columns). After the general layout has been approved, and any changes noted, check the layout for what data you need to know from the data base. Divide the required data into what is calculated, and what must come from the files. Sometimes formulas use data that does not print on the report. Include that data in your lists. Remember everything has to breakdown by date, too. Report Data that Come from Files: Sales Rep Name Sales Rep Region Sales Rep Sales Sales Rep Quota Sales Rep Cost of Sales (COS) Customer Name Customer Sales Dollars Calculated Report Data: Bonus Amount % Profit Amount and % Over/Under Quota Bonus Group % and Display String If you have the same data in several files, pick as much data from already used files as possible. After looking at your 1st list and comparing it with the tables that are available to you, you decide to use these File.FIELDs: Sales Rep NameEmployee.NAME for Sales Rep Sales Rep RegionSalesinfo.REGION for Sales Rep Sales Rep SalesLineItem.PRICE added up for Sales Rep for sales this month Sales Rep QuotaSalesinfo.QUOTA for Sales Rep Sales Rep COSLineItem.COS added for Sales Rep for sales this month Customer NameCustomer.CONAME for Sales Rep for sales this month Customer Sales $Sum of LineItem.PRICE for this Sales Rep, totaled by Customer for sales this month Pick the Data Links  Now that you have decided what fields and files you need data from, you must decide the best way to link them. The Groups Go back to your report layout and look at how the data is grouped. There is a Group by Sales RepWe calculate the bonus for one Sales Rep at a time. There is a Group by  Date of sales by this Sales RepAll sales and costs have to be in the desired month. There is a Group by Customer of this Sales RepEach rep's sales $ are subtotaled for each customer. (Can different Sales Reps sell to the same customer, so one customer might be on more than one report?) You can tell by this analysis, that the Sales Rep is the key to the report and should probably be the top of the linkage tree. The Links Now, look more closely at the data tables. Check the key fields for each file. Where do these key fields appear in other data tables? When a key field occurs in another file, you can make a link to that file. Once linked to the file, you can access all data fields in that file. We link from the Sales Rep to the Header and then the Customer file. This avoids any problems we might have, if several reps sell to the same customer. If we had connected from the Header.SLSID to the Salesinfo.SLSID, we might have had difficulty with the sort order.