Work with Oracle Application Express

Within APEX, the Application Builder module provides access to the pre-built Enterprise Reports delivered with PowerSchool, as well as the functionality to edit these reports and create new ones. Two pre-built applications are available within the Application Builder:

• Application 100, PowerSchool Enterprise Reporting, is reserved for you to save modified Interactive Reports and to create new ones.
• Application 200, PowerSchool Enterprise Reporting, is reserved for PowerSchool pre-built Interactive Reports now and going forward.

All reports created or modified during the preview release of Enterprise Reporting should be imported back into Application 100, PowerSchool Enterprise Reporting.

Only interactive reports created in APEX may be published to PowerSchool as Enterprise Reports. Websheet Applications, Forms, and other pages created in APEX may require read/write access, are not supported, and may not be published in PowerSchool.

In addition, all interactive reports to be published as Enterprise Reports within PowerSchool must use PS_APEX and not PS or any other Schema. PS_APEX provides secure access to PowerSchool data. Reports created using other schema will not be allowed to run from PowerSchool.

APEX is a full featured development environment. In order to learn more about APEX and how to create additional interactive reports that may be published to PowerSchool as Enterprise Reports, refer to Oracle Application Express Documentation Release 5.0, which contains several important user guides including, the Application Express Application Builder User's Guide, the Application Express SQL Workshop Guide, and the Application Express End User's Guide.

Access Oracle Application Express (APEX)

To access APEX, you must navigate to a specific URL outside of PowerSchool. In the preview release, APEX could be accessed using the URL http://(serveraddress)/ords. In order to better manage performance, Enterprise Reporting is now running on the same application server as ReportWorks. A port number has been added to the URL. APEX can now be accessed using the URL http://(serveraddress):8443/ords. If you are self-hosted, this port number may change based on how you have ReportWorks set up in your local environment.

1. Enter http://(serveraddress):8443/ords* in your browser's *Address field. The Oracle Application Express sign in page appears.

2. Use the following table to enter information in the fields:

   Field	Description
   Workspace	Enter PS.
   Username	Enter your APEX username.
   Password	Enter your APEX password.

3. Click Sign In.
   The first time you sign in, you must reset this password.

4. Use the following table to enter information in the fields:

   Field	Description
   Current Password	Enter your current password.
   New Password	Enter your new password.
   Confirm Password	Enter your new password again.

5. Click Apply Changes. The Oracle Application Express home page appears.

Create Enterprise Reports in APEX

Use this procedure to create a new interactive report. For detailed information, see APEX documentation.

There is no need to modify default settings unless otherwise indicated.

1. On the home page, click Application Builder.
2. Click Application 100, PowerSchool Enterprise Reporting.
3. Click Create Page. The Create a Page wizard appears.

4. Use the following table to enter information in the fields:

   Field	Description
   Application	Defaults to 100 - PowerSchool Enterprise Reporting.
   User Interface	Defaults to Desktop.
   Select a page type	Click Report. The next page of the Create a Page wizard appears.

5. Click Interactive Report. The next page of the Create a Page wizard appears.

6. Use the following table to enter information in the Page and Region Attributes section:

   Field	Description
   Page Number	Defaults to a new (sequential) page number.
   Page Name	Defaults to Report1. Enter a name for the page, if needed.
   Page Mode	Defaults to Normal.
   Region Template	Defaults to Interactive Report. Note: Do not change this setting.
   Region Name	Defaults to Report 1. Enter a name for the region, if needed.
   Breadcrumb	Defaults to Do not use breadcrumbs on page.

7. Click Next. The next page of the Create a Page wizard appears.

8. Use the following table to enter information in the Navigation Menu section:

   Field	Description
   Navigation Preference	Defaults to Do not associate this page with a navigation menu entry.

9. Click Next. The next page of the Create a Page wizard appears.

10. In the Report Source section, use the following table to enter information:

    Field	Description
    Source Type	Defaults to Table.
    Table/View Owner	Verify PS_APEX appears as the default. It not, select it.
    Table/View Name	Use the pop-up menu to choose the table/view from which you want to build the report.
    Select Columns	Select one or more columns to be included in the report.
    Link to Single Row View	Defaults to Yes.
    Link to Single Row View	Defaults to ROWID.

11. Click Next.
12. On the next page of the Create a Page wizard, click Confirm once you have verified your information.

Field Level Security (FLS) Support in Enterprise Reporting

FLS is supported within Enterprise Reporting at the column level within interactive reports that are published to PowerSchool as Enterprise Reports. This means that in order to implement FLS in an Enterprise Report users must add the needed code to each column in an APEX interactive report. All of the example Enterprise Reports provided in the preview and current release have FLS implemented at the column level. Specifically, a PL/SQL Expression shown below needs to be added as a Condition for each column:

CAS_GET_FIELD_SECURITY('LASTFIRST','STUDENTS',:P101_CURRENT_USERID,:P101_CURRENT_SCHOOLID)

For more information on adding conditions at the column level to interactive reports in APEX, refer to the Conditions Available in Application Builder section in the Application Express Application Builder User's Guide.

Back Up Enterprise Reports

Interactive reports created or modified within APEX are not automatically backed up with your PowerSchool data. You should be sure to periodically back up all of your interactive reports. Any reports that you create or modify must be exported and then re-imported in order to restore them. For detailed information about how to export APEX reports, refer to the Exporting an Application and Application Components section in the Application Express Application Builder User's Guide. For detailed information about how to import exported APEX reports, refer to the Importing Export Files section in the Application Express Application Builder User's Guide.