Report building using Crystal Reports

There are three types of joins available in Crystal Reports:

Inner joins

Inner joins are useful when you wish to return only those records for which a specific condition is true, such as only those schools that have enrolled students, or when you know that a match is guaranteed, such that fact that all students must have a physical address.

The inner join is the default join in Crystal Reports.

Left outer joins

Left outer joins are useful when you want to view all records of a specific type, with their associated characteristics, such as viewing all buildings in the district and their associated rooms, even if rooms have not yet been created. You can use this type of report to check for completeness of your data.

Right outer joins

Important: Right outer joins are illegal for reports being uploaded to PowerSchool SMS, and will not pass the validation process. If you need a right outer join to return the data you require, you need to re-assess the mapping of your report.

Link types used with joins

In most cases, you will be linking tables on an ID field, which means that only equal values are relevant. By default, all joins are equal.

However, in cases where you wish to link on a different type of field, you may wish to refine the nature of your joins by selecting a link type.

In addition to the join types in Crystal Reports, there are other conventions related to connecting views that are required for reports being uploading to PowerSchool SMS.

Directional links

All of the connections in your report must move in a single direction outward from your root view.

When connecting views, you should organize them in Crystal Reports so that they flow logically from left to right. Any joins that do not move in this direction will cause the report to be rejected when it undergoes the validation process at upload time.

Aliased views

If necessary, you can add the same view to a report more than once. Crystal Reports creates an alias for the additional version by adding _1 to the end of the view’s name. You can change the name of the aliased view to something more distinctive, if required.

The ability to add aliased views is particularly useful when one view accesses multiple types of the same information. For example, if you want to show a student’s physical address and mailing address on the same report, you must add the view for addresses twice. You then link the student’s record to one instance of the view on the reference ID for physical address, and to the other instance on the reference ID for mailing address.

Note: In this case, consider changing the name of both instances of the view to reflect their respective natures.

Intersection views and reference IDs

There are two ways to link views:

=

Intersection views are special views created to link views in a one-to-many relationship. The intersection view contains no actual fields – just the ID fields of the two views it joins.

=

Reference IDs are special fields that reference the IDs of other views. The reference ID is used to link views in a one-to-one relationship.

To illustrate, use the example of a simple report that shows the name and enrollment status of all of the students in a particular school.

The first step of the process is to determine the view that the entire report will be based on. This is known as the root view. All of the links between views move outward from this root view. For the example, the root view is the view for the school, which will then have a collection of students associated with it. The name of our root view in this case is DA_School, which has an ID field of SchoolID.

Because the school must have a collection of students attached to it, the next step is to look for a view of students related to a particular school and the appropriate intersection view. In this case, that view is called DA_SchoolStudent, and the intersection view is called DA_School_I_Students.

To link your views [Figure 323], connect the ID field of the first view (1) to the same field in the intersection view (2), then connect the other ID field key from the intersection view (3) to the related field in the second view (4). The links look like this:

There is then a one-to-one relationship between a student and his or her enrollment status [Figure 324]. Identify the reference ID in the DA_SchoolStudent, which is EnrollmentStatus#EnrollmentStatusID. Add the view DA_EnrollmentStatus to the report and the link from the reference ID in DA_SchoolStudent to the ID field, EnrollmentStatusID, in the other view. Then, you join the QUIDs for both views. The link looks like this:

Query ID connections

In addition to IDs and reference IDs, each view contains a Query ID, or QUID, used to uniquely identify the version of a report being run by a particular user. This ensures that if multiple users are running the report at the same time, a user will receive the appropriate data in their version of the report.

In order to ensure that a single ID applies to all views in the report, you must link the QUIDs of all of the views in the same order as the links between IDs, intersection views, and reference IDs were made.

Use the naming convention ###_ROType_Name for your parameters in order for the parameter to be rendered correctly when the report is uploaded to PowerSchool SMS. The format is broken down as follows:

=

###: Enter a three-digit number for the print option. This number is used to determine the sequence in which the print options appear in the Report Options dialog.

=

ROType: The code that designates the report option type you want to use for the print option, which will determine how it will appear in the Report Options dialog. Select from the following:

