You can familiarize yourself with Crystal Reports for Blackbaud by designing a simple report that lists the number of students in each grade level.
First, use Query to group all records with the status Current Student.
1. Select to create a new dynamic, student query. Click OK.
2. Select the filter criteria Status Equals Current Student.
3. Mark the Sort option and highlight Grade Level Now. Click Sort. Sort by Table Order.
4. Highlight Student Name and click Sort. Sort in ascending order.
5. You can select any fields for output, because they are used only for visual verification of your results. Select the minimum number of fields necessary.
6. Select File, Run Query from the menu bar.
7. Save and name the query. Mark Other Users May Execute this Query to allow other users to run but not change your query. They can modify copies of the query only by selecting File, Save As from the menu bar. To protect the original query, unmark Other Users May Modify this Query.
In Export, extract, or copy, certain information from the group of Current Student records to a data file Crystal Reports can read.
1. Select to create a new student export.
2. Choose Selected Records and select the query you just created. Do not mark Load Query Output Fields. (Remember, they are simply used to verify your query results. In this module you select the fields for your report.)
3. Highlight Student Name and Grade Level Now (Short Grade Level) and click Add Field. You can extract fields in any order, though they may be easier to use in Crystal Reports if you choose a practical sequence.
4. Select File, Export from the menu bar. On the Export Format screen, select MS Access Report Writer Database. Mark Print Control Report. Click OK.
5. On the Enter Export File Name screen, enter a file name that does not exceed eight characters plus the three-character extension. Click OK.
6. After processing is complete, print the Custom Export Control Report, which documents the data file’s name and path.
In Crystal Reports, you manipulate your exported data to create a report.
1. Click New Report. Click Custom on the Report Gallery screen.
2. Click Data File. Select the MDB file you just exported.
3. Select Insert, Group from the menu bar. Group by Grade Level Now in ascending order. Click OK. Your final report’s results will appear in grade level order.
4. Select Insert, Database Field from the menu bar. On the Insert Fields screen, place Grade Level Now in the Details section in one of three ways:
* Click on the field name and drag it to the Details section.
* Double-click on the field name and place it in the Details section.
* Highlight the field name, click Insert, and place the field in the Details section.
5. To calculate the number of students in each grade level, select Insert, Summary from the menu bar. On the Common tab, select Count. Click OK. When you print the report, the records appear sorted and grouped by Student Grade Level Now. The number of students in each grade level also prints.
6. Right-click on the gray part of the Details section. Select Suppress (No Drill-Down) from the menu.
7. Select File, Print Preview from the menu bar.
Tips for Using Totals and Counts in Crystal Reports
Having trouble totaling reports in Crystal Reports? These tips should help.
1. To create a subtotal of an amount field, right-click on the field in the detail line and select Insert Subtotal. Select on what to base the subtotal and click OK.
2. To create a grand total of amounts, right-click on the field you want to total and select Insert Grand Total.
3. To underline the totals, right-click on the field and select Format Field. Select Borders. Select single line on top and double line on bottom.
4. To get a total count of records listed on the report, right-click on the field that is not an amount and select Insert Grand Total. Select Count to have the program count each line and give you the total number of records.
5. To get a total count of individual records (such as vendors with multiple invoices), right-click on the Name field, select Insert Grand Total and Distinct Count. This counts items that are the same only once. For example, if ABC Company were listed three times on the list, the program would count it as only one record.
Changing Report Field Properties in Crystal Reports
Have you ever wanted to add or sort on a text field that is a number? The ToNumber function in Crystal Reports can convert a non-numeric field, specifically a text field, into a numeric field when you need to perform arithmetic on the field or sort the field in numeric order. For example, you export Constituent ID for sorting or grouping in a report, but discover that because it is an alphanumeric field, it sorts in alphanumeric order instead of numeric order. To sort or group by this field numerically, convert the field using the ToNumber function.
For the ToNumber function to work properly, the field you want to convert must be populated with numbers only. When using this function, you should first test the value with the NumericText function. If NumericText returns the value TRUE, then you can convert it to a number. If you try to convert a value to a number that is not numeric, the formula produces an error in the report.
The following example uses both functions: If the text string, Constituent ID, can be converted to a number, then convert it to a number else, use zero as the value.
If NumericText ({Constituent.ConstituentID}) Then ToNumber ({Constituent.ConstituentID}) Else 0
With this formula, the Constituent ID field is converted from a text string to a number and may now be used for sorting or grouping in numeric order.
This function might also be used for an Attribute Description or Comment field. The formula would be if the text field, Attribute, can be converted correctly from a text string to a number, then convert it to a number otherwise, use zero as the value.
If NumericText ({Attribute}) Then ToNumber ({Attribute}) Else 0
The field Attribute, originally a text field that contained only numbers, is now considered by Crystal Reports to be a numeric field and may be subtotaled or used in other arithmetic functions.
The combination of the NumericText and ToNumber functions allows you to easily convert report fields. These formulas can be used for any text field providing the values are solely numeric.
Source: Blackbaud
