Open topic with navigation

Creating a custom export template

To create a custom export template using Views and add it to PowerSchool SMS, you need:

Creating an export template involves connecting to the database, selecting your Views, and linking them logically.

To illustrate the process of creating a custom export template, these sections provide an example of how you would create a sample export that provides the last name, first name, birthdate, gender, and ethnic category for all students in a school.

Connecting to the database

To access the data for an export template, you need to connect to the database. You do this by specifying the server and database name in Crystal Reports, which allows you to use Data Access Views. You then use Views to access and link the appropriate tables so that you can select the fields you need to export from.

To connect to the database:You can do this if your role is District Administrator.[DA]

1

Start Crystal Reports and create a blank report.

2

Under Available Data Sources, click next to Create New Connection. The Create New Connection folder drops down [Figure 376].

3

Click next to OLE DB (ADO). The OLE DB (ADO) dialog appears.

4

Under Provider, select Microsoft OLE DB Provider for SQL Server. Click Next. The OLE DB (ADO) dialog appears.

5

Under Connection Information, enter the following information:

=

Server: The name of the server where the data access database resides. This is specific to your district.

=

User ID: The user ID of a user account that permits you to connect to the data access database.

=

Password: The password of a user account that permits you to connect to the data access database.

=

Database: The name of the data access database, which always ends with _DA. For example, CSL_SMS_DA.

6

Click Finish. The Available Data Sources list now shows the database to which you are connected [Figure 377].

Figure 376: Create new connection

Figure 377: Database Expert

Linking views

Typically, data being exported comes from multiple tables. This means the associated Views must be linked together to ensure all required data is exported. Linking is a drag and drop procedure in which you drag the ID field from one View and drop it on the ID field in another, creating a link between the two.

The first step of the process is to determine the View that the entire template will be based on. This View is known as the Root View. All the links between Views move outward from this Root View.

To ensure your template will work as expected, the following rules must be adhered to while setting up your links:

The following procedures illustrate how you would link Views to create a sample export template to export the last name, first name, birthdate, gender, and ethnic category for all students in a school.

To link views:You can do this if your role is District Administrator.[DA]

1

Connect to the database using Crystal Reports [>>].

2

In the Available Data Sources list, click next to the database name. Note: All views end with _DA.

3

Move each View you want to use for the export to the Selected Tables list and click OK.

For this example export template, you would select the these Views:

=

DA_FederalEthnicCategory

=

DA_School

=

DA_School_I_Students

=

DA_SchoolStudent

The dialog displays a Links tab [Figure 379] that shows a diagram of the Views you chose and the fields they share.

4

Click Clear Linksand click Yes to confirm.

5

Re-order the Views so the links all move from left to right.

For the sample export template, you would arrange the Views in the following order from left to right:

a

DA_School

b

DA_School_I_Students

c

DA_SchoolStudent

d

DA_FederalEthnicCategory

6

Link the views by dragging a field in one View to the field of the same name in another view, depending on the type of information you want. Click OK. The Crystal Reports window displays.

For the sample export template, the links in [Table 92] would need to exist.

Table 92: Sample links

Drag this field	To this field
SchoolID in DA_School	SchoolID in DA_School_I_Students
SchoolStudentID in DA_School_I_Students	SchoolStudentID in DA_SchoolStudent
FederalEthnicCategory# FederalEthnicCategoryID in DA_SchoolStudent	FederalEthnicCategoryID in DA_FederalEthnicCategory
QUID in DA_School	QUID in DA_School_I_Students
QUID in DA_School_I_Students	QUID in DA_SchoolStudent
QUID in DA_SchoolStudent	QUID in DA_FederalEthnicCategory

7

Click Field Explorer from the View menu. The Field Explorer pane appears.

8

Click next to Database Fields and expand each Data Access View you added to the template.

9

Click and drag the fields you want to export from the Database Fields to the Details section on the Design tab in the order you want the information exported.

For the sample export template, you would click and drag the fields in [Table 93] to the Details section:

Table 93: Database Fields

Data Access View	Field
DA_SchoolStudent	LastName FirstName BirthDate Gender
DA_FederalEthnicCategory	Description

10

Click Save from the File menu and save the file in the appropriate location.

Figure 378: Selecting views

Figure 379: Links tab

Figure 380: View

Note: In this example, DA_School is the root view.

www.powerschool.com
Tel: 866-434-6276
Email: smssupport@powerschool.com

Copyright 2015-2016 PowerSchool Group LLC and/or its affiliate(s). All rights reserved. All trademarks are either owned or licensed by PowerSchool Group LLC and/or its affiliates.