=

ROC – checkbox. Use this to specify an option that is either on or off.

=

ROR – radio button. Use this to require users to select one of two or more related options.

=

ROV – list of values. Use this to require users to select one from a dropdown.

=

ROL – label. Use this to add text to the print options, such as a heading to group other options.

=

ROT – text box. Use this to require users to enter text values, such as names, for use in the report.

=

ROD – date. Use this to have users specify a single date for use in formulas in the report. This enables users to select today’s date or to specify a different date.

=

RODR – date range. Use this to have users specify a date range.

=

Name: A unique name for the parameter.

The following Types apply to the PowerSchool SMS naming convention:

=

Boolean – select this for checkboxes (ROC).

=

String – select this for radio buttons (ROR), lists of values (ROV), labels (ROL), and text boxes (ROT).

=

Date– select this for dates (ROD) and date ranges (RODR).

You can create pagination options based on the parameter fields that you created.

To set up paging based on a parameter:

1

From the Report menu, click Section Expert. The Section Expert dialog appears.

2

In the Sections list, click the section to which the parameter applies.

3

On the Paging tab, select the type of option you want to apply based on the user’s selection. Next to the option, click . The Formula Workshop dialog appears [Figure 326].

4

In the left pane, expand the Report Fields tree to view the parameter field you created.

5

Double-click the parameter to add it to the formula and construct the rest of the formula as appropriate.

6

Click Save and close.

7

Click OK.

Crystal Reports comes with a predefined set of special fields that you can use to add document information to your report, such as page numbers, dates, file author, report title, and page count.

1

In the Field Explorer window, click to expand Special Fields.

2

Click and drag the special field to the appropriate location on the Design tab. Special fields are usually added to the Page or Report Footers.

3

From the File menu, click Save.

Date and time fields are inserted in a default format. If you wish to use an alternative format, you can change the field options.

1

From the File menu, click Options. The Options dialog opens.

2

Click the Fields tab.

3

Click Date, Time or Date and Time. The Format Editor dialog opens.

4

Click the tab for the format you want, click a style, and then click OK.

5

Click OK again.

6

From the File menu, click Save.

If you cannot find the field you want under Special Fields, you may also want to add text objects to your report.

1

Click to insert a text object. An empty object frame appears in the upper left corner of the Design tab.

2

Position the text object where you want it to appear in the report, and click once. The object frame is placed in the report and is selected for editing.

3

Enter the text.

4

From the File menu, click Save.

Figure 327: Text field

Text objects in your report can be formatted any way you want. This is useful for distinguishing the headings for data from the data itself, or for making the report title more visible.

1

Perform one of the following:

=

To revise the text, double-click the text object and change the text.

=

To format the text, select the text in the text object. Right-click the text and choose Text Formatting. In the Text Format dialog, make the changes you want. Click OK.

=

To format all of the text in the object, right-click the text object and choose Format Text. In the Format Editor dialog, make the changes you want. Click OK.

2

From the File menu, click Save.

You may want to move, resize, or delete the fields and text objects you have added to your report.

1

Click the border of the field or text object. If you are currently editing the text, you must click outside the object before clicking the border. The pointer changes to .

2

Perform one of the following:

=

To move the object, drag it to a new location.

=

To delete the object, right-click on the object and choose Delete.

=

To re-size the object, hover your cursor over the border until the pointer changes to , and then drag the handle.

3

From the File menu, click Save.

Margins, guidelines, and grids are available to assist you in positioning objects in your report.

1

Perform one of the following:

=

To change margins of the page, in the margins of the report, right-click and choose Page Setup. In the Page Setup dialog, change the margins. Click OK.

=

To add guidelines, non-printing lines that you can use to align, move, and resize objects with precision, on the ruler, click the spot where you want a guideline.

=

To show grids, an underlying network of lines for aligning fields and graphic, in the margins of the report, right-click and choose Guidelines > Design.

=

To snap text to grid, automatically aligning any fields you insert or resize to the nearest grid, in the margins of the report, right-click and choose Snap to Grid.

2

From the File menu, click Save.