|
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 create a custom query by selecting a table or view fro the object list with the option of using the wizard to create column conditions.
On the Queries menu, click Query Builder.
Select the Object List tab.
Objects may start with TBL for tables, VW for views, or SYN for synonyms. It depends on which FAST application you are creating your custom report in, what kind of access you have, and what data your institution uses.
These two images show what an object list looks like.
Click one of the following objects (your institution may not allow access to all types of objects):
Table: TBLtablename.
View: VWviewname.
Synonym: SYNsynonymname.
On the right, the Table tab is selected and information that you have access to about the object opens, including schema, the table/view name, type, and column data.
Select the Query tab.
The column names and data types are listed, with
a check box on the left and the wizard 
icon on the right.
Select the check box for each column you want to include in the query.
You can create a column condition
by clicking the wizard . Skip to Step 8 if you are
not creating a column condition.
In the Column Condition Wizard, create your condition and click Save.
Repeat Steps 6 and 7 for each column condition you are creating.
Click Execute Query to test and view the results.
You can select the View SQL tab to edit the query.
Notes:
If a column in the query is using a function, that column needs to have an alias name. For example, SUM(realamount) AS realamount.
If you want the user to enter a value, add square brackets [ ]. For example, WHERE B.FCTRBUDGETSERIES = [Enter the Budget Series].
Click Execute Custom SQL to list the results.
You can Return to the Query Wizard to further customize your query.
When you are finished, click Save Query.
Type a new Query Name.
Select the Private check box if you do not want other users to see the query in the Query Editor or Query Viewer.
Click Save Query then Ok. The query is added to the list.
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.
