Subtitles of the Movie

In this movie we're going to go over the solution to Filter Formula Problem 1. From Crystal Reports, click on the Standard Report Wizard from the Start Page, or the Create New Report Icon located in the top left-hand portion of the screen. From here make a Connection, either Direct or ODBC, to your Data Source. Once connected, go ahead and choose the Office Table and the Revenue Transaction Table and click Next. From here I organize my tables a little bit and I'll join Site to OfficeNum and click Next. Next I choose the fields I wish to display, in this case I need to see Office Name from the Office Table, and from the Revenue Transaction Table I need to see Revenue Amount and Revenue Date, and click Next. From here my Group By, as I hinted, Office Name and Revenue Date I need to pull in. Notice from here I can't choose to Summarize Revenue Date by Year or all those functions in Crystal. You have to wait till you're actually in the report. Go ahead and click Next. From here, Crystal's automatically summarized my Revenue Amount Field at both Group levels that I've chosen. I've done all I can do. I can get all the way to the Record Selection, but it really doesn't let me get into the Advanced Formula Record Selection I'm looking to recreate, so I'm going to go ahead and click Finish. I have all my Groups with one slight exception. Let's go to Design view and right-click on Group Header 2, and choose Change Group. From here I needed this instead of each week; I need the Revenue Date expressed as a Year, and press OK. Now when I look in my Preview I have all my data grouped by Office and then by Year. I clicked on my Select Expert and I'm going to go ahead and choose Office Name to start. And the first office I'm going to choose is going to be Northern California, so I'm going to say is equal to, and then use my drop-down box and choose Northern California. I go to Show Formula, and go to my Formula Editor. To start off with, I have a few conditions I need this to meet, so I'm going to put parentheses around the whole thing, put my cursor inside the parentheses and start spelling out my conditions. My Office Name must be Northern California and the Revenue Amount must be greater than 150. For illustrative purposes, I'm going to go ahead and return this down so we can see the Formula in its entirety. And, the next case that it needs to meet is the Revenue Date greater than or equal to Date Time. Now, this is the standard syntax for my Date Time. I start off with the Year, 2007, then go to the Month, which is 01, then go to the day, which is 01, then I go to my hours, then my minutes, then my seconds. From here I'm not quite done. I type in my Or, and I say Office Name equal to Louisiana. Notice I'm using the double quotes to identify strings and text. The conditions for Louisiana also were required to be in parentheses, and in this case, my Revenue Amount times 10 percent point 1, I'm going to put that in parentheses because I need it to be evaluated, will then be compared to 9. From here I'll check my formula. If no errors are found I'll go ahead and Save and close. Press OK again at the Select Expert Window, and Refresh the data. From here I have Louisiana and I have California. From here we're missing one final step, which is the Select Expert, Show Formula, but we go to Group Selection. We'll go ahead and click New, and notice we're going to go down to the Group 2 Revenue Transaction, Sum of Revenue Amount, and select it, and in this case we say it had to be greater than or equal to 500 and press OK. Now, let's go ahead and Suppress the Details, give ourselves a better view. As we can see we have our years listed here, 2006, 2007. Those all met the conditions for being greater than 500 dollars. In addition to the conditions we set for Louisiana and Northern California. As you can see, using the Formula Editor to help you filter can be as complex or simple as you like. It really depends on the exact needs of your report.