Open topic with navigation

Uploading Custom Reports

All reports created using the data access views must be uploaded to PowerSchool SMS to be run. During this process, the report is validated against the reporting framework to ensure that the joins between views are valid.

Because of the flexibility of the reporting framework, you can share reports with other districts and schools by distributing the .rpt files and .sql scripts used to create them. In a similar fashion, PowerSchool SMS can send out newly developed reports without waiting for a new release. The upload process for all reports created using the data access views is the same, regardless of who created the report.

The process of manually uploading individual reports involves the following steps:

Step 1: Run SQL Script

Important:If your report uses report extensibility to perform complex calculations, you must execute the SQL script provided prior to uploading the report to PowerSchool SMS. You must run the SQL scripts for both the main database and the report writing database (ending in _DA). If your report does not use report extensibility, skip Step 1.

Running SQL script:

1

On the PowerSchool SMS database server, from the Start menu, click Programs > Microsoft SQL Server > SQL Server Management Studio. The Connect to SQL Server dialog appears.

2

Enter the Login Name and Password for the owner of the PowerSchool SMS database, and click OK to launch the SQL Query Analyzer.

3

In the database dropdown, select the PowerSchool SMS database. For example, select CSL_SMS. The dropdown is typically set to Master.

4

From the File menu, click Open. Navigate to the SQL script that you need, and click Open.

5

Click the Execute Query button to run the script on the main database. A message displays to indicate that the script ran successfully.

6

In the database dropdown, select the PowerSchool SMS Data Access (DA) database. The name of this database matches the name of the main PowerSchool SMS database, but ends with the suffix _DA. For example, if the main PowerSchool SMS is called CSL_SMS, the Data Access database is called CSL_SMS_DA.

7

Click the Execute Query button to run the script on the _DA database.

8

Close SQL Query Analyzer.

Step 2: Upload Report File

After running any required SQL scripts, you can upload the report files. Unlike in the previous step, you do not need to be on the machine running the PowerSchool SMS database. You upload reports using the PowerSchool SMS interface.

Uploading a report file:

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Reports. The Customize Reports page appears.

3

Select the report to be uploaded:

=

If the report has already been added to the Report List, click the name of the report you want to upload.

=

If the report has not yet been added to the Report List, from the Actions menu, click Add Report.

4

Under General, enter or change the following information, as necessary:

=

Report Name: The name that displays in the report list.

=

Description: The description of the report that displays in the report list.

=

Category: , perform one of the following to group reports together on the Customize Reports and Report List pages:

=

Click Select and select an existing category from the dropdown.

=

Click New and enter the name of a new category.

=

Report Weight, select one of the following to ensure that the report is sent to the appropriate server for processing:

=

Light: To have the report run by a Lightweight Report server. This type of server manages reports that do not require significant system resources.

=

Heavy: To have the report run by a Report server. This type of server manages reports that require significant system resources.

5

Under Report File, click Browse and navigate to the appropriate report file, and click Open.

If the report has been created using not_allowed joins (e.g., if the joins are not unidirectional, or if views are linked on the incorrect field), the report will not upload. You will receive a system message outlining the error in the report. If you receive an error message, you must fix the report in Crystal Reports and then attempt to upload the file again. Note: Only the first error the system finds will display; any subsequent errors will display only after the first issue is resolved.

6

If you want to change the default permissions for the report, under Permissions , identify the base roles and roles that can Edit or View the report [>>].

7

Click OK.

Step 3: Set Report Filters

The Report Filters page allows you to specify filters for your report. The options available on this page are dynamically generated from the views you selected when you created your report in Crystal Reports.

In each view, you can select up to five of the available fields to act as filters for your report. When users run the report, these filters appear on the Reporting Options page, where they can enter specific values for the default filters or add other filters as they choose.

Note: You cannot set filters on dynamic properties in PowerSchool SMS. If you require your report to filter on a dynamic property, as in the case of viewing only active students, you must have added this filter in Crystal Reports prior to uploading the report to PowerSchool SMS.

Setting report filters:

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Reports. The Customize Reports page appears.

3

Select the checkbox next to the report.

4

From the Actions menu, click Edit Filters. The Report Filters dialog displays [Figure 335]. By default, all of the panels are collapsed.

5

Expand the panels that contain the filters you need to set, and then select the filters from the dropdowns.

6

Click Save, and then click Close.

Figure 335: Report filter

Step 4: Test run the report

Once you have created a custom report using the new data access views, you should perform a test run of the report using various filters and user IDs to ensure that the appropriate data is returned.

