local_library Resource

Using Selection Criteria for OBI Reports

While each Oracle Business Intelligence (OBI) subject area has its own unique selection criteria, there are many concepts common to most of them. This resource addresses gotchas, tips and tricks for using them effectively and avoiding pitfalls.

See a short demo for Selection Criteria (2:20)

Three buttons at the bottom of the Selection Criteria perform the following actions:

Apply

  • After entering or changing selection criteria click Apply to run the report.
  • Forgetting to select Apply after entering new values is a common mistake. If the selection criteria Apply is pushed out of sight to right, use the horizontal elevator to bring into view.
  • Collapsed sections may show a spinning clock when expanded but will not return data until you select Apply again.

Next

  • Depending on the report, additional selection criteria fields can be displayed by clicking Next
  • The Next button changes to Previous after being clicked. Clicking Previous returns to the original display of selection criteria.
  • Criteria in the Next page of selection criteria, even when not visible, affect which data is returned. Jumping between dashboard report tabs carries selection criteria along but they will not initially be visible in a tab until Next is clicked.

Reset

This button has three options:

  • Reset to last applied values: sets all selection criteria to the values present when the Apply button or Apply Saved Customization was last clicked.
  • Reset to default values: for reports that initially display with pre-filled with "default" values  (like start and end dates for labor schedules) clicking this button will return all values to those initial values.
  • Clear All: removes all entries from selection criteria but leaves customizations of the report output format in place allowing for a completely new set of selection criteria to be entered.

Clear All removes all criteria including turning off all radio buttons and one may need to be re-selected before reports will run successfully.

Clear my Customization, available from the gear icon page option, removes selection criteria like Clear All but goes further removing all modifications to the report output as well returning it to its default state.

  • To enter an organization (org), click the down arrow to the right of Select Values and click More/Search to open the pop-up window with the search function otherwise you would need to type the full long name.
  • Organization perspective choices of award org, project org and task org specify which segment of the PTA  to search on and only matter if an actual organization value is entered; then records are returned where the selected segment (P, T or A) is owned by the specified organization.
  • Task Org is the default perspective for Consolidated Expenditure (CER) reports as the task is where expenses are accounted for while Award Org is the default for Revenue and Fund Management (RFM) reports as the Award is where funds are accounted for including beginning and ending balances.
  • Budget Unit choice once selected cannot be deleted. Instead select the blank at the top of the list to clear a selection.
  • In Expenditure Reports, when using the organization perspective field, you can select a single perspective (Award Org, Project Org or Task Org) using the selector in the perspective field, but if you select the blank row at the top you will retrieve data for all three perspectives at once.
  • Owner/Manager Perspective has checkboxes that allow making multi-selections. When using this criteria, you must also select an owner/manager name in the field above
Screenshot showing Owner/Manager Perspective selection criteria

 

When selecting from a large set of values, you can narrow the search by using text matching (start, contains, ends, is Like (pattern matching)) by clicking the entry field's down arrow and selecting More/Search.

Screenshot showing selection criteria search dialogue box
  • Values are case sensitive unless unless Match Case is deselected.
  • To select all but a few values,  display the list of values, select the double right arrow [>>] to select all (which moves them to the box on the right) and then choose the ones to remove using the single left arrow [<].
  • is Like recognizes two wildcards (%) and (_) which match zero-or-more, or exactly-one, character respectively. For example "%z%b%" looks for zero or more characters followed by z, followed by zero or more characters followed by b, followed by zero or more characters and would match "a zebra" (so would "%z_b%").
  • Click the pencil tool in the upper right corner of the select values dialog box to paste in columnar lists of search values . The tool will add the semicolon ";" delimiter between values. You can export data from one report to cut and paste in the search dialog for another report (e.g., purchase order (PO) numbers or PTAs).
    Screenshot showing selection criteria search dialogue box

  • To pull data for a single General Ledger (GL) Period, enter the same GL Period for both the start and end period along with selecting the matching GL Period radio button.
  • Selecting Fiscal to Date (FTD) will return from September 1 of the fiscal year of the chosen GL Period, so FTD JUN-2012 will return SEP-2011 to JUN-2012.
  • Selecting Project to Date (PTD) for sponsored projects returns from the beginning of the project regardless of fiscal year but choosing PTD for non-sponsored PTAs will return the same as FTD, effectively pulling from September 1 of the given fiscal year to the GL Period chosen.
  • When pulling by Project to Date, especially when using org to pull sponsored projects, turn off the checkbox for Closed in the award status fields to avoid returning many old closed projects.
  • Many reports now have a Last Closed period that can be selected to use with FTD, PTD or First/Last GL Period. This will default to the last closed period and is convenient to use if you save a customization of this report to run on a repeat basis. 
  • Care should be taken when using Last Closed and what your expectations are on the date you run the report. 
    • If you run FTD and GL Period = Last Closed on July 3, 2019, your report will include data from SEP-2018 through MAY-2019, because June has not closed yet. To include June data, you should specify JUN-2019.
    • If you run FTD and GL Period = Last Closed on July 15, 2019 it will include data from SEPT-2018 through JUN-2019. To include July data, you should specify JUL-2019. 
  • Filling in the wrong GL Period box for the Period Selection radio button choice will cause no data to be returned.
  • Selecting the radio button for Fiscal or Project Year to Date by itself is not sufficient, you must also explicitly name the GL Period up through which to pull data. 

All selection criteria and report output formatting (customizations) can be saved for later reuse by clicking the Page Options gear icon and selecting Save Current Customization and entering a name. At any later time when on this report page clicking the Page Options gear icon, selecting Apply Saved Customization and choosing the given name will restore both the selection criteria and report output formatting and apply them.

  • Do not over specify the criteria. For example, if you are using an award number then it is best to not specify the org as the award is a more narrow specification and if you list the wrong organization then the award will not be pulled.
  • Do not include any spaces between values in selection criteria. The delimiter between values is the semi-colon except in a few BI Publisher reports which use a comma (noted in the instructions page on those reports).
  • To minimize refresh time when developing customizations of reports, start with a small data set to test or build reports, e.g., a single small PTA for a single GL Period.
  • Collapse unneeded sections to make reports return results more quickly.
  • The maximum number of rows returned is 500,000 and reports timeout after 15 minutes, so datasets may not be returned if they are too large.
Last Updated: Oct 17, 2023

Questions?

arrow_upward
Back to Top