FluentPro Help Center

What do you need help with?

Working with SQL Queries

When Widget Editor is in Advanced mode you can edit widget SQL query on the SQL tab.

There are several simple rules that you need to know in order to write a correct SQL query:

  • Query should return a single dataset, i.e. there should be only one “select” statement that returns results.
  • To use Project Filter or Resource Filter selection join the #TempUids temp table into you query. This table has a column named [Uid] that contains UIDs of all of the entries selected in the filter. For example, join #TempUids on #TempUids.Uid = AUV.ProjectUID
  • To get Timeline dates use @StartDate and @EndDate variables. For example, where [AssignmentStartDate] ⇐ @EndDate and [AssignmentFinishDate] >= @StartDate

Please note: Once you made any changes to the SQL tab, you will no longer be able to edit anything on the Fields or Filters tab.

Example

For a table widget that displays Project Name, Finish Date, Work and Cost and displays only projects that intersect with a period selected in the dashboard Timeline, query can be as simple as:

SELECT
 proj.ProjectName AS [Project Name]
 ,tsk.TaskFinishDate AS [Finish]
 ,proj.ProjectCost AS [Cost]
 ,proj.ProjectWork AS [WORK]
FROM MSP_EPMProject_UserView proj
JOIN #TempUids uids ON uids.Uid = proj.ProjectUID
JOIN MSP_EpmTask tsk ON proj.ProjectUid = tsk.ProjectUid AND tsk.TaskIsProjectSummary = 1
WHERE tsk.TaskStartDate <= @EndDate AND tsk.TaskFinishDate >= @StartDate
ORDER BY proj.ProjectName

Was this article helpful?

Table of contents

    Back To Top