Join Curt Frye for an in-depth discussion in this video Adding case statements to formulas, part of Crystal Reports 2013 Essential Training.
In the previous movie, I showed you how to use the if then structure to decide which of several sets of instructions follow based on a report value. In this movie, I will show you another way to select a set of instructions to follow by using the case structure. I have a list of call data for a number of cities. And what I'd like to do is display one of three possible values. That would either be high volume for values above 4,000, moderate volume for values from 2,001 to 4,000 and low volume for any number that is under 2,000.
To create a new field that displays those values I need to create a formula. So I'll go over to the Field Explorer, and if you don't see the Field Explorer, just go to the standard toolbar and click the Field Explorer button. But once it's displayed, at the right edge of the program window, click Formula Fields, and then on the Field Explorer Toolbar click the New button. Then, in the formula name box you need to type a name for your new formula.
And I'll call it Volume Case. And press enter. Doing so displays the formula workshop. And my cursor is flashing in the formula construction area, which is where I need it to be. The first word for a cased statement is select. So I have S-E-L-E-C-T. And then a left parentheses. So basically what I'm asking Crystal Reports to do, is select from a number of cases. So I have select, and I need to identify the field that I'm basing the selection on.
And that is the number of calls. So, I'll go up to the report fields area and if report fields for some reason wasn't expanded, then just click the expand control next to report fields and you should see it. But here's the field I want. Sheet one calls. I'll double click it. So, I have Sheet1_Calls and I'll type a right parenthesis to close that. Now I'll press enter to move to the next line and then I'll press the spacebar twice. Adding two spaces isn't strictly necessary.
Of course the reports will understand what you mean when it looks at your formula code. But adding spacing and returns and other items that help readability, make it easier for you and your colleagues to understand what you're doing. Okay. So, I'm in my select statement, so I need to define my first case and that is Case is, then a space and less than or equal to 2000, and a colon. So, what do I want crystal reports to do for any value less than or equal to 2,000? I'll press Enter and then I press the Spacebar four times.
Again by indenting a little bit further, I understand or at least leave a clue for myself that what I'm about to do happens if the case is less than or equal to 2,000. So I will consider that, low volume, and low volume is a string, so I have it in double quotes. And I'll press Enter twice, two spaces, and I can define my next case. Case 2001 to 4000, then a colon.
Return, four spaces. And I'll call that Moderate volume. And, again, Moderate volume is in double quotes. Enter twice, two spaces. Case is greater than 4,000. because I already have 2,001 to 4,000 inclusive. So that means, if I were to have two cases where the values of 4,000 overlapped, then that could create an error. But in this case, I want to look at values greater than 4,000, so I'll press Enter, maintain my spacing, double quote, and I'll call that High Volume, and a double quote again.
So just to review, I look at three cases, one where the value is less than or equal to 2000, for the number of calls if that's the case, print low volume. If it's between 2001 and 4000 inclusive, that's moderate volume and if it's greater than 4000, then that's high volume. That's all you need to do. So I'll go ahead and click Save and Close and my new formula field appears here in the field explorer. I'll switch over to design view. So I have a little bit more control when I add my field, and I'll drag the volume case formula field to the details area next to calls.
I'll drop that there. And you can see that I also have a field heading for Volume Case. So I'll click the field header, and press the down arrow key a couple of times to bring it to down into the group header section. And I'll change its formatting by right clicking the Calls field heading. And then from the shortcut menu, click format painter and then i'll move the mouse pointer over volume case and this is the volume case header, and when the mouse pointer changes to an eye bar with a paint brush i'll click, and crystal reports applies the formatting that I copied.
One thing to notice is that the volume case field here, has an @ sign in front of it, and that indicates that it's a formula field. That's all I should need to do so I'll switch back to preview, and you can see that in fact I do have high volume, low volume, and moderate volume displayed in the formula field in the proper places.
Author Curt Frye shows you how to define and manage reports, create and delete sections, and summarize and visualize data with charts. Plus, learn how to focus your analysis with selections, subreports, and cross tabs; export report data; and connect to external data sources.
- Creating a report
- Linking to a data source
- Adding fields to a report
- Creating sections in a report
- Sorting, grouping, and summarizing data
- Applying a template
- Applying formats to date fields and other report controls
- Managing images and drawing objects
- Creating charts
- Selecting records
- Using formulas
- Adding subreports
- Exporting report data