The ToText function, used with times, lets you add an am or pm string to your times. You can even pass your own am/pm strings if you don’t want to use the default AM and PM.
Here’s a look at how it works. (Where timefield= 4:15:00 p.m.)
|
Example |
Result |
|
ToText({timefield}, “HH:mm:ss tt”) |
“16:15:00 PM” (default) |
|
ToText({timefield}, “HH:mm:ss tt”, “morning”, “night”) |
“16:15:00 night” |
|
ToText({timefield}, “hh:mm:ss t”, “am”, “pm”) |
“04:15:00 p” |
On occasion, I will need to create a report showing only events that occur in the current week. (For example, all deliveries scheduled for the week.) Note: This assumes you are using Sunday as the first day of the week.)
Here’s how to set this up:
I frequently find I need to create reports that show if a date field falls on a specific day, for example if an event occurred yesterday. Here’s how:
Create a new variable called WeekStart and enter the formula:
CDateTime (CurrentDate-(DayOfWeek(CurrentDate)+1),CTime(00,00,00) )
This uses a few different functions to calculate the date of the first day of the week. It starts with the current date (say, Friday, 9/10.) Then it uses the DayOfWeek function to get the value for the day of week (Sunday is 1, Saturday 7, so Friday is 6.) Subtracting 6 from 9/10 gives us 9/4 which is the date of the previous Saturday. We add 1 to get Sunday.) Finally, It then sets the time to 0:00:00 which is the start of the day (or midnight of the previous night.)
CDateTime (CurrentDate+(7-(DayOfWeek(CurrentDate)),CTime(23,59,59) )
This uses a few different functions to calculate the date of the last day of the week. It starts with the current date (say, Friday, 9/10.) Then it uses the DayOfWeek function to get the value for the day of week (Sunday is 1, Saturday 7, so Friday is 6.) Subtracting 6 from 7 gives us 1. Add that to 9/10 and we get Saturday’s date, 9/11. It then sets the time to 23:59:59 which is just before midnight at the end of the day.
{Event_Date} in {@WeekStart} to {@WeekEnd}
I frequently find I need to create reports that show if a date field falls on a specific day, for example if an event occurred yesterday. Here’s how:
Create a new variable called YesterdayMin and enter the formula:
CDateTime (CurrentDate-1,CTime(0,0,0) )
This variable takes the current day (today), and subtracts 1 to get yesterday. It then sets the time to 0:00:00 which is the start of the day (or midnight of the previous night.)
CDateTime (CurrentDate-1,CTime(23,59,59) )
This variable takes the current day (today) and subtracts 1 to get yesterday. It then sets the time to 23:59:59 which is just before midnight at the end of the day.
If {Event_Date} in {@YesterdayMin} to {@YesterdayMax} then "yesterday" else "not yesterday"
You can then use the results of this formula to group or select your records.
The above works well if your report runs every day. However, what if the report doesn’t run on the weekend, and you need to include Saturday and Sunday events in your Monday report? You just need to modify your YesterdayMin variable to include multiple days if it’s Monday. You can do this using the DayOfWeek function which evaluates the current day of the week (1 is Sunday, 2 is Monday, and so on.)
Change YesterdayMin to have the following formula:
If DayOfWeek (CurrentDate)=2 then CDateTime (CurrentDate-3,CTime(0,0,0) ) Else CDateTime (CurrentDate-1,CTime(0,0,0) )
For our next example, we’re going to create a report that lets the user select one or more text values to filter on. We will create a parameter that lets the user select one or more Customer to show in the report.
Follow these steps to create a new parameter:
Next, modify your selection formula to find only those records that have a customer selected by the user.
Displaying the parameter in the title is a little more complicated when you let the user select multiple options; this turns the parameter into an array and means you’ll have to use the Join function. (See my earlier post for more details.)
Enter the following into your ReportTitle formula:
‘Orders placed by ‘ & Join ({?Customer}, ‘, ‘)
Preview your report:
In this example, we will create a report that selects records based on a date range entered by the user.
In our example, we’ll use a report that shows customer orders, and we want to limit our report to orders entered in a selected date range. This means we’ll need two date parameters: a start date and an end date.
Continue reading ‘Parameters: Creating a Report that Selects Records on a Date Range’ »
Parameters can help you create flexible, powerful reports. This series will introduce parameters and explain how to use them.
A parameter is a variable that lets your report users select the value of at run time. For example, you could set a parameter that lets them select the date range, or to select a record number, or the specific staff member to review.
Parameters let you create more flexible reports–you don’t have to hard-code data, you can let the user select what they want to see when they run the report.
Parameters are defined as follows:
| Name | The name of the field. When Parameters appear on your Crystal Reports in design view, the field name is preceded by a question mark.) |
| Prompting Text | The text the user sees when prompted to enter the data. |
| Value Type | The type of data stored in the parameter. (Boolean, Currency, Date, DateTime, Number, String, or Time) |
| Allow Multiple Values | If you select this option, the parameter will be treated as an array. This is useful, for example, if you are letting users select specific salesmen to review; they don’t have to run the report once for each salesman, they can enter all salesmen at one time.
If you do select this, you will need to treat your parameters as arrays (for example, to include the parameter in a title will require you to use the Join function. |
| Discrete Value(s) | Selecting this option lets users enter a single, discrete value. For example, a single date (3/12/10), or a single record number (12332).
If you have also selected Allow Multiple Values, users will be able to enter several discrete values (12332, 12456, 12743). You can select Discrete value(s) OR Range value(s) OR Discrete and Range value(s) |
| Range Value(s) | Selecting this option lets users enter a range of values. For example, a date range (3/12/10-4/12/10), or a range of record numbers (12332-12338).
If you have also selected Allow Multiple Values, users will be able to enter several ranges of values (12332-12338, 14857-15000). You can select Discrete value(s) OR Range value(s) OR Discrete and Range value(s) |
| Discrete and range value(s) | If you have selected Allow Multiple Values, this option will become available.
Selecting this option lets users enter a combination of discrete and range values. For example, (12332-12338, 14435). You can select Discrete value(s) OR Range value(s) OR Discrete and Range value(s) |
| Set default values | This option lets you enter default values for the parameter. For example, if you are using a parameter to determine what field to group on, you may define the choices available to the user.
On the other hand, if you are asking users to select a date range, you probably don’t want to give them default values. |
| Allow editing of default values | If this is checked, users can edit the default values, or enter their own. If it is not checked, they are limited to the values you have entered. |
I usually try to format my reports so that a record has a clearly delineated start and end (either a line below, or shading the group header.) However, it can still get confusing if a record is continued, especially if the record contains a lot of text. Here is a quick way to use a formula to add a “Continued on Next Page” blurb when a record is broken by a page break: Continue reading ‘How to Display when a Record is Continued on Another Page’ »
If you want to display a checkbox on a report, you can create a formula to show an unchecked box or a checked box (using the desired WingDing character) depending on the values of the field.
You will need to create a new formula, with an appropriate name (for example, Checkbox) that defines what character code to show, depending on the value of the field. For example, if you want to show a checked box if the field MyField is true, and an unchecked box if it is False, you would create: Continue reading ‘Adding a Checkbox to a Report’ »