When Widget Editor is in Advanced mode you can edit widget XML definition on the Widget XML tab. This can be useful in case you want to add multiple data sources or add widget parameters.
Please be careful while editing XML definitions, since this can break your EPM Pulse instance.
Data Sources
By default widget editor adds only one data source to the widget XML, however, you can manually add more data sources directly to the widget XML. In order to do that just add a new data source node under the DataSourcesList node.
For example, a widget containing 2 data sources DataSourcesList node should look like:
<DataSourcesList xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<d2p1:KeyValuePairOfstringstring>
<d2p1:key>Reporting</d2p1:key>
<d2p1:value>context.SQLQuery = "select ProjectOwnerName, ProjectName from MSP_EPMProject_UserView proj \
join #TempUids uids on uids.Uid = proj.ProjectUID";</d2p1:value>
</d2p1:KeyValuePairOfstringstring>
<d2p1:KeyValuePairOfstringstring>
<d2p1:key>Reporting</d2p1:key>
<d2p1:value>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 + "'";</d2p1:value>
</d2p1:KeyValuePairOfstringstring>
</DataSourcesList>Type of the data source and its actual connection string is determined from the key node of data source XML. By default EPM Pulse is provided with 2 data sources:
- Reporting - SQL type data source connected to reporting database of PWA.
- OData - generic OData data source.
Widget Parameters
To make widgets configurable, you can add widget parameters to the widget XML, so users will be able to make adjustments while editing dashboards.
All parameters should be added under Parameters node of XML definition. Each parameter should be provided with the following properties:
|
Property |
Description |
| Data | Default value |
| Data Type | Data type |
| Group | Tab name where parameter will be displayed |
| Id | Property ID to refer to the parameter from data source script |
| Label | Display name of the parameter |
| HandlerCommand | Optional property that can be used in Select or AutoComplete parameters to populate control with values |
| IsMultichoice | Optional property that can be used in Select parameter to enable selection of multiple values |
| AllowDuplicates | Optional property that can be used along with multi choice Select parameter to allow selecting the same value multiple times |
| Options | Optional property that can be used in Select or AutoComplete parameters to populate control with values |
| DataSource | Optional property that can be used for populating parameter values dynamically with a custom data handler |
Below is a list of sample parameters that can be used in widget XML:
|
Description |
XML |
| Checkbox | <Parameter> <Data xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:boolean">true</Data> <DataType>Bool</DataType> <Group>Filters</Group> <Id>UseTimeline</Id> <Label>Use Timeline Dates</Label> <Type>Checkbox</Type> </Parameter> |
| Input | <Parameter> <Data xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">#ff7f0e,#2ca02c,#c7c7c7,#d62728</Data> <DataType>String</DataType> <Group>RAG</Group> <Id>RAGColors</Id> <Label>Colors</Label> <Type>Input</Type> </Parameter> |
| Autocomplete with handler command | <Parameter i:type="AutoCompleteParameter"> <Data xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string" /> <DataType>String</DataType> <Group>Data</Group> <Id>GroupField</Id> <Label>Group By</Label> <Type>AutoComplete</Type> <HandlerCommand>GetProjectFields</HandlerCommand> <Options xmlns:d4p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" i:nil="true" /> </Parameter> |
| Multichoice Select parameter | <Parameter i:type="SelectParameter"> <Data xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string" /> <DataType>String</DataType> <Group>Fields</Group> <Id>Fields</Id> <Label>Fields</Label> <Type>Select</Type> <AllowDuplicates>true</AllowDuplicates> <DataSources xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" i:nil="true" /> <HandlerCommand>GetProjectFields</HandlerCommand> <IsMultichoise>true</IsMultichoise> <Options xmlns:d4p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" i:nil="true" /> </Parameter> |
| Select with predefined values | <Parameter i:type="SelectParameter"> <Data xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">count</Data> <DataType>String</DataType> <Group>Data</Group> <Id>Function</Id> <Label>Function</Label> <Type>Select</Type> <HandlerCommand i:nil="true" /> <IsMultichoise>false</IsMultichoise> <Options xmlns:d4p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d4p1:KeyValueOfstringanyType> <d4p1:Key>Count</d4p1:Key> <d4p1:Value xmlns:d6p1="http://www.w3.org/2001/XMLSchema" i:type="d6p1:string">count</d4p1:Value> </d4p1:KeyValueOfstringanyType> <d4p1:KeyValueOfstringanyType> <d4p1:Key>Sum</d4p1:Key> <d4p1:Value xmlns:d6p1="http://www.w3.org/2001/XMLSchema" i:type="d6p1:string">sum</d4p1:Value> </d4p1:KeyValueOfstringanyType> <d4p1:KeyValueOfstringanyType> <d4p1:Key>Avg</d4p1:Key> <d4p1:Value xmlns:d6p1="http://www.w3.org/2001/XMLSchema" i:type="d6p1:string">avg</d4p1:Value> </d4p1:KeyValueOfstringanyType> <d4p1:KeyValueOfstringanyType> <d4p1:Key>Min</d4p1:Key> <d4p1:Value xmlns:d6p1="http://www.w3.org/2001/XMLSchema" i:type="d6p1:string">min</d4p1:Value> </d4p1:KeyValueOfstringanyType> <d4p1:KeyValueOfstringanyType> <d4p1:Key>Max</d4p1:Key> <d4p1:Value xmlns:d6p1="http://www.w3.org/2001/XMLSchema" i:type="d6p1:string">max</d4p1:Value> </d4p1:KeyValueOfstringanyType> </Options> </Parameter> |
| Project Filter | <Parameter> <Data i:nil="true" /> <DataType>String</DataType> <Group>Filters</Group> <Id>FieldsFilter</Id> <Label i:nil="true" /> <Type>FieldsFilter</Type> </Parameter> |
| Resource Filter | <Parameter> <Data i:nil="true" /> <DataType>String</DataType> <Group>Resource Filter</Group> <Id>ResourceFieldsFilter</Id> <Label i:nil="true" /> <Type>ResourceFieldsFilter</Type> </Parameter> |
| Task Filter | <Parameter> <Data i:nil="true" /> <DataType>String</DataType> <Group>Filter</Group> <Id>TaskFieldsFilter</Id> <Label i:nil="true" /> <Type>TaskFieldsFilter</Type> </Parameter> |
| Drill Down URL | <Parameter> <Data i:nil="true" /> <DataType>String</DataType> <Group>Drill Down</Group> <Id>DrillDownUrl</Id> <Label>Drill Down URL</Label> <Type>Input</Type> </Parameter> |
Below is a list of handler commands that are currently available:
| Handler Command | Description |
|---|---|
| GetProjectFields | Returns a list of Project fields |
| GetResourceFields | Returns a list of Resource fields |
| GetTaskFields | Returns a list of Task fields |
| GetParamData | Returns list of values from parameter data source |
Accessing Parameters From Data Source
Parameters can be accessed from a data source via GetWidgetParameter method of the context object.
For example, the following data source returns a value of the widget parameter with ID 'TestParameter':
context.SQLQuery = "select @test";
context.AddSQLParameter('test', context.GetWidgetParameter('TestParameter'));However, there are several parameters that can be accessed in a different way.
Project Filter Parameter
Project Filter parameter adds an additional temporary table #TempUidsReduced to the SQL context, so that it could be used instead of #TempUids table.
context.SQLQuery = "select ProjectOwnerName, ProjectName from MSP_EPMProject_UserView proj \ join #TempUidsReduced uids on uids.Uid = proj.ProjectUID";
Resource Filter Parameter
Resource Filter parameter adds an additional temporary table #AdditionalTempUidsReduced to the SQL context.
context.SQLQuery = "select ResourceName from MSP_EPMResource_UserView res \ join #AdditionalTempUidsReduced ruids on ruids.Uid = res.ResourceUID";
Task Filter Parameter
Task Filter parameter adds an additional temporary table #TaskTempUids to the SQL context.
context.SQLQuery = "select TaskName from MSP_EPMTask_UserView task \ join #TaskTempUids tuids on tuids.Uid = task.TaskUID";
Drill Down Parameter
There is no need to use Drill Down parameter in the code explicitly, it will be automatically used as a widget-level drill down URL in case UrlSettings property is added to the widget.
Multichoice Select Parameter
Multichoice Select parameter returns JSON string that contains selection made in the control. Data from this parameter can be handled as follows:
var fields = context.ParseJson(context.GetWidgetParameter("Fields"));Dynamic Parameters
Starting from version 3.1 widget parameters can use custom data handlers that allow populating widget parameters dynamically. In order to use this feature parameter should use the GetParamData Data Command and should be provided with at least one data source within DataSources node that returns data set containing 2 columns: Text and Value. The data source can be written the same way as other widget data sources.
Example below adds the AutoComplete parameter to a widget and populates it with all project fields of date or datetime types:
<Parameter i:type="AutoCompleteParameter">
<Data xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string" />
<DataType>String</DataType>
<Group>Fields</Group>
<Id>GroupBy</Id>
<Label>By Date</Label>
<Type>AutoComplete</Type>
<DataSources xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<d4p1:KeyValuePairOfstringstring>
<d4p1:key>Reporting</d4p1:key>
<d4p1:value>context.SQLQuery = "select COLUMN_NAME as [Value] from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'MSP_EPMProject_UserView' and DATA_TYPE in ('datetime','date') order by COLUMN_NAME";
context.GetData();
var result = [];
context.AppendFromDataTable(result);
for (var col in result){
switch (result[col]["Value"]){
case "ProjectStartDate":
result[col]["Value"] = "TaskStartDate";
break;
case "ProjectFinishDate":
result[col]["Value"] = "TaskFinishDate";
break;
}
result[col]["Text"] = context.GetProjectFieldDisplayName(result[col]["Value"]);
}
return result;
</d4p1:value>
</d4p1:KeyValuePairOfstringstring>
</DataSources>
<HandlerCommand>GetParamData</HandlerCommand>
<Options xmlns:d4p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" i:nil="true" />
</Parameter>