Using Query Manager to Create and Save Queries in BFS
For instructions and examples on how to join multiple records together, please see the next job aid:
Definitions
- Query = A request for information from a database
- Record = A table in a database, similar to an excel spreadsheet
- Field = A column on a record
- Criteria = Filtering the data that will be returned in the query results
- Run = View the results or output of the query
2) Create New Query
- From the Reporting Tools page, click the Query folder to access the Query Manager
- If you don't see Query Manager, you may need to request access through sara.berkeley.edu
- Click the 'Query Manager' navigation link to go to the Query Manager page, where you can create, edit, save, and export queries
- Click the 'Create New Query' link
3) Search Record
Records Page
- Creating a new query takes you to the Records Page, where you can search for and add records to your query
- There are tabs at the top of the page to help you navigate between the different pages in Query Manager
- Type the name of the record you wish to search for in the search field (which is labeled 'begins with')
Let's begin by searching for a record, using the example: JRNL_HEADER
- You can click the 'Search' button, or press Enter on your keyboard to display the search results
5) Choose Fields
Query Page
The Query page is where you can choose what the output of your query is going to be, or what records and fields you want to use.
By default, the record is expanded to see all fields that are in the record.
Please keep in mind that even though a field is included in the table, it does not mean the field is populated with data, since not every field is required to be filled out in our day to day operations.
- You can choose the fields you want to use by checking the Field boxes to the left of the field names
For this example, let's choose the following Fields:
- Business Unit
- Journal ID
- Journal Date
- Source
Fields Page
At any time, you can see which fields you have chosen by clicking on the 'Fields' tab at the top of the page
- Fields can also be edited or deleted from this page, using the 'Edit' and 'Delete' buttons
- Here you can also change the name of a field (which would be the same as changing the column name of an Excel spreadsheet)
6) Add Criteria
Now let's go back to the Query page and add some filters or criteria to refine our search.
- Criteria can be added by clicking the criteria buttons to the right of the field names (filter icons with '+' sign)
Business Unit Criteria
For this example, let's limit the Business Unit to: 10000
- First, click the criteria button for Business Unit
- Then, from the Edit Criteria Properties page, you can set the criteria conditions that are available
- Set the Expression 1 Type to 'Field'
- Choose 'Equal To' in the Condition Type drop down menu
- Set the Expression 2 Type to 'Constant'
- Set the 'Constant' to 10000
- Click 'OK' to set the criteria
Criteria Page
- At any time, you can click on the 'Criteria' tab to see the criteria you've just defined
- Here we can confirm that we have successfully set our criteria for Business Unit to 10000
Journal Date Criteria
Now let's add Journal Date criteria with a date range.
For this example, let's say we want to include criteria from the month of January
- From the Query page, click the Criteria button to the right of the Journal Date row
- From the Edit Criteria Properties page, select 'Between' in the Condition Type drop down menu
- The Expression 2 section will change to display input fields for the date range
- To do this, type or select the first and last day of the month in the input fields, and click 'OK'
8) Save
It is always a good idea to save your query, especially if your query is more complex and involves many fields and criteria.
You can save your query at any time from any of the navigation tabs at the top of the page (with the exception of the Run tab).
- To save your query, go to the Records page, and click 'Save'
Here you can choose a name for our query, the query type, and whether your query will be public or private:
- Public - anyone with access to Query Viewer can run this report
- Private - the query is limited to your use only
For this example, let's name our query AA_TEST, set the Query Type to User, and set the Owner to Private
- It is good practice to incorporate the initials of your first and last name as part of the saved query name
- Click 'OK' to save
- The Query Name has changed at the top of the page
It is always possible to replicate or change the name of the query via Save As, however once saved, clicking the 'Save' button again will automatically save the current state of your query.
