Report Design

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.

The root view for a report varies depending on the type of information you wish to have available in your report. When selecting your root view, ask yourself the following questions:

=

Which view provides the greatest amount of fields required for my report canvas?

=

Which view provides the greatest number of links to the other views required for the report.

=

Which view is the logical owner of all of the other views included in the report?

The answers to these questions will not necessarily be the same view, but they will provide a jumping-off point for further investigation.

For the example, if you are creating a school report that contains mostly Student Demographics information, the view with the greatest amount of fields required for the report canvas is probably the School Student view. This is also the view that provides the greatest number of links to other required views. However, the most logical owner of the other views, at the highest level, is actually the School view (you can tell that the School Student view is owned by the School view by the names). If you intend to include information about the School itself, such as the school name or address, you might consider using the School view as your root view. You can still link from School to School Student, plus you can link from School to School Info for that additional information.

For information on how to find out which views contain which fields and connections to other views, see Report Building Help [>>].

Another factor that influences how you map out your report design is the concept of object hierarchy. In the Database Fundamentals chapter, you read about intersection views. These views are used to connect objects that have a one-to-many relationship, which means that one object (the parent object) can have a collection of other objects (child collection) related to it.

You also read about Owner Object IDs, which can be used to link a child object back to its parents. However, because this is a reference link, rather than an intersection, the same data will be returned as a one-to-one relationship, rather than a one-to-many relationship.

When you connect objects, the best practice is to start with the parent object and end with the child collection. The reason for this is that, when the report is generated, the data is filtered as it moves from one object to another. If you start with the largest objects first, the filtering process is much more efficient, thereby increasing system performance.

To use the same example of School and School Student, the school itself is higher in the hierarchy because it has a collection of students associated with it. If you use the School view first when connecting your views, when the report is generated, it will be filtered first on Schools, reducing the number of student records to be returned. If you use the School Student view first, the report will pull all student records, and then sort and filter them based on the school to which they belong.

When mapping out your report, you must also consider who owns the data you want to include in your report. For example, core demographic information about students is owned at the district-level. However, there is additional information that relates to a student’s demographic at a particular school (homeroom, grade level, etc.) that the district does not have access to.

If you are creating a report that requires information stored at the district level, look for views that reflect that data ownership, such as the District Student view. Conversely, if your report focuses on information owned by the individual schools, choose school-related views.

Occasionally, you may need to include both District-only and School-only information in the same report. In these cases, you can link this disparate information either by linking two views on the Base Object ID or Owner Object ID (for example, District Student is the base object of School Student) or creating a sub-report for one of the types of data and linking them on a shared field, such as the student number. See Report building using Crystal Reports [>>].

Role-level security is a security feature in PowerSchool SMS that limits what information users can view in a report based on the roles to which they are assigned [>>].

For example, a school administrator and a school teacher can have permission to run the same report – this permission is granted when the report is uploaded to PowerSchool SMS, providing the ability to view, edit, or delete the report.

However, with role-level security in place, the report looks different for each of these users. The school administrator can view data for the school(s) to which he or she is assigned. The teacher’s version of the report displays information only for students in that teacher’s classes.

When you are creating custom reports, keep these security measures in mind. Using role-level security can eliminate the need for other filters.

The reporting framework is set up in such a way that all of the views you link together to build your reports are uploaded with the report to PowerSchool SMS and are available for filtering. You can specify default filters when you upload the report; users can also choose their own filters at run-time. See Step 3: Set Report Filters [>>].

For example, if you create a Student Demographics report using the School and School Student views, both of those views – including all of the fields that are contained in the tables referenced by those views – are available when you run the report. From a design perspective, this means that, if you want to filter on a particular field in the PowerSchool SMS database, you must ensure the view that references that field is included when you build the report, regardless of whether you include any fields from that view in the actual report canvas.

The flexible nature of reports in PowerSchool SMS means that, while you can specify what the default filters are for a particular report, you cannot prevent users from changing those filters at run-time. If you wish to ensure that a particular filter is always used for a report, you must insert that filter in Crystal Reports, where it becomes “hard-coded”. See Adding query filters [>>].

In the Database Fundamentals chapter, you read about dynamic properties and dynamic reference IDs, which are used to include values and records that are calculated at runtime. Because of the complexity of these calculations, dynamic properties and views linked using dynamic reference IDs cannot be used to filter a report from within PowerSchool SMS; in other words, the dynamic properties and dynamically-linked views are not carried over into PowerSchool SMS at upload time, as the other fields and views are.

If you want to filter your report on a dynamic property, you must add the filter in Crystal Reports. See Adding query filters [>>].

Like other filter in Crystal Reports, these filters are “hard-coded”, so you cannot change them at run-time. Depending on how you want to filter the report, you may need to create multiple versions of the same report to cover all of the options. For example, if you wanted to filter a report on a student’s active status, which is a dynamic property in the School Student view, you would have to create one version of the report for each status you want to filter on.

1

In the Admin menu, click School Setup. The School Setup page appears.

2

Under Customization, click Customize Reports. The School Report List page appears.

3

From the Actions menu, click Report Schema Help. The Schema Help page appears [Figure 322].

4

In the Schema dropdown, select the schema that represents the group of records you want to view.