- When Widget Editor is in Advanced mode you can edit widget Data Sources on the Data Source tab.
Data Source is a script with JS-like syntax backed by Jint. EPM Pulse API is exposed via context object accessible from the script.
Currently 2 types of Data Sources are supported:
- SQL
- OData
SQL Data Sources
Data Sources of that type must set up SQLQuery property of the context variable.
Minimal Data Source script of that type may look like:
context.SQLQuery = "select ProjectName as [Project Name] from MSP_EPMProject_UserView";
SQL query must return a single dataset, i.e. there should be only one “select” statement that returns results. Results must be in a format required by the widget type.
EPM Pulse exposes the #TempUids temp table to the context of SQL query that contains UID's of the items (Projects or Resources) selected in the Dashboard Filter that allows filtering data by that selection.
Minimal Data Source script that uses #TempUids table may look like:
context.SQLQuery = "select ProjectName as [Project Name] from MSP_EPMProject_UserView proj \ join #TempUids uids on uids.Uid = proj.ProjectUID";
OData Data Sources
Data Sources of that type must set up at least one OData URL using the AddODataUrl method of the context object.
Minimal Data Source script of that type may look like:
context.AddOdataUrl("http://myserver/pwa/_vti_bin/listdata.svc/Pages");If multiple URL's are added to the context object, then all these URL's will be queried and results will be returned in a single data set. Results must be in a format required by widget type.
In On-Premise installations OData feeds will be queried under Application Pool account, in Online installations under the synchronization account.
Combining Multiple Data Sources
For instructions on how to add additional data sources please check the documentation on Widget XML.
On creation of a new widget, Widget Editor adds a single Data Source of SQL type to the widget definition. But widgets may have multiple Data Sources that will be executed sequentially and can be added to the widgets by editing their XML Definition. If the widget contains multiple data sources, then its visualization will be built using a data set returned by the last data source.
To pass results between data sources use the GetDataTableRowValue and GetDataTableRowsCount methods of the context object.
For example, if the first data source looks like:
context.SQLQuery = "select ProjectOwnerName, ProjectName from MSP_EPMProject_UserView proj \ join #TempUids uids on uids.Uid = proj.ProjectUID";
then values of the resultant data set can be obtained in the next data source as follows:
var count = context.GetDataTableRowsCount();
var result = "";
for (var i = 0; i < count; i++){
result += context.GetDataTableRowValue(i,0) + " is the owner of the " + context.GetDataTableRowValue(i,1) + " project. ";
}
context.SQLQuery = "select '" + result + "'";In-place Data Source Execution and Modification of Result Data Set
Starting from version 3.1 data sources can be executed in scrip-like manner. That allows to retrieve data immediately and modify result data set inside the script.
To execute the data source immediately use the GetData method of the context object. In order do put data into JS object use the AppendFromDataTable method of the context object. To finish data source script execution and return result data set use the return keyword.
For example:
context.SQLQuery = "select top 2 ProjectName from MSP_EPMProject";
var result = [];
context.GetData()
context.AppendFromDataTable(result);
result.push({ProjectName:'This is a new row for ProjectName column'});
result.push({ProjectName:'Another row',CustomColumn:'This is a new column added from data source script'});
return result;You can call GetData function as many times as you need inside a single data source.
In case you need to apply string operations (like indexOf, replace, toLowerCase, etc.) to string-like fields in the result data set, you need to convert that fields to string explicitly, for example:
context.SQLQuery = "select ProjectName from MSP_EPMProject";
var result = [];
var projects = []
context.GetData()
context.AppendFromDataTable(projects);
for (var project in projects){
result.push({ProjectName:String(projects[project]['ProjectName']).toLowerCase()});
}
return result;Server-Side Grouping
Starting from version 3.1 table widgets support server-side grouping. In order to group data set the ServerGrouping property needs to be declared within data source. The property accepts the associative array that describes grouping in the following format:
[
{
field:'<Field Name to Group By>',
aggregates:{
'<Field Name>':'<aggregate>'
}
}
]Note that <Field Name to Group By> field will be hidden from the table widget.
Currently the following aggregates supported:
- first
- count
- sum
- avg
- max
- min
For example, the following data source returns list of projects and project costs grouped by project departments with a count of projects in each department and sum of project costs per department:
context.SQLQuery = "select \
isnull([Project Departments],'(Not Set)') as [Project Departments] \
,[ProjectName] as [Project Name] \
,[ProjectCost] as [Cost] \
,[Project Departments] as [ProjectDepartmentsGrouping] \
from MSP_EPMProject_UserView \
join #TempUids uids on uids.Uid = ProjectUID \
order by [Project Departments], [ProjectName] \
";
context.AddProperty('ServerGrouping',[
{field:'ProjectDepartmentsGrouping',
aggregates:{
'Project Departments':'first',
'Project Name':'count',
'Cost':'sum'
}
}
]);For correct grouping data rows should be ordered by groups within SQL query.
In case you don't need to group some rows then add the Grouping bit column set to 0 to the rows that need to be skipped.
For example:
context.SQLQuery = "select \
isnull([Project Departments],'(Not Set)') as [Project Departments] \
,[ProjectName] as [Project Name] \
,[ProjectCost] as [Cost] \
,[Project Departments] as [ProjectDepartmentsGrouping] \
,cast(1 as bit) as [Groupable] \
from MSP_EPMProject_UserView \
join #TempUids uids on uids.Uid = ProjectUID \
order by [Project Departments], [Project Name] \
";
var result = [];
context.GetData();
context.AppendFromDataTable(result);
context.SQLQuery = "select \
'Total:' as [Project Departments] \
,cast(count([ProjectName]) as nvarchar) as [Project Name] \
,sum([ProjectCost]) as [Cost] \
,'' as [ProjectDepartmentsGrouping] \
,cast(0 as bit) as [Groupable] from MSP_EPMProject_UserView \
join #TempUids uids on uids.Uid = ProjectUID";
context.GetData();
context.AppendFromDataTable(result);
context.AddProperty('ServerGrouping',[
{field:'ProjectDepartmentsGrouping',
aggregates:{
'Project Departments':'first',
'Project Name':'count',
'Cost':'sum'
}
}
]);
return result;Adding SQL Variables
Besides editing a SQL query from the data source script explicitly using string concatenation, it is also possible to expose variables into SQL query context using AddSQLParameter method of the context object.
For example,
context.SQLQuery = "select @FromJS";
context.AddSQLParameter('FromJS', 'This variable comes from JS script');Visualization Properties
Depending on a widget type widgets may have additional properties that allow adjusting various visualization settings like data formats, colors, tooltip formats, etc. These properties can be added using the AddProperty method of the context object.
Data Formats
The way of setting up data formats depends on the widget type. Below is a list of supported formats:
| Format Code | Description |
|---|---|
| {0} | Plain text |
| {0:html} | HTML text |
| {0:#,0} | Integer |
| {0:#,0.00} | Decimal |
| {0:c} | Cost |
| {0:#,0.##}>#/th### | Percentage |
| {0:#,0.##} hrs | Duration |
| {0:d} | Date |
Tables
To set up data format for table visualizations (Table, Heatmap and Timeline containing table) use the ColumnFormats property.
For example,
context.SQLQuery = "select ProjectName, ProjectStartDate, ProjectCost, ProjectWork, ProjectPercentCompleted from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty('ColumnFormats', ['{0}', '{0:d}', '{0:c}', '{0:#,0.##} hrs', '{0:#,0.##}%']);Single Value
To set up data format for single value visualizations (Scalar and Gauge widget types) use the Format property.
For example,
context.SQLQuery = "select ProjectCost from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty('Format', '{0:c}');Two-axis Visualizations
To set up data format for two-axis visualizations (Pie, Bar, Column, Area, Line and Stock widget types) use 2 properties Format for y-axis formatting and CategoryFormat for x-axis formatting.
For example,
context.SQLQuery = "select ProjectStartDate, ProjectCost from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty('Format', '{0:c}');
context.AddProperty('CategoryFormat', '{0:d}');Scatter Chart
To set up data format for the Scatter chart use the Format property that sets up formatting for both axis.
For example,
context.SQLQuery = " \
select \
tsk.TaskName as [Name], \
tsk.TaskBaseline0Work as [Estimated Work, hrs], \
tsk.TaskActualWork as [Actual Work, hrs] \
from MSP_EpmTask_UserView tsk \
JOIN #TempUids uids ON uids.Uid = tsk.ProjectUID \
where tsk.TaskIsActive = 1 and tsk.TaskIsSummary = 0 and tsk.TaskIsMilestone = 0 and tsk.TaskDuration > 0 and tsk.TaskPercentCompleted = 100.0";
context.AddProperty("Format", "{0:#,0.##} hrs");Bubble Chart
To set up data format for Bubble chart use 2 properties Format for x-axis and AdditionalFormat for y-axis.
For example,
context.SQLQuery = " \
select rsk.Impact as [Impact], rsk.Probability * 100.0 as [Probability], count(*) \
from MSP_WssRisk rsk \
inner join #TempUids as uids on uids.Uid = rsk.ProjectUID \
group by rsk.Impact, rsk.Probability";
context.AddProperty("Format", "{0:#,0.##}");
context.AddProperty("AdditionalFormat", "{0:#,0.##}%");Tooltip Templates
TooltipTemplate property allows specifying custom templates for chart tooltips. It is possible to use the following variables in the templates:
- value - the point value. Value dimensions are available as properties, for example, value.x and value.y
- category - the category name
- series - the data series
- dataItem - the original data item
For example, Pie chart tooltip can be customized as follows:
context.SQLQuery = " \
select 'Over Budget', count(case when (prj.ProjectDuration > prj.ProjectBaseline0Duration) then 1 else null end) \
from MSP_EpmProject_UserView prj \
inner join #TempUids uids on uids.Uid = prj.ProjectUID \
\
union all \
select 'On-Track', count(case when (prj.ProjectDuration <= prj.ProjectBaseline0Duration) then 1 else null end) \
from MSP_EpmProject_UserView prj \
inner join #TempUids uids on uids.Uid = prj.ProjectUID";
context.AddProperty("TooltipTemplate", "#= category #: #= kendo.format('{0:\\#,0.\\#\\#}%', value)#");Colors
Colors property allows overriding default color palette in the charts widgets (Pie, Column, Line, etc.). If this option is defined in the data source, then the visualization will use the specified colors cyclically for series colors. Colors should be specified in HTML format, so you can use both color names and HTML codes.
For example,
context.SQLQuery = "select sum(ProjectCost), sum(ProjectActualCost), sum(ProjectBaseline0Cost) from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty("Colors", ["red", "#0c860c","#1010ff"]);Note: It is recommended to set up a color for Line, Scatter and Stock widget types explicitly in case they have only one data series.
Labels Visibility
ShowLabels property allows disabling the chart labels.
For example,
context.SQLQuery = "select sum(ProjectCost), sum(ProjectActualCost), sum(ProjectBaseline0Cost) from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty("ShowLabels", false);Column Width
Table, Heatmap, and table part of Timeline widgets supports defining columns width from a data source via ColumnWidths property. Widths can be specified in pixels or as percentage, but they should not be used simultaneously.
For example,
context.SQLQuery = "select ProjectName, ProjectOwnerName, ProjectPercentCompleted from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty('ColumnWidths', ['', '250px', '50px']);Consuming Query String Arguments
Widgets can consume query string arguments from the URL via GetRequestParameter method of the context object. This is useful for creating custom drill-downs or interactive filters, please check out Advanced Drill Down and Slicer demo dashboards for usage examples.
Data source in the below example returns a value passed through the test parameter of the query string:
context.SQLQuery = "select @test";
context.AddSQLParameter('test', context.GetRequestParameter('test'));Drill Down Templates
In order to enable drill-down links in the Table, Heatmap or Timeline widget, the data source should be provided with a drill-down template via UrlSettings property. Template syntax is the following:
context.AddProperty('UrlSettings', '{ "UrlIndex": 0, "LinkColumn": "Project Name", "Source": {"{projUid}": "ProjectUID"} }');where:
- UrlIndex - value that determines drill-down type (0 - Project, 1 - Resource, 2 - Issue, 3 - Risk, 5 - Deliverable).
- LinkColumn - name of the column in the resultant data set where the hyperlink will be displayed.
- Source - tags bindings, all of the columns used in the bindings will not be displayed in the widget.
For example, minimal data source with drill-down may look like:
context.SQLQuery = "select ProjectName as [Project Name], ProjectUID from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";
context.AddProperty('UrlSettings', '{ "UrlIndex": 0, "LinkColumn": "Project Name", "Source": {"{projUid}": "ProjectUID"} }');Helper Functions
Below is a list of helper functions exposed via context object:
|
Function |
Description | Usage |
| GetPWAUrl | Returns full PWA URL | context.GetPWAUrl() |
| GetFirstDayOfWeek | Returns first week day for current user basing on his locale | context.GetFirstDayOfWeek() |
| GetWidgetParameter | Returns value of a widget parameter | context.GetWidgetParameter('WidgetParameterID') |
| GetDashboardParameter | Returns value of a dashboard parameter | context.GetDashboardParameter('DashboardParameterID') |
| GetEntityViews | Returns list of available dashboard views |
context.GetEntityViews() ID of current view can be retrieved with context.GetDashboardParameter('EntityViewUid')
|
| FormatString | Returns formatted string | context.FormatString("{0:d} - {1:d}",
[context.GetDashboardParameter("StartDate")
, context.GetDashboardParameter("EndDate")]) |
| GetProjectFieldDisplayName | Returns display name of a project field | context.GetProjectFieldDisplayName('SQLFieldName') |
| GetResourceFieldDisplayName | Returns display name of a resource field | context.GetResourceFieldDisplayName('SQLFieldName') |
| GetTaskFieldDisplayName | Returns display name of a task field | context.GetTaskFieldDisplayName('SQLFieldName') |
| ParseJson | Returns JS object created from JSON string | context.ParseJson('{a:1,b:2}') |
Widget Parameters
For instructions on widget parameters please check Widget XML guide.