Subtitles of the Movie

In this movie we're going to go over the solution to Cross-Tab Problem 1. From my Crystal Reports Start Page, go ahead and click on the Cross-Tab Report Wizard. From here we're simply going to connect to our Data Source. If you haven't done so already, go ahead and connect to your Crystal Training DB. From here we need the Employee, the Office, and the Revenue Transaction table and go ahead and click Next. From here we're going to go ahead and Delete this link because we need to create our own. From here I'm doing a simple rearranging; don't have to do this but I find it helps to know what my main table is and go from my main to my support tables, if you will. From here I need to connect Revenue Transaction Site by clicking and holding down and dragging it to OfficeNum of the Office table. From here I'm going to scroll down to the Office Manager field and connect it to the Employee ID field of the Employee Table, and that's it. Now I click Next. From here we were supposed to pull in the Employee Full Name. You can do this manually but the hint said go ahead and use a Formula. For right now I'm going to go ahead to the Office table and pull in Division first, then I'm going to pull in Office Name, then I'm going to go to my Revenue Transaction table and pull in the Rev Date up here in the column. I'm going to highlight that and instead of for Each Day I'm going to show for Each Week. In the Summary field, I'm going to go ahead and drag in Revenue Amount once, and I have the Sum of the Revenue Amount and we'll go ahead and drag it in again. But this time I'm going to highlight it and choose Average. You can also simply drag in Employee First Name, Middle Name, and Last Name. Go ahead and click Next. Next. And there's no Filter Finish, let's go ahead and hit Finish. Notice how this report has broken it down into its groups, very largely so. Now for the last group, which is the Employee Name, we don't need all those subtotals, so what we're going to do is right-click, go to our Cross-Tab Expert, go to my Customize Style, and I'm going to scroll down here, and I'm going to click on Employee Middle Name and Suppress the Subtotal. Click on Employee First Name, Suppress the Subtotal, and click on the Office Name and Suppress the Subtotal. Then I'm going to press OK. And here we are. Not bad, eh? The point of the Cross-Tab, of course, is not to make it as complicated as possible, but enable a user to compile large amounts of data rather quickly. Now I do have to warn you. The Cross-Tab reports functions pretty much in the same way as a regular Crystal Report. The more records you have to pull across onto your local PC for your processor to crunch the longer this will take. Large data sets are always tricky anyway, but for most uses Cross-Tabs can outpace a regular Crystal Report 99.999 percent of the time. Now whether they're beautiful and exactly what the report requester's looking for, of course, is another story. The last thing I wanted to show you came in the hint in the definition of Cross-Tab Problem 1, where it was hinted use a formula link to create the Full Name of each employee. The quick way, I just dragged in each of the fields and then suppressed the subtotals, but now we're going to do something a little bit different. We right-click on our Cross-Tab, go to our Cross-Tab Expert, click on New Formula, and type in Full Name. From here we press OK, and from here it's actually pretty simple. I'll drag in First Name, I'll put the plus sign and I'll put a single quote space single quote plus and I'll go ahead and drag in their last name. I'll check my work; no errors are found Ð I'm simply concatenating the strings together with a blank space in between so they're not all jammed together. It makes it a little easier to read. You can just as easily put Employee Last Name comma First Name, but this will do for now. Let's go ahead and Save and close. Now, instead of each separate field which required me to suppress the subtotal, I'm going to go ahead and drag in our Formula Field Full Name and press OK. From here we have a single field as opposed to three. It makes it a little bit easier to read and a little easier to deal with. If you have any questions about this, please review the Formula Section of this course.