Reporting Engine Basics

Below are some basic instructions for use with reporting engine reports on PowerSchool 6. If you're on 5, instead of clicking on Reports, Engine, you'll click on Reports, Reporting Engine:

Add a report -

Click on Reports, Engine, Load One Local Report and then browse and find the file. Reporting Engine files end with the extension of .rpt. Click on Import and once it's loaded you can run it by going back to the Engine main screen.

Run a report -

Click on Reports, Engine, click on name of the report you wish to run, fill out any search criteria, and click on submit. You can check the little boxes on the right side of the report to use the information on that line as the default information for next time you run the report. Some reports will generate the output as another screen, some will send the output to the report queue, and some will ftp the results to another server.

Edit a report -

Click on Reports, Engine, Setup. There you'll see a list of the reports and a line of links for each one. You click on the link to edit that item for the report.

Delete a report -

Click on Reports, Engine, Setup, and then the name of the report under the Report ID column. Scroll all the way to the bottom of the Report ID screen and click on the Delete button.

Export a report -

Click on Reports, Engine, Setup, and then click on the Create link for the report. That will let you save the report as a file that will have an extension of rpt.

Editing a non-sql report

There's a pdf on the PSUG web site (Files, National, Documentation, and click on repenginemanual.html - you'll need to be logged into PSUG to see it) that is a must-have if you want to edit non-SQL reports. It has a lot of good information and goes into each section in more detail than what's below, which are short summaries of the different sections of a report. If you're working with sql-based reporting engine reports, then some of the sections below are not used. For example, the sql statement will usually replace all the queries and order by part. Go here instead for sql-based information - http://www.powerdatasolutions.org/content/sql-based-reporting-engine-exp...

To get to the sections, you go to the reporting engine setup area (reports, engine, setup), and each report will have a line of links to each of these sections:

Report Name/ID – this is where you name the report, choose the table the report will be associated with, add comments for users to read, and other things. You cannot have two reports with the same names and id's, so you'll have to come to this section to rename reports. This is also the section to go to if you need to delete a report - scroll to the bottom of the page to see the delete button.

Parameters – this section may or may not be used depending on the report. Parameters chosen can appear as options on the report, such as a drop down box of teacher names or check boxes for grades. If use parameters is checked, the final output ends up in the report queue. If you add a parameter to an existing report, be sure to add a reference to it in the appropriate query section. Check the pdf mentioned above for descriptions and uses of each paramater.

Template – the output of the report is based on the template. The template is text based and you can only edit it by exporting it and making changes within a text editor. You then import it back in using the template link.

Reports From – this shows the master table the report is based on and is not a link. You change the table using the report id section

Items – the items that appear on the output. They are referenced on the template by their item numbers. They are usually field names (use the table name in square brackets with each field name), but can also reference variables. If you're referencing a table that is not the master table (the one under Reports From), then you must use Sub-Queries (see below) to tie the tables together.

User Vars – non-parameter items that appear as choices for users when they run the report. A common example is a user var on how to sort the report. If you add a new user var, you will need to add a reference to it in the appropriate query, or in the case of sorts, in the order by part. Referenced by adding a u in front of the name of the user var. For example, if the user var name is sort, then the reference would be ~(u.sort).

Variables - used by the report writer to do certain things. Examples include creating a variable to hold school information for each student in the query, creating a variable to be used as a counter, and using variables to create links to student screens and group functions. Variables can be added to the template by creating an Item for them first. Referenced by adding a v in front of the name of the variable. For example, if the variable name is frnnumber, then the reference would be ~(v.frnnumber).

Pre-Query - used to narrow down the search before the main query runs. For example, if you’re searching for information on current students, a pre-query of enroll_status=0 can be run to find only active students. User vars and variables can be used in the pre-query.

Main Query – the main query does most of the work and can consist of a query or several lines under edit items. User vars and variables can be used in the main query. For example, a person could be asked for a date and then have that used in the main query. Table_info commands are often used in queries - see the pdf mentioned above for examples of how they are constructed.

Sub-Queries – sub-queries are mainly used to match up items to other tables. For example, if the main table is students and you want the school attended to appear on the report, you use sub-queries to create an association between the two tables by associating the student table schoolid field to the schools table id field. Once there is an association, any field from that table can appear on the report as an item, or the table can be associated to another table.

Order By – used to sort the results. Often used with a user var and if statements to sort different ways

Lookup Tables – lookup tables can be mini-databases that you set up and use

Create File – used to save a copy of the report to your computer

Save Lists – creates a file showing a list of items, user vars, and variables in the report

Ver. – version of the report, set in the Report ID section