When you run the report, all of the views that were used to create the report are available for filtering. If a field in a view was selected as a filter when the report was uploaded to PowerSchool SMS, the panel for that view is open, and the filter is displayed on the Basic tab. However, you are not required to use only the filters chosen at design time. If a filter already exists for a view, you can add more filters for that view on the Detail tab. You can also expand the panels for other views and add filters for them.

Running the report:

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Reports. The Customize Reports page appears.

3

Select the checkbox next to the report.

4

From the Actions menu, click Run Report. The Report Options dialog appears. Note: If the message "No template was found" displays, the template for the report has not been uploaded. Close the screen and upload the report package for the current version of PowerSchool SMS. See Step 2: Upload Report File [>>].

5

Under Filters, to run the report as designed, select an operator and a value for each Search by dropdown or leave the filters blank.

=

To add a filter:

=

If the view already has a filter, click the Detail tab.

=

If the view does not already have a filter, expand the panel for the view. In one of the available dropdowns, select the appropriate filter, the operator, and the value. Click Add Criteria.

6

Under Scheduling Options, to run the report immediately, select Run now.

=

To run the report once at a specific time, select One time, and then enter the date and time for the scheduled task.

=

To run the report on a recurring schedule, select Recurring, and then enter the start date, start time, and interval for the scheduled task.

7

Under Output Options, select the appropriate type of output from the available options:

=

Adobe Acrobat (*.pdf)

=

Crystal Reports (*.rpt)

=

Microsoft Excel (*.xls)

=

Microsoft Word (*.doc)

=

Rich Text (*.rtf)

8

Click Run Report.

=

The Report Status dialog displays status information to indicate what stage the report is at as it is being generated. If you require, you can cancel the report while it is in the queue.

=

If the report was scheduled to run immediately, it is displayed in a browser in the specified format.

=

If the report was scheduled to run at a specific time, the task is sent to the Task Manager. Once the report has run, it can be downloaded by clicking the Reports links under Scheduled Events on the home page.

Downloading custom reports

Download any report so that you can modify it.

Download the schema

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Pre-4.2 Reports. The Customize Reports or Customize School Reports page appears.

3

Under To create a new report, under Step 1, click Download schema.

4

Click Save As.

5

Navigate to the folder where you want to save the file, and then click Save.

Download custom report

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Reports. The Customize Reports or Customize School Reports page appears.

3

Select the report and select Download Report Template from the Actions menu.

4

Click Save .

5

Navigate to the folder where you want to save the file, and then click Save.

Download the Pre-4.2 reports

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Pre-4.2 Reports. The Customize Reports or Customize School Reports page appears.

3

Under Step 3, click School Report List. The School Report List page appears.

4

Select the report. The Edit Report page appears.

5

Under Report File, click Download next to the report file. Note: If the built-in report has already been customized, there will be two files from which to choose, the original built-in report and the customized version. Ensure that you select the correct version for your purposes.

6

Click Save .

7

Navigate to the folder where you want to save the file, and then click Save.

8

If the Download Complete dialog displays, wait until it says Download Complete, and then click Close.

Deleting custom reports

You can delete custom reports from PowerSchool SMS, assuming that your user account has Delete permissions for that report.

Deleting a report means that it is no longer available for users to run, and it is no longer on the PowerSchool SMS database server.

To delete a custom report:

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Reports. The Customize Reports page appears.

3

Select the checkbox next to the report.

4

From the Actions menu, click Delete Report.

To delete a Pre-4.2 custom report:

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Pre-4.2 School Reports or Customize Pre-4.2 School Reports. The Customize Reports page appears.

3

Under To modify a built-in report, click District Report List or School Report List. The District Report List or School Report List page appears.

4

Select the report and click Delete.

Reverting to an original report

If you have modified a report and added it to PowerSchool SMS, you can later choose to stop using the modified report and start using the original report again.

When you revert to the original report, the modified report remains on the PowerSchool SMS database server unless you clear it. The modified report is not used in PowerSchool SMS, but it is available for later use.

To revert to an original report:

1

On the control bar, under Admin, click District Setup or School Setup. The District Setup or School Setup page appears.

2

Under Reports or Customization, click Customize Pre-4.2 Reports. The Customize Reports or Customize School Reports page appears.

3

Under To modify a built-in report, click District Report List or School Report List. The District Report List or School Report List page appears.

4

Select the report. The Edit Report page appears.

5

Under Report File, set Use report file to Built‑In.

6

To remove the modified report from the PowerSchool SMS database server so that it cannot be used again, click Clear.

7

Click OK.

Figure 336:

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.