Adding Database Queries

Use queries to retrieve project data and other data from the Accolade database to display in a query-based list metric. Accolade provides views into the database in the form of reporting views. Reporting views group like data together that you can use to write a query. For example, RV_Classes contains the data that is relevant to classes within Accolade, and RV_ProcessModels contains information you can use to report on process models and their usage within Accolade.

After you have identified the type of data you want to retrieve, review the RV_ and RVP_ reporting views to determine which view contains the data you need.

To quickly view the list of all reporting views in Accolade, from the System > Content Sources > Queries menu, create and preview the following query: SELECT * from RV_ReportingViews. Substitute the RV_ReportingViews portion of the query with any reporting view name to see the list of all columns within that view.

To create a database query:

1. From the System menu, select Content Sources > Queries.

To narrow the query list, search by the query name, system name, or category.

2. Do one of the following:

• To add a new query - Click Add New in the upper right corner of the page.
• To edit an existing query - Click the name of the query to open it for editing.

3. Complete the following information to identify the query:
   
   Required fields display with red text and an asterisk * if the field is empty.

Field	Description
Name	Enter a name, up to 64 characters long, which identifies the query.
System Name	Enter a unique, shorter name that identifies the query in reporting views, field codes, and other places in Accolade. The name must be unique among queries, and can contain only letters (English alphabet), numbers, and the underscore.
Description	Enter a description of the purpose or nature of the query, and the data it returns. This description helps other users identify the query throughout the system.
Category	Enter or select the group to which this query belongs. More... Use categories to organize like queries together. For example, you may choose to group all the queries used for financial analysis into the same category, in order to separate them from queries that are used for different purposes. Leave this field blank to add to the Default category. To define a new category, select New Category and enter the category name. To delete a category, remove every item from the category. Empty categories are deleted automatically.
Configuration Access Groups	Select the access groups to which this query belongs. Process Designers with matching permissions will be able to edit and view the query. The access groups displayed are based on the current user's access group permissions and the access groups the query belongs to.
Process Model Usage	Click the Process Model Usage button to see a list of process models that the query is associated with. The list includes all process models the query is included in, as well as links to the process model's component tree pages you have Edit access to.

4. In the SQL field, enter the code for the query.
5. Click Preview to ensure the query pulls the correct data and is working as you intended.
6. Click Create to create a new query or Apply to save changes to an existing query.

Notes:    You may intend a query to display a chart or report either on the Charts & Reports Manager page within a project, or in both locations. When charts and reports display on the Charts & Reports Manager page, they can display information about multiple projects. However, the same chart or report is restricted within a project to show only data related to the project it is viewed in. Data not related to projects, such as data from RV_ views, is not affected. If a query contains a reference to a RVP_ or CRVP_ reporting view, the result displays [Unavailable] when used on an external Idea Submission form. External users do not have the security rights to see the data contained within those views. To delete a query, click in the Delete column on the Queries page. Categories created for queries, HTML reports, or MS Excel reports are saved in common and apply to all three. To remove a category from the list, ensure that no queries, HTML reports, or MS Excel reports are assigned to the category.