List by Category widgets (Site List by Category and Project Site List by Category) display a breakdown of the aggregated value of one of the fields of SharePoint list items by another field, like count of issues by status or average risks exposure by category. These widgets have the following parameters:
|
Parameter |
Description |
|
Site 1) |
Site URL to pull the data from. |
|
List Query |
OData query that starts from a list name and determines items that will be displayed in the table. List by Category widgets do not support $select option in OData queries. |
|
Function |
Function that will be applied to the Value column. |
|
Value |
Column name that the Function will be applied to. |
|
Group By |
Column that contains categories to group the aggregated value by. |
|
Format |
Formatting that will be applied to the aggregated value. |
|
Show Subprojects 2) |
Determines whether Project Site widget should pull items from project sites of sub-projects or not. |
1) Is applicable only to Site List widget. Please see Site Parameter for details.
2) Is applicable only to Project Site List widget. Please see Show Subprojects Parameter for details.
Parameters
List Query
List Query is a parameter used to specify OData query. Query should be specified in a relative form, full URL will be determined automatically by the widget.
For example, provide the following as List Query to fetch list of issues for calculating aggregated value:
Issues
List Query parameter also accepts OData Query String Options, except the $select parameter.
For example, provide the following as List Query to calculate the aggregated value for issues that are active or postponed:
Issues?$filter=StatusValue eq '(1) Active' or StatusValue eq '(2) Postponed'
Function
Function is a parameter used to specify a function that will be applied to Value field. The following functions are available:
|
Function |
Description |
|
Count |
Calculates the number of items |
|
Sum |
Calculates the sum of the column specified in the Value parameter |
|
Avg |
Calculates the average of the column specified in the Value parameter |
|
Min |
Returns the minimum value from the column specified in the Value parameter |
|
Max |
Returns the maximum value from the column specified in the Value parameter |
Value
Value is a parameter used to specify OData column name that contains the value to apply aggregation function to.
Group By
Group By is a parameter used to specify the OData column that contains categories to group aggregated value by.
For example, for issues or risks this can be:
StatusValue
Format
Format is a parameter to specify formatting for aggregated value.
Please note: Pie Chart has special behaviour for Percent formatting, in this case it converts metrics value to a percentage.
Examples
Count of Active Issues by Priority
| Parameter |
Value |
|
List Query |
Issues?$filter=substringof( 'active',StatusValue) |
|
Function |
Count |
|
Value |
Title |
|
Group By |
PriorityValue |
Count of Issues by Status
|
Parameter |
Value |
|
List Query |
Issues |
|
Function |
Count |
|
Value |
Title |
|
Group By |
StatusValue |
Average Exposure of Active Risks by Category
|
Parameter |
Value |
| List Query | Risks?$filter=substringof( 'active',StatusValue) |
|
Function |
Avg |
|
Value |
Exposure |
|
Group By |
CategoryValue |
Maximum Impact of Active Overdue Risks by Category
|
Parameter |
Value |
|
List Query |
Risks?$filter=substringof( 'active',StatusValue) and DueDate lt {Now} |
|
Function |
Max |
|
Value |
Impact |
| Group By | CategoryValue |