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