Subtitles of the Movie

In this movie we're going to cover the solution to Report Exercise 1. From Crystal Reports go ahead and Create a New Report. We're going to create a New Connection and from here you could choose Direct Access or ODBC. From here you choose your Data Source and click Finish. From here we chose the tables we needed. In this case, Customer, Office, Product, and the Revenue Transaction Table, and click Next. From here we clear the links. As has been explained before, Crystal does have an Auto Linking function, but only works if the Field Names to link on are identical throughout all of its tables and its relationships. Now in real life that's not always the case. Next, we pick our kind of Main Table. It's important to have a table from which you're pulling your main set of data. This is usually the Transactional Table, or the table that holds the revenue, depending on the purpose of your report. I put that one first and then just align the rest as I see fit. These supplementary tables don't have to go in any specific order. The next piece here is very simple. We're going to join Revenue Transaction Client Number to the Customer ClientNum field. To join, if you recall, we click and hold down the mouse on the field we want, which in this case Prod Number and we join it to the table of the corresponding field, in this case Product Prod Number. Then we take Site, hold down the mouse, drag it to OfficeNum from the Office Table and release, and that's the joins as we've defined them in the exercise. Go ahead and click Next. Here we choose the fields we need to display and use. In this case from the Customer Table let's go ahead and choose Client. From the Office Table let's go ahead and choose Division. From the Product Table we need both Group and Description. From the Revenue Transaction Table we need Revenue Amount and we need Revenue Date. Then we hit Next. Next is our Group By. Now, according to the exercise we need Revenue by Client, by Product Group, and Product Description, so this is fairly straightforward. We choose Client, we choose Product Group, and we choose the Product Description, and hit Next. This is where Crystal automatically finds a field it can summarize and inserts it into the report for us. In this case this works so we're going to leave it. Go ahead and click Next. We're going to skip this section as well as skip this section. Next, we arrive at the Filter Fields. From here we needed to choose Division, so we pull over Division and instead of is any value, we say is one of. In this case we use the drop-down box to define East and the drop-down box to define West. The last Filter required, we just needed the years 2005 and 2006. So we go ahead and pull that over, highlight the field so we can start modifying it, and in this case, we're going to choose something a little bit different. Kind of left this on your own to figure out but it's fairly obvious once you think about it. I'm going to use the is between function. Is between works great with dates. In this case it's 1 /1 /2005 at 12:00:00AM all the way to 12 /31 /2006 at 11:59:59PM. This is a good little tip for you when you're working with dates and time stamps. If I had just put 12-31-2006 there I would have essentially skipped that entire day for any transactions happening past 12AM on the 31st would be excluded from the data pull. It's something to keep in mind. From here I'm going to go ahead and Finish, and voila! I have a basic report set up by Crystal's Report Wizard. I have my Client, my Product Group, and my Product Description field. From here I'm going to go to my Design view and I'm going to Suppress my Group Header 2, and my Group Header 3. I'm also going to suppress the details and Preview. Now this gives me my Client and then it's Product Group, and the specific Product pertaining to said group. From here I need to do something a little bit different. I'm going to right-click on my Group Header 1, go to my Section Expert, choose Color, and give it some color. This helps break up the groups as you go along. One of the last things that we needed to do was highlight those clients less than 500,000 dollars, or it can be any amount really, it's up to what you're looking for. In this case we're going to find the AHARTS Summary, which is this one here; it's in my Group Footer 1 Field, also GF1, right-click on that field, go to my Highlighting Expert and I'm going to click on New Condition. When this field is equal to Ð no Ð when this field is less than or equal to I put 500,000 and I'm going to choose the Background Color to be red, and press OK. Now as you can see as I scroll down, all those clients with less than 500,000 dollars are highlighted in red. This could mean that I need to check these out or figure something out why. It could also mean I need to adjust my Condition. As, of course, you go down you'll find clients that aren't highlighted because they've met the condition. The PLAZA GAIJUN for example. As for the rest of the format it's really in the eye of the beholder; I'll leave that to you. I can't tell you what's wrong or right any more than I can tell you that red is a better color than blue; it's going to depend on what you're going to use this for and how you want to present it and how you want the eye and the audience, the receivers of the report, to view it. Hopefully this exercise has helped you build a nice base in how to create simple basic reports and kind of open the door on the potential possibilities for you to test certain functionality out and see if it works for you.