Noetix WebQuery
This chapter describes how to work with and manage queries in Noetix WebQuery (NWQ).
Overview
Complete the following tasks to set up NWQ:
Install all components of Noetix QueryServer (NQS) and NWQ. For information, see the Noetix Platform Installation Guide.
Set up your data sources in NQS, either manually (For information, see Noetix Platform Administration Guide.) or automatically using Noetix Generator for Noetix Platform—Oracle E-Business Suite Edition (Noetix Generator).
Set up users and roles in NQS, either manually (For information, see Noetix Platform Administration Guide.) or automatically using Noetix generators.
Verify your web browser meets the NWQ system requirements. For information, see the Noetix Platform Release Notes available with the product software.
Helping Users Get Started
Administrators may want to use the following steps to help users get started with NWQ:
Ensure users have access to a web browser meeting the NWQ system requirements (see the NQS/NWQ Release Notes available with the product software).
Provide the URL for accessing NWQ through browser to query users in your enterprise. The URL is http://<computer name>/nwq/login.htm, where computer name is the name of the computer on which NWQ is installed. If your organization policy requires the use of Secure Sockets Layer (SSL) for encryption of user credentials and data, the URL must be https://<computer name>/nwq/login.htm.
Inform users of their user names and passwords as set up in NQS.
Refer new users to the NWQ User Help for instructions on navigating NWQ. To access the NWQ User Help, click the help link in the top right-hand corner of any NWQ page. Help topics can be printed for reference.
NWQ Security
The Noetix WebQuery (NWQ) security model works in conjunction with the security model in Noetix QueryServer (NQS), using the users, authentication, and other security objects of NQS. NWQ also adds its own layer of security to the Noetix Platform.
Four main types of security are used in NWQ:
Logon security
Folder security
Query security
Feature security
These types of security are each discussed in the following sections.
Logon Security
Logging on to NWQ requires that a user is set up in NQS. Each user logs on with an NQS user name and password. The user and password is authenticated using the authentication method defined for that user (for example, against a network server or Siebel application), which tells NWQ whether the user should be admitted.
When a user is authenticated against Oracle E-Business Suite, NWQ will also prompt the user to choose what Oracle responsibility he or she wants to log on with. To switch between responsibilities, a user can log off and then log on again with another responsibility.
Folder Security
Queries in NWQ can be organized into three different types of folders—Personal, Public, and virtual tables (VTables). The following sections describe these folders and how users can be granted access to them.
There is also the Subscriptions folder that contains query delivery subscriptions. For information about granting users access to susbscription management and this folder, see the NWQ Administrator Help.
Folder security determines whether a user can see and open a folder and view the queries it contains. A user still may not have permissions to run each of these queries and return data. For more information about query security, see Query Security.
Personal folder: Each user has his or her own Personal folder, where queries he or she creates or customizes can be accessed. Users have full access to their own Personal folder, and are the owners of their Personal queries. Users have permission to create subfolders within their Personal folder to organize their queries. Users can also move, rename, copy, delete, and create queries within their Personal folder.
Users can choose to share their own queries with other users. For more information about sharing queries, see Sharing Queries.
Public folder: Public folder provides a way to share access to a query throughout an organization, to multiple users at once. As described in the “Personal Folder” section, queries themselves always exist in the Personal folder of a user and so can never actually live in a Public folder. Instead, a link can be created in the Public folder that points back to the actual query. This is similar to how shortcuts work in Microsoft Windows. Other users can open this link and work with the query, but to save changes, they must save the modified query to their own Personal folder.
Folder permissions allow you to determine who can access a certain Public folder and its contents. To do this, you can grant an NQS role certain access to an NWQ folder. By granting access to an NWQ folder, you are giving anyone who is a member of that role the permission to view this folder and its contents. This will include any user that is a member of the role explicitly or implicitly.
There are five different permissions that can be granted to determine how a user can use and modify a folder—Select, Drop, Alter, Create, and Change Permissions (For information, see Manage Permissions in Noetix Platform Security Set Up). You can grant or revoke each permission, or allow users to inherit the permissions from permissions set elsewhere. Revoking a permission will disallow that permission even if a user is granted that permission through a conflicting role.
Let’s continue the following example. Given our setup of users, organizations and roles, the administrator now starts creating Public folder in NWQ.
Figure 1.1 NWQ Folder Permissions
Immediately below the root of Public, we create a folder called “Western US Reporting.” Below that, we create a folder called “Sales Reports,” and then “Commission Reports.” We then go through the process of publishing our sales and commission reports into these last two folders.
When Sally logs on to NWQ, what will she see? Since she has been granted the “High Tech Sales” role, which has Select permission to the “Sales Reports” folder, we expect that she will be able to see the “Sales Reports” folder. But what about the “Western US Reporting” folder? When Sally browses to the Public folder root, she will indeed see this folder, and will be able to continue navigating into the “Sales Reports” folder.
The reason that Sally can see the “Western US Reporting” folder is that she has been implicitly granted a Browse permission (You cannot explicitly grant a user this permission). A user is granted this permission when they have explicit select access to a child folder, but not the parent. The browse permission lets the user navigate into the folder, but not view any contents except for the folder they have access to. If queries had been linked directly into the “Western US Reporting” folder, then Sally would not be able to see them.
Folder permissions flow down to child folders. Thus, since Sally has select access to the “Sales Reports” folder, she will also have select access to the “Commission Reports” folder and its contents.
For more information about setting permissions on NWQ folders, see Granting Public Folder Permissions.
VTables folder: VTables folder allows users to access the virtual tables (VTables) in NQS. These VTables are exposed as VTables queries in NWQ. VTables queries allow users to run SELECT statements against the VTables only, and should be considered starting points for custom queries. Users cannot modify or save queries in the VTables folder, but can create new custom queries from the VTables folder queries, then save them to their Personal folder.
Access to contents of the VTables folder is determined by granting default subfolders in NQS Administrator. A subfolder will appear in NWQ when the logged-on user has been granted the subfolder as a default subfolder, either implicitly (through organizations or roles) or explicitly (directly granting permissions to specific users). A user will only be able to access an object that is within one of these default subfolders.
If a user has permission to an item that is not in one of his or her default subfolders, the item will not show up at all in the VTables folder. If a user has been granted a default subfolder but not granted permission to the connection, the object will show up but the user will not be able to run it.
Query Security
In many cases, a user may end up with security to view all queries within a specific folder, such as a default subfolder in the VTables folder. A user may also be given a copy of a query they did not originally have permissions to. When this happens, the security of the data is not in jeopardy. To run the query and return data, a user must have been granted permissions to the following:
The virtual table (VTable) the query is based on.
At least one mapping that can return the data.
A connection that can be used to log on to the target database.
A user will receive an error if he or she attempts to run a query without having these permissions.
For more information about these permissions, see Granting Permissions to a VTable in “VTables and Mappings.”
Users can send links to queries or run queries in Excel as well. When distributing a query to other individuals this way, a user can choose to embed their logon and password, allowing the recipient to run the query without logging on with their own credentials. If these features pose a security risk, you can use NWQ profiles to allow only certain users access to these features.
Feature Security
Features in NWQ are made available to users based on the NWQ profiles they are assigned. NWQ profiles are sets of rights that allow administrators greater flexibility in controlling which NWQ features are available to users. When certain NWQ features are not needed or recommended within your enterprise, you can revoke access to those features. For example, you can revoke access to sharing queries, or executing a query in Excel.
For clarity sake, it is important when talking about security issues in NWQ to distinguish between profiles that grant and revoke features, and folder permissions that grant and revoke access.
NWQ profiles do not control users’ access data; NWQ profiles determine what users can do with the data once they log on to NWQ (for example, can they export to Excel or customize queries). These profiles are used in NWQ only (since they deal with access to NWQ features) and are not applied to third-party reporting tools that may be used to retrieve data from NQS.
NWQ profiles are optional; you can allow all users to access the default NWQ features without any additional setup steps. If you choose to customize your NWQ profiles, you can either modify and grant a single NWQ profile to all users in your enterprise or create multiple profiles based on different levels of access.
For more information about creating and assigning NWQ profiles, see Manage Profiles in “Security.”
The following steps can be used to set up permissions:
Create Roles that map closely to job functions.
Grant these Roles access to the necessary Servers and VTables.
Grant these Roles to your Organizations.
Grant or revoke permissions at a user level (if any exceptions to inherited permissions are needed). To minimize maintenance tasks, limit the number of these exceptions whenever possible.
Web Queries
Web queries are built on one or more base tables, much as traditional reports or queries are written against tables in a database. These base tables are defined in NQS as VTables, and each base table is associated with one or more database tables. The Properties page for a query shows which virtual tables (VTables) serve as its base tables.
A Web query can contain all of the columns in its base tables, or a subset of those columns. Many Web queries can share base tables, and yet each query can be built so as to return and display data to meet different needs.
After VTables are set up in NQS (for information, see VTables and VViews in “VTables and Mappings.”), you must perform a few steps to make them available to users to query from NWQ. Understanding all the options for making VTables available to users can help you in planning what VTables you will need to satisfy users’ reporting requirements.
You can use the following steps to make VTables available to users in NWQ:
Expose the VTable in NWQ. (You can also expose a whole subfolder of VTables at once.)
Customize or format the Web query (if needed).
Share the query with users.
Exposing VTables in NWQ
A virtual table (VTable) created in NQS does not automatically display in NWQ for users to work with. An administrator or report developer needs to make the VTable available in NWQ before queries can be written against it. This process enables a Web query based on the VTable to be created in NWQ, with all the columns of the VTable initially included in the query.
There are two ways to make VTables available in NWQ:
Exposing a single VTable: This method makes a single VTable display in the Personal folder of a single user.
Exposing a group of VTables: This method makes an NQS subfolder and all the VTables within it display in NWQ in the VTables folder.
Adding a VTable to a User’s Personal Folder
If you need to make a single virtual table (VTable) available in NWQ or need to expose it for only one user, you can use a shortcut and bypass using the VTables folder. This method creates the VTable as a web query in a Personal folder (for yourself or another user), and you can modify and/or publish the query from there without needing access to the VTables folder.
This method is ideal for making a VTable available for a user without access to the VTables folder, or if you plan to publish VTables to users using the Public folder rather than the VTables folder.
The following procedure explains how to make a single VTable available in the Personal folder for yourself or another user. The query will have the same name as the VTable and will be saved in the Personal root folder of the user.
To expose a single VTable
Using NQS Administrator, navigate to the VTable you want to make available in NWQ.
Verify that you (or the user you will create the query for) have Select permission to the VTable. For more information about permissions, see Manage Permissions.
Right-click the VTable, then select one of the following:
To make the VTable available for yourself, click Create Web Query> For Yourself (user name).
To make the VTable available for another user, click Create Web Query> For Another User, navigate to the required user, and click OK.
A message will display stating the query was created successfully, and will list the name and location of the query that was created.
After performing these steps, the VTable will display as a Web query in the Personal folder. The query can be run as-is, or further customized and formatted.
Adding a Group of VTables to VTables Folder
You can make a subfolder of virtual table (VTable) in NQS display for select users in the NWQ VTables folder. The VTables folder provides read-only access to the VTables in NQS, arranged and named the same as in NQS. These VTables can be customized and saved as Web queries in a user’s Personal folder, then subsequently shared with other users if required.
Access to VTables within this folder is set in NQS. For each user, a subfolder will appear in NWQ when the user has been granted the subfolder as a default subfolder in NQS, either implicitly (through organizations or roles) or explicitly (directly granting permissions to specific users). The user will then see all VTables within one of these default subfolders.
The VTables folder is best used when a whole subfolder of VTables needs to be exposed to users (for example, when connecting to other data sources through NQS/NWQ). Most users may not need access to the VTables folder, especially if the majority of their reporting is done against NoetixViews or NoetixAnswers.
For information about making a subfolder of VTables available in the VTables folder for users, see Granting a Default Subfolder in “VTables and Mappings.”
After performing these steps, the subfolder and all VTables within will display for the user in the VTables folder area, as a web query. The object can be run as a query, or further customized and formatted.
Customizing or Formatting the Web Query
When a virtual table (VTable) has been made available in NWQ, you can run the resulting Web query as-is. However, the query will not yet contain many properties of a Web query, such as a title, until you customize and/or format the query in NWQ.
For example, you will want to:
Remove any unneeded columns from the query
Add a title and subtitle for the query
Modify the default description
Sort the results
When customizing a query in the VTables folder, the new query will be saved to your personal folder. You can then publish or share it to other users as needed, but you cannot save the customized query to the VTables folder.
For more information about customizing and formatting a query, see the NWQ User Help.
Viewing Query Details
You can view detailed information about the NWQ query components on the query specifications page in NWQ Administrator.
To view NWQ query details
On the Queries tab, click the name of the query. The query specifications are displayed in a new browser window.
The following query components will be displayed on the query specifications page.
Answers
The following are the specifications for answers. You can customize them in the Options page when creating or customizing a query.
Description: The description of the answer.
Report Title: The title of the report that is displayed in the query results page.
Report Subtitle: The subtitle of the report that is displayed in the query results page.
Parameter Page Title: The title of the parameter page if the query has parameters. The parameter page is displayed when you run the query.
Query Distinct
Specifies whether unique rows of a query are displayed. If you have selected the Hide Duplicate Rows (choosing a RED column will disable this option) check box on the Columns page, Yes is displayed, and you can view only the unique rows of the query. If the check box is not selected, No is displayed, and you can view all the rows of the query (including duplicates).
VTables
The following is the specification for VTables:
Base VTable: Lists a virtual table (VTable) or related VTables based on which a query is created. You can customize the VTables, such as change the base tables, add columns to the VTables, and join the VTables based on related VTables on the VTables page when customizing or creating a query.
Joins
The following are the specifications for VTable joins. You can customize them on the VTables page when creating or customizing a query.
Left VTable: The name of the left VTable in a join.
Right VTable: The name of the right VTable in a join.
Relationship: The relationship defined for the VTables that are joined.
Join Type: The type of join used in a query.
Columns
The following are the specifications for columns:
Col: The column name or column alias of a query.
Expr: An SQL statement that represents a column or a calculated column with a complex SQL expression.
Sort Pos: If you have defined sort rules for multiple columns, each sort option is given a sort position number automatically, starting from sort position 0.
Sort: The sort rule defined for the column, either Ascending or Descending.
Group Sort: If the Group Sort check box is selected for the column in the Sorting page, Yes is displayed. Otherwise, No is displayed.
Disp Width: The width of the column. You can customize it in the query results page and save the query.
Format Mask: The format mask specified for a column based on the format class to which the column belongs. For example, the format mask of a column belonging to Date format class can be specified in any of the formats available in the query results page, like <MM-DD-YY>. You can customize it in the query results page.
Format Class: The format class to which the column belongs (for example, data types like String, Date with or without Time, Numbers, and Currency). You can customize it in the query results page.
Aggregation: The aggregation type defined for the column.
Agg Distinct?: Specifies whether unique rows of a query are displayed. If you have not selected the Aggregate All? check box on the Edit Column page, Yes is displayed, and you can view only the unique rows of the query. If the check box is selected, No is displayed, and you can view all the rows of the query (including duplicates).
Page Item: If a column of a query is added to the filter bar as a page item on the query results page, Yes is displayed. Otherwise, No is displayed. You can customize it in the query results page.
Page Item Pos: If you have selected multiple columns as page items on the query results page, each page item is given a page item number automatically in the order they appear on the page, starting from page item position 0.
Filters
The following are the specifications for filters. You can customize them in the Filters page when creating or customizing a query.
Filter: If you have defined multiple filter conditions for a query, each filter condition is given a filter number automatically, starting from filter number 0.
Column: The column name to which the filter condition is applied.
Aggregate Filter?: If a column of a query is defined as an aggregate or a calculated column, Yes is displayed. Otherwise, No is displayed. You will see an Aggregation Filter option in the Filters page when you customize the query.
Operator: The condition defined for the filter.
Value(s): The value provided for the filter criteria.
Include Null Values?: If the Include Null Values check box is selected in the Filters page, Yes is displayed. Otherwise, No is displayed. Selecting the check box will retrieve even the columns with null value.
Boolean: The boolean operator that is defined if there are multiple filters for a query, either AND or OR.
Parameters
The following are the specifications for parameters. You can customize them in the Parameters page when creating or customizing a query.
Parameter: If you have defined multiple parameters for a query, each parameter condition is given a parameter number automatically, starting from parameter number 0.
Prompt: The descriptive prompt defined for the parameter.
Column: The column name for which the parameter condition is defined.
Aggregate Parameter?: If a column of a query is defined as an aggregate or a calculated column, Yes is displayed. Otherwise, No is displayed. You will see an Aggregation Parameter option in the Parameters page when you customize the query.
Operator: The condition defined for the parameter.
Default Value(s): The default value provided for the parameter.
Include Null Values?: If the Include Null Values check box is selected in the Parameters page, Yes is displayed. Otherwise, No is displayed. Selecting the check box will retrieve even the columns with null value.
Is Optional?: If the Optional check box is selected in the Parameters page, Yes is displayed. Otherwise, No is displayed. Selecting the check box will make the parameter optional. You can choose to provide or not provide the parameter value when running the query.
Boolean: The boolean operator that is defined if there are multiple filters for a query, either AND or OR.
Totals: The following are the specifications for totals. You can customize them in the Totals page when creating or customizing a query.
Total: If you have defined grand totals or subtotals for multiple columns, each total is given a total number automatically, starting from total number 0.
Column: The column for which the total condition is defined.
Aggregation: The aggregation type defined for a total.
Total Label: The descriptive label provided for a grand total or subtotal.
Total Type: The type of the total, either Grand Total or Sub total.
Break Col: If the Page break check box is selected for subtotals in the Totals page, Yes is displayed. Otherwise, No is displayed. Selecting the check box will insert a page break in the printed report at each change in the column value at subtotal level.
Break on Group Sort: If the Page break check box is selected for grand totals in the Totals page, Yes is displayed. Otherwise, No is displayed. Selecting the check box will insert a page break in the printed report at each change in the column value at grand total level.
Changing Query Ownership
When a user customizes and saves an NWQ query, the query lives in their Personal folder and that user is the query owner. You can use the following procedures to change the query ownership.
Taking Ownership of a Query
Use the following procedure to take ownership of a query from a user. You will now own the query and it will appear in your Personal folder.
To take ownership of a query
Log on to NWQ Administrator, and open the Queries tab.
From the Show queries owned by drop-down list, select All Users or the specific user that owns the query to delete.
- OR -
Search for the query by entering a word or phrase in the Find Queries field, and then clicking Go.
In the list of NWQ queries that is displayed, select the check box associated with the query that you want to take ownership of.
Click Take Ownership. A warning message will be displayed indicating that any dependent subscriptions of the query will be deleted.
Click OK to confirm the action. You now own the selected queries, and the queries will be displayed in your Personal folder.
Giving Ownership to Another User
If for some reason you need to give the ownership to another user, use the following procedure.
To change a query's owner
In NWQ, navigate to your Personal folder, and then check the box next to the required query.
Click the Give link.
If you do not see this link, you may need to modify the NWQ feature permissions for the profile you are currently logged on with.
The Give Query Ownership page appears. Do one of the following, and then click Next:
Enter the user name of the person you want to give the query ownership to.
- OR -
If you do not know the name, click Next to display a list of users. Double-click the required user.
A confirmation page appears. Verify the information is correct, and then click OK.
The query will be deleted from your Personal folder, and will be added to the other user’s Personal folder.
The Give Query Ownership: Status page appears, confirming the ownership has been transferred successfully.
Redirecting Queries
When a query is created, it is based on a single virtual table (VTable) or related VTables (the base VTables) in NQS. If a base table is deleted, made obsolete, or replaced with a different VTable, you will need to rebase the queries that were created against it. Rebasing a query involves changing the base VTable of the query and pointing the query to a new one.
Two or more VTables are related if foreign-key relationships have been defined in NQS between each VTable and at least one of the other VTables.
It is also possible to migrate a query that has been created in a different instance of NWQ (for instance, in a test or development instance). The query can be migrated to another system and based on a corresponding VTable or related VTables. Migrating a query to a different system requires that you have logon information for each system.
Rebasing a Query
When a query is created, it is based on one or more base tables, that is a single virtual table (VTable) or related VTables, located in NQS and exposed in NWQ. If a base table is deleted, obsolete, or replaced with a different table, you must rebase the queries that were created against that base table.
Rebasing a query means:
If the query is based on a single VTable, then that VTable (base table) is replaced.
If the query is based on related VTables, then one or more of those VTables (base tables) are replaced.
You may need to rebase a query if:
A query is no longer usable because its base VTable was deleted or corrupted.
Due to a regeneration, an obsolete base VTable has been deleted and replaced with a new VTable.
To rebase a query, a replacement VTable must exist in NQS. This replacement must meet the following criteria:
The VTable contains every column the current base table contained. (It is okay if the new VTable contains other columns in addition.)
Each corresponding column in the current and new VTables have the same datatype.
IMPORTANT: If a query is based on related VTables, then it is built on base tables with a particular join relationship. If any of these base tables are deleted or replaced, then the join relationship changes. Once the query is rebased, you need to restore the original join relationship by creating joins between the replaced VTables and the unchanged VTables to equal the joins that originally existed. For information about creating joins, see the NWQ User Help.
Only queries in your Personal folder can be rebased. To rebase a query, you must be granted the Rebase profile and have Select permissions on the new base VTables. You will also need to be in Manage mode.
You will only be able to browse to VTables exposed in the VTables folder in NWQ. If the target VTable is not displayed in the VTables folder, you can do either of the following:
Add the subfolder containing the VTable to your user account’s default subfolders list in NQS, and ensure you have Select permission to it;
- OR -
Write down the name of the NQS folder and subfolder containing the VTable, and type the location instead of browsing to it in step For each query you have chosen to rebase, specify the new destination VTable using one of the following methods: below.
You may be able to rebase a query to a base table yet not have permission to run the query after it is rebased. Check your NQS permissions to the VTable, connection, and so on, to manually ensure you have the appropriate permissions.
To rebase a query
Navigate to the folder that contains the query you would like to rebase.
Check the box in front of the query's name. You can select multiple queries if required.
Click the Rebase link.
For each query you have chosen to rebase, specify the new destination VTable using one of the following methods:
For each of the query’s VTables that must be replaced, click the Browse button to browse to and select the replacement VTable. To browse to a VTable, it must be exposed in NWQ under the VTables folder. For that, the VTable must be in one of your NQS default subfolders.
- OR -
Type the name and path to the target VTable in the text box next to the query. The syntax should be <Folder>.<Subfolder>.<VTable>.
- OR -
If you want to migrate all selected queries to VTables in the same subfolder, type the names of the folder and subfolder in the text boxes in the upper right-hand corner and click Apply All. Then, verify or enter the VTable names for each query.
When the new VTables have been specified for each query listed, click OK.
A Finished page displays with a message that the queries were rebased successfully, or that errors were encountered. Click OK to go back to NWQ.
Migrating a Query to a Different System
To migrate a query that has been created in a different instance of NWQ (for instance, in a test or development version of NWQ), the query can be migrated to another system (for instance, the production instance) and based on a corresponding virtual table (VTable) or related VTables. Migrating a query to a different system requires that you have logon information for each system, and a valid VTable (or related VTables) exists in the system you are migrating to (the target system).
You may need to migrate a query if:
You have more than one NQS and want to pass Web queries from one to another.
You write queries on a test or development version of NWQ and then need to copy them to users in production.
You select a VTable or related VTables in the target system to be the base table for your migrated query. This new base table must meet the following criteria:
The target VTable contains every column the original VTable contained. (It is okay if the target VTable also contains other columns.)
Each corresponding column in the target and original VTables have the same datatype.
You can browse to the target VTables in the VTables folder in NWQ (the base table is in a subfolder in your default subfolders list in NQS).
Only queries in your Personal folder can be migrated. To migrate a query, you must be granted the Migrate profile and have Select permissions on the new base VTables. You will also need to be in Manage mode.
You will only be able to browse to VTables exposed in the VTables folder in NWQ. If any of the target VTables are not displayed in the VTables folder, you have two options:
Add the subfolder containing the VTable to your user account’s default subfolders list in NQS, and ensure you have Select permission to it; or
Write down the name of the NQS folder and subfolder containing the VTable, and type the location instead of browsing to it in step 4 below.
You may be able to migrate a query to a base table yet not have permission to run the query after it is migrated. Check your NQS permissions to the VTables, connection, and so on, to manually ensure you have the appropriate permissions.
Migrating a query does not automatically share it to other users or give users permissions on the query after it has been copied over. You will need to manually publish or share the query and verify that users have the correct permissions to view and run it. The original query's related queries and publications to the Public folder also are not copied over.
To migrate a query
Navigate to the folder that contains the query you would like to migrate.
Check the box in front of the query’s name. You can select multiple queries if required.
Click the Migrate link.
Enter the logon information for the target Noetix Gateway (gateway). This includes a user name and password of a user on the target system that has Select permissions on the target VTables, the name of the server on which NWQ is installed, and the port your gateway uses, if this has been set up to be different than the default, “80.”
The user you enter will be the owner of these queries on the target system, and the queries will be saved in this user's Personal folder.
If prompted for a responsibility, select an Oracle E-Business Suite responsibility from the list, then click Continue.
For each query you have chosen to migrate, specify the new destination VTable using one of the following methods:
Click the Browse button to browse to and select the VTables. To browse to a VTable, it must be exposed in NWQ under the VTable folder. That is, the VTable must be in one of your NQS default subfolders.
Type the name and path to the target VTable in the text box next to the query. The syntax should be <Folder>.<Subfolder>.<VTable>.
If you want to migrate all selected queries to VTables in the same subfolder, type the names of the folder and subfolder in the text boxes in the upper right-hand corner and click Apply All. Then, verify or enter the VTable names for each query.
After VTables have been specified for each query listed, click OK.
A Finished page displays with a message that the queries were migrated successfully, or that errors were encountered. Click OK to go back to NWQ.
Sharing Queries
You may share customized queries with users by granting them access to a query, giving them a copy of a query, or publishing a query to the Public folder.
Sharing queries with users does not give them access to the NQS virtual table (VTable) the query is based on. If a user has trouble executing a query you have shared, check their permissions in NQS.
You can only share queries that you own – that is, queries in your Personal folder. To share, you must also have an NWQ profile that grants access to the “Sharing” features. This is not granted by default in the default profile. For more information about NWQ profiles, see Manage Profiles in “Security.”
To share a query that has been created in a different instance of NWQ (a development or test instance, for example), you will first need to migrate the query to the required NWQ system. For information about sharing a query, see Migrating a Query to a Different System.
Granting Access to a Query
When you grant a user access to a query, the user can run the query, but cannot change or delete the query. Subsequent changes you make to the query will affect all users granted access to the query. The query will display in the user’s Personal folder in a subfolder called “Queries shared by: [Your user name].”
To grant access to a query
In your Personal folder, browse to the query you want to grant access, then click the query name.
A menu will display to the right of the query. Click Sharing.
On the Sharing page, click Grant this query to someone.
Select the user from the left-hand list. Using the –> button, move this user to the right-hand list.
To grant access to all users listed, use the >> button.
Click the Grant Now button.
To revoke access to a query
On the Sharing page, select the user, and then click the Remove All Grants button.
Giving a Copy of a Query
When you give a copy of a custom query to another user, the user may change, delete, or otherwise customize the query copy without affecting your original query. The query will display in the user’s Personal folder.
To give a copy of a query
In your Personal folder, browse to the query you want to copy, and then click the query name. A menu will display to the right of the query.
From the menu, click Sharing.
From the Sharing page, click Give a copy of this query to someone.
Select a user from the left-hand list.
Click the Copy To button.
Repeat the steps previous steps for each user who needs a copy.
Publishing a Query
Publishing a query will save a link to your query within the Public folder. When you publish a query to the Public folder, any users with the proper folder permissions can access it. Other users can run the query, but cannot change or delete the query. Any time you make changes to your query, they will be reflected in this published query as well.
For more information about Public folder permissions, see Granting Public Folder Permissions.
To publish a query
In your Personal folder, browse to the query you want to publish, and then click the query name. A menu displays to the right of the query.
From the menu, click Sharing.
On the Sharing page, click Publish this query to a public folder.
Click the Browse button.
Navigate to the Public folder where you would like to publish this query.
Click OK. The Publish page appears.
Click Publish. The Publish page displays the new folder where the query is published, as well as any folders where the query has already been published.
To publish a query within Manage mode only:
In your Personal folder, confirm that you are in Manage mode. If not, click the switch to manage link.
Browse to the query you want to publish, and select the check box.
Click the Sharing:Publish link.
Navigate to the Public folder and subfolder where you would like to publish this query.
Click OK. A dialog box informs you that the query has been published.
Click OK. You are returned to the My Web Queries page.
To confirm the publishing of the query, click the query name. A menu displays to the right of the query.
Click Sharing.
On the Sharing page, click Publish this query to a public folder. The Publish page should display the new folder where the query is published, as well as any folders where the query has already been published.
To delete a published query
On the Publish page, select the published query, then click the Remove button.
This will delete the query from the Public folder, but will not delete the original from your Personal folder.
Querying in Excel
NWQ allows you to run web queries in Microsoft Excel, embedding the query logic directly into the spreadsheet. The resultant data can be saved within Excel and refreshed for up-to-date results.When you click the query in Excel option against a query in NWQ, a dialog box appears prompting you to save or open an .iqy file or cancel both the actions. Choose one of the options. When you open the .iqy file, the query report is displayed in Excel. You can then save the .xls file. However, when you open a saved Excel report, you have to manually refresh the report so that any change that is made to the query is reflected in the report.
Important: When you run a query in Excel in NWQ, the report will return a maximum of 125,000 rows by default. You can also configure the maximum number of rows for a report using the ExcelMaxRows setting in the Noetix.Gateway.Service.exe.config file available at <installation location>\Noetix\NWQ\NoetixGateway\Server\bin. However, increasing the default number of maximum rows could result in memory related issues that may cause Microsoft Excel to crash. If the query results exceed the configured limit of rows, the query results will be truncated, and a warning message will be displayed.
This feature is enabled by default for all users, except in the following cases:
You have set up Noetix Platform to use Oracle E-Business Suite security.
You need to run long-running queries.
Instructions for setting up NWQ to query in Excel in these cases are listed in the following sections.
Querying in Excel for
Oracle E-Business Suite Users
The Query in Excel feature of NWQ does not automatically include the Oracle E-Business Suite responsibility prompt when running queries. To enable the Oracle E-Business Suite prompt for all querying in Excel, you will need to set the value of the ExcelQueryPromptResponsibility element to “Yes” in the Noetix.Gateway.Service.exe.config file. After making the change, all users executing queries in Excel will be prompted for a responsibility. Users who are not configured with Oracle E-Business Suite responsibilities can type% at the prompt and click OK. For more information about the Noetix.Gateway.Service.exe.config file, see Noetix.Gateway.Service.exe.config in “Maintenance.”
Managing Query Deliveries
In NWQ Administrator, you can manage query deliveries by updating the respective subscriptions and their schedules.
Important: To be notified about the success or failure of the subscription deliveries through email messages, you must configure Database Mail in Microsoft SQL Server, and then configure SQL Server Agent Mail to use Database Mail. For information about configuring Database Mail, see Setting Up an email Profile in “Maintenance.” For information about configuring SQL Server Agent Mail to use Database Mail, see the Microsoft documentation.
Managing Subscriptions
The Subscriptions tab lists the details of a given subscription and lets you delete subscriptions. The system displays subscriptions by their name, query name, user, delivery method, next run (as determined by the schedule), schedules, and last run. You can update the subscriptions list by clicking Refresh.
If you click the link under Next Run for a particular schedule, you will be taken to the Subscription Schedules tab and shown the details of that schedule. You can see the entire schedule list by clicking Show all.
Managing Subscription Schedules
The Subscription Schedules tab shows you the schedule details for each subscription, including the next run and whether the schedule is enabled. On this tab, you can delete, disable, or enable schedules. You can see the current schedules by clicking Refresh.
Viewing Subscription Deliveries
The Subscription Deliveries tab displays the details of subscription deliveries. The subscription delivery details, such as the query and subscription names, delivery status, and delivery start and end times, will be available on the page. You can click Refresh to see the current list of subscription deliveries.
Managing Subscription Profiles
To control the use of query delivery in NWQ Administrator, you can turn subscriptions completely on or off by updating the Subscriptions feature in the Profile Properties:Query feature list.
For more information about query deliveries and query delivery subscriptions, see the NWQ User Help and the NWQ Administrator Help.
Folder Management
In NWQ, queries are organized into three main folders:
Personal folder: Each user has his or her own Personal folder, where queries he or she creates or customizes can be accessed. Queries that users share directly with other users can also be found in the Personal folder.
Public folder:NoetixViews and NoetixAnswers can be accessed in this folder. You can also “publish” any queries you want to the Public folder to allow other users to access them.
VTables folder: The VTables created in NQS that have been exposed in NWQ can be found in this folder. These can be run as queries or copied, to customize and format them further. The queries are grouped into subfolders based on how you have set up folders and subfolders in NQS.
Within the Personal and Public folders, you have the ability to manage the folder structure and where queries are saved. Additional folders can be created, modified, renamed, and deleted, and you can manage the permissions for Public folders as well.
To manage folders, you need to be the owner of the folder, or have permissions to the folder. Users will have control over their Personal folders, which contain queries they have customized or that have been granted to them. You can set permissions on the Public folder and any subfolders to determine who can manage the same features in the Public folder.
Adding a Folder
You can add subfolders to help organize your queries in a manageable fashion. To add folders or to make changes to existing folders, you will need to be in Manage mode.
To add a folder
In NWQ, confirm you are in Manage mode. (If you are not, click the switch to manage link.)
Navigate to the folder under which you would like to add a new folder.
Click the Add Subfolder link.
Enter the name and description of the new folder. Each subfolder needs a name that is unique within the folder. Adding a description to the folder will make it easier to manage your folders as more and more folders are created.
Enter the business value for the new folder. The business value determines in which position (order) the folder will display.
You must enter numbers for the business value. For instance, if you want the folder to display at the top (first), enter “1”. If you want the folder to display at the bottom (last), enter a large value, such as “9999.” To allow the contents of the folder to sort by name, check the box next to Use Default Business Value.
If a folder contains both subfolders using defined business values and subfolders using default business values, the subfolders using default values will display at the bottom of the list, sorted by name, below those whose positions were defined. Folders with the same position will also sort by name.
Click OK.
Editing a Folder
To edit a folder
In NWQ, confirm you are in Manage mode. (If you are not, click the switch to manage link.)
Navigate into the folder itself; you should see the name of the folder at the top of the page and all its contents below.
Click the Edit link.
Edit the name and description of the folder as required. Each subfolder needs a name that is unique within the folder. Adding a description to the folder will make it easier to manage your folders as more and more are created.
Edit the business value of the folder as required. The business value determines in which position (order) the folder will display.
You must enter numbers for the business value. For instance, if you want the folder to display at the top (first), enter “1”. If you want the folder to display at the bottom (last), enter a large value, such as “9999.” To allow the contents of the folder to sort by name, check the box next to Use Default Business Value.
If a folder contains both subfolders using defined business values and subfolders using default business values, the subfolders using default values will display at the bottom of the list, sorted by name, below those whose positions were defined. Folders with the same position will also sort by name.
Click the OK button to save changes.
You can also delete or rename a folder from within the folder itself using the links displayed at the top of the page.
Granting Public Folder Permissions
Folder permissions allow you to determine who can access a certain Public folder or subfolder in NWQ. You set up folder permissions by granting a folder to a role, and then setting the permission level. Then any user granted this role will have access to the folder at the specified permission level.
For example, say you create a new folder called “Sales Reports” in the Public folder that the sales managers need to access. You also have an NQS role named “Sales Manager” that has already been granted to all sales managers.
So, to grant all the sales managers access to the new folder, what you would do is grant the “Sales Reports” folder to the “Sales Manager” role. Assuming all sales managers have already been granted access to the underlying data, the managers would now be able to access the “Sales Reports” folder and run the queries.
Folder permissions do not grant users permission to run queries, only to view, publish, or delete them. For more information about permissions to run a query, see Granting Permissions to a VTable in “VTables and Mappings.”
To grant a folder permission to a role
In NWQ, confirm you are in Manage mode. If you are not, click the switch to manage link.
Navigate into the folder itself; you should see the name of the folder at the top of the page and all its contents below.
Click the Permission link.
On the Folder Permissions page, select a role type (group of roles) from the list at the top of the page. The roles available under this role type will display.
Click the required roles to select them. To select more than one role, hold down the CTRL key as you click each role.
Click the Grant button. This will add the roles to the Granted Roles list.
Click a single role from the Granted Roles list. The permissions assigned to this role display in the Permission area.
For each granted role, Grant, Inherit, or Revoke each permission level as required. For more information about permissions, see Manage permissions in Noetix Platform Security Set Up
Click Apply.
You can change or remove the permissions granted to a role using the Folder Permissions page as well. Select the required role from the Granted Roles list, and then make the required changes to the permissions or click Remove.