|
Using the Query Builder circumvents all form (row-level) security and allows users to see all data in all tables in the Object List. |
|
You can use the Query Builder to create custom queries using Structured Query Language (SQL). If you are familiar with SQL or are an advanced user, you can generate the queries from the database. The Graphical Query Builder provides you with a visual aid to create queries.
When you open the Query Builder in FAST Report Builder, you must first select the schema , or application, that you are going to work with from the drop down list at the top of the page. Alternatively (and more commonly), the Query Builder is accessed directly from the Queries menu in the application in which you are building the Custom Query.
For Example: if you are building a Custom Report in FAST Finance Reporting, Select FAST Finance Reporting -> Queries -> Query Builder to begin.
You will see a Query List, which is a listing of all Queries that you have access to. This includes any queries that you have built and saved as your private queries, along with any queries that have been built by others and saved as public queries. If you Click on a query in this list, you will see a listing of the query in the Query Editor to the right.
You will also see an Object List, which is a listing of all objects that you are allowed to see and use when building queries. If you click on an object, you can see the object details, including the fields (or columns) within the objects and their parameters.
The schema (application) that is displayed is the object owner (application-level, not user level), as access to database objects via the Query Builder goes back to access at the application level, not the user level. It is possible to access external objects with the appropriate security grants in place to appear within a given application. For example, if you are building a query in FAST FINANCE and require an object from FAST AR, it is possible to be granted access. It does not matter if you (the developer) have access to an object, FAST assumes that if you are building something in the Query Builder, it is most likely being done for other users as well, so access needs to be granted at the object level, to the FAST application.
When you are writing queries in the Query Builder, they are executing in the query schema of the related application. So in FAST Finance Reporting, the query would need to execute in the FASTFINANCE_QUERY schema. Therefore, FASTFINANCE_QUERY will need to have "SELECT" privileges to all objects referenced in the query. If you want to access an object from the FAST AR application in FAST Finance Reporting, two things need to happen:
STEP 1: The person or group that is responsible for the FAST AR data must agree to allow this information to be access by FAST Finance. Once obtaining this permission,
STEP 2: Your DBA must grant "SELECT" privileges on this object to the FAST QUERY schema in the FAST Oracle database.
After completing these steps, you will be able to reference this object in the FAST Finance Reporting Query Builder, but you must fully qualify the object with the schema name.
Additionally, if the data is coming from across a DBLINK, you will also need to append @[DBLINK NAME] to the end of the object.
If this is your first time creating this type of Query, access to external objects can be set up by Millennium staff and we are happy to walk you through it. For support, please submit a support ticket to Millennium and we will grant access for the object to show up in FAST application required. This relates to objects being shared between FAST applications. Please first ensure that permission has been explicitly granted by the area(s) on campus responsible for the data to be shared.
In the Query Builder, you can:
Type code to create a custom query.
Select a table or view, edit the code, and save the query.
Select a query and edit the code.
Select an existing query and delete it.
In the Query Viewer, you can:
Select and view custom queries and export them to Excel.
