Query Examples

See the sections below for examples of common, basic queries that you may use in your business. If you require a more complex query, review external resources about writing SQL queries, or contact Sopheon Customer Support.

Selecting Data from a Single View

The most basic query retrieves selected columns of data from a reporting view.

SELECT TeamLeaderName, TeamLeaderEmail
FROM RVP_Projects

The query above returns all project managers and their email addresses from the RVP_Projects report view, which reports on all opened and closed Accolade projects.

Restricting Data Returned

To restrict the results to only those records that have specific properties, filter the query using a WHERE clause. For example, you could write a query to retrieve records of projects devoted to a specific market, or records of projects where a metric identifies the project as behind schedule.

SELECT ProjectName
FROM RVP_Projects
WHERE DateCreated > '01/01/2014'

The above query returns a list of all projects created since January 1, 2014.

Selecting Data from Multiple Views

Queries can return data from more than one view.

SELECT P.ProjectName, PM.ProcessModelName
FROM RVP_Projects P, RV_ProcessModels PM
WHERE P.ProcessModelID = PM.ProcessModelID

The query above returns the project name and model name for all records where the model’s ID is the same in the RVP_Projects and RV_ProcessModels reporting views. The query produces a report showing the model used for each project.

In this query, the “P” is defined as an alias for the RVP_Projects view and “PM” is defined as an alias for RV_ProcessModels. In the FROM clause, the aliases ensure that each field is selected from the correct view. The WHERE clause ensures that each data pair is drawn from rows containing the same model ID.

Arranging Results

To arrange the items returned by the query, add the ORDER BY keyword to the end of the query.

SELECT TeamLeaderName, ProjectName
FROM RVP_Projects
WHERE ProjectClosed = 0
ORDER BY TeamLeaderName

The query above returns the names of the assigned project managers and the names of projects, listed alphabetically by the project manager’s name.

Replacing Default Column Headings

Each query returns data using default column headings, which might not be as meaningful as you need them to be in the returned results. Replace the Accolade default column headings in reports with more understandable expressions using AS to substitute the name of your choice for the default column name.

SELECT ProjectName AS Project, TeamLeaderName AS Project_Leader, CurrentStageName AS Current_Project_Stage
FROM RVP_Projects
WHERE ProjectClosed = 0
ORDER BY ProjectName

The above query returns three columns that display as “Project,” “Project Leader,” and “Current Project Stage” in the results. Accolade replaces the underscore character in aliases with a space. In this example, the WHERE filter limits the results to open projects.

Including Links to Accolade Projects

Make the project names in a report behave as hyperlinks to each project’s initial page, as defined in its model to provide quick access to a project's details directly from a report.

To create a report that shows project names as hyperlinks, replace the ProjectName column in the query with the text LinkableName.

SELECT LinkableName from RVP_Projects

The query above returns a list of all open or closed projects with each project name as a link to the project details.

SELECT TeamLeaderName, LinkableName
FROM RVP_Projects
WHERE ProjectClosed = 0
ORDER BY TeamLeaderName

The query above returns the names of the assigned project managers and the names of projects, listed alphabetically by the project manager’s name, with the project name as a link to the project details.

Tallying Results

Tally the number of items returns in a query using COUNT clause.

SELECT TeamLeaderName AS Leader,
COUNT(*) AS Leaders_Projects FROM RVP_Projects
WHERE ProjectClosed = 0
GROUP BY TeamLeaderName
ORDER BY TeamLeaderName

The query above returns a list of project managers of active projects in alphabetical order with a count of the number of active projects assigned to each project manager. The count displays in a column named Leaders Projects.

Personalizing Results

Create queries that display information specific to the user viewing the report using the $USERID$ substitution token in a WHERE clause in the query.

SELECT * FROM RVP_ProjectDelivActivities
WHERE ActivityOwnerID = $USERID$

The query above displays a list of all activities that the user is currently assigned.

The $USERID$ token must be set equal to a column that contains a user ID, such as TeamLeaderID, DeliverableOwnerID, and so on.

Learn More: