How Can We Help?
User Stats Monthly
This scenario is a is a monthly report that displays stats regarding the users calls, meetings and messages.
Example
Top section: number of each type in the period (weekly or months), and the percentage is change since last time period – green for increase, red (with a – in front) for decrease.
Lower section:
P2P calls %
Value is CallsVsMeetingsPercentageChange, calculated as:
PL/SQLPL/SQL
round(CallsVsMeetingsPercentage - CallsVsMeetingsPercentageLastPeriod) * 100, 2) as CallsVsMeetingsPercentageChangeCallsVsMeetingsPercentage is
PL/SQLPL/SQL
(CallsThisPeriod as float) / (CallsThisPeriod + MeetingsThisPeriod)Meetings %
Value is MeetingsVsCallsPercentageChange, calculated as:
PL/SQLPL/SQL
round(MeetingsVsCallsPercentage * 100, 2) as MeetingsVsCallsPercentage ,round((MeetingsVsCallsPercentage - MeetingsVsCallsPercentageLastPeriod) * 100, 2) as MeetingsVsCallsPercentageChangeP2P vs Meetings
Takes CallsVsMeetingPercentage, MeetingsVsCallsPercentage and shows proportion of calls vs meetings
Chart is done in https://quickchart.io
Required Settings
| Setting | Value |
|---|---|
| StoredProcedureName | automation.SpGetUserStatsForMonth |
| IsSentOnce | False |
SQL Query
TextText
CREATE PROCEDURE [automation].[SpGetUserStatsForMonth]
AS
BEGIN
EXEC automation.SpGetUserActivityStatsBase 28
END
GO
Example Data Model
TextText
{
"RowsWithAdditionalProperties": [
{
"Value": {
"UserFirstName": "Oliver",
"CallCount": 176,
"CallsLastPeriod": 35,
"CallCountPercentageChange": 402.86,
"MeetingCount": 33,
"MeetingsLastPeriod": 42,
"MeetingCountPercentageChange": -21.43,
"TeamChatCount": 0,
"TeamChatMessagesLastPeriod": 1,
"TeamChatCountPercentageChange": -100,
"PrivateChatCount": 1951,
"PrivateChatMessageCountsLastPeriod": 1095,
"PrivateChatCountPercentageChange": 78.17,
"CallsVsMeetingsPercentage": 84.21,
"CallsVsMeetingsPercentageChange": 38.76,
"MeetingsVsCallsPercentage": 15.79,
"MeetingsVsCallsPercentageChange": -38.76
}
}
]
}
Example Card Template
Basic format to be customised
TextText
{
"$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
"version": "1.2",
"type": "AdaptiveCard",
"originator": "OriginatorIdPlaceholder",
"body": [
{
"type": "Container",
"backgroundImage": {
"url": "https://cdn.modalitysystems.com/TeamworkAnalytics/Automation/Backgrounds/header_lightblue.png"
},
"items": [
{
"type": "ColumnSet",
"columns": [
{
"type": "Column",
"items": [
{
"type": "Image",
"url": "https://cdn.modalitysystems.com/Icons/ic_fluent_people_team_filled.png",
"size": "Small"
}
],
"width": 30
},
{
"type": "Column",
"items": [
{
"type": "TextBlock",
"text": "User Activity",
"horizontalAlignment": "Right",
"wrap": true,
"color": "Dark"
},
{
"type": "TextBlock",
"text": "MONTHLY REPORT",
"horizontalAlignment": "Right",
"spacing": "None",
"size": "Large",
"color": "Attention",
"wrap": true,
"weight": "Bolder"
}
],
"width": 70
}
]
}
]
},
{
"type": "TextBlock",
"text": "${RowsWithAdditionalProperties[0].Value.UserFirstName}, here's your Teams report:",
"wrap": true,
"size": "Large",
"color": "Accent"
},
{
"type": "ColumnSet",
"separator": true,
"spacing": "ExtraLarge",
"columns": [
{
"type": "Column",
"items": [
{
"type": "TextBlock",
"text": "P2P Calls",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": "Meetings",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": "Channel Messages",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": "Chat Messages",
"spacing": "Small",
"size": "Medium"
}
],
"width": "stretch",
"spacing": "Medium"
},
{
"type": "Column",
"items": [
{
"type": "TextBlock",
"text": " ${RowsWithAdditionalProperties[0].Value.CallCount}",
"size": "Medium"
},
{
"type": "TextBlock",
"text": " ${RowsWithAdditionalProperties[0].Value.MeetingCount}",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": " ${RowsWithAdditionalProperties[0].Value.TeamChatCount}",
"size": "Medium",
"spacing": "Small"
},
{
"type": "TextBlock",
"text": " ${RowsWithAdditionalProperties[0].Value.PrivateChatCount}",
"spacing": "Small",
"size": "Medium"
}
],
"width": "auto"
},
{
"type": "Column",
"width": "auto",
"items": [
{
"type": "TextBlock",
"text": "${if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.CallCountPercentageChange + '%'))}",
"color": "${if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange > 0, 'good', 'attention'))}",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": "${if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange + '%'))}",
"color": "${if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange > 0, 'good', 'attention'))}",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": "${if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange + '%'))}",
"color": "${if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange > 0, 'good', 'attention'))}",
"spacing": "Small",
"size": "Medium"
},
{
"type": "TextBlock",
"text": "${if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange + '%'))}",
"color": "${if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange > 0, 'good', 'attention'))}",
"spacing": "Small",
"size": "Medium"
}
]
}
]
},
{
"type": "TextBlock",
"text": "How are people talking to each other?",
"wrap": true,
"size": "Large",
"color": "Accent",
"spacing": "ExtraLarge"
},
{
"type": "ColumnSet",
"separator": true,
"columns": [
{
"type": "Column",
"width": "auto",
"items": [
{
"type": "TextBlock",
"text": "${if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange >= 1000, '1000+%', if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange >= 0, RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange + '%', RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange + '%')))}",
"horizontalAlignment": "Center",
"wrap": true,
"size": "ExtraLarge",
"color": "${if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange > 0, 'good', 'attention'))}"
},
{
"type": "TextBlock",
"text": "P2P Calls",
"horizontalAlignment": "Center",
"weight": "Bolder",
"wrap": true,
"spacing": "Small"
}
]
},
{
"type": "Column",
"width": "stretch",
"items": [
{
"type": "TextBlock",
"text": "\r\n",
"horizontalAlignment": "Center",
"weight": "Bolder",
"wrap": true,
"spacing": "Small"
},
{
"type": "Image",
"horizontalAlignment": "Center",
"wrap": true,
"spacing": "None",
"url": "https://quickchart.io/chart?c=%7B%0A%20%20%22type%22%3A%20%22doughnut%22%2C%0A%20%20%22data%22%3A%20%7B%0A%20%20%20%20%22datasets%22%3A%20%5B%7B%0A%20%20%20%20%20%20%22label%22%3A%20%22foo%22%2C%0A%20%20%20%20%20%20%22data%22%3A%20%5B${RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentage}%2C%20${RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentage}%5D%2C%0A%20%20%20%20%20%20%22backgroundColor%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%22rgba(255%2C%2099%2C%20200%2C%200.2)%22%2C%0A%20%20%20%20%20%20%20%20%22rgba(0%2C%200%2C%200%2C%200.1)%22%0A%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%22textcolor%22%3A%5B%22%23000555%22%2C%22%23555555%22%5D%2C%0A%20%20%20%20%20%20%22borderWidth%22%3A%200%2C%0A%20%20%20%20%7D%5D%20%0A%20%20%7D%2C%0A%20%20%22options%22%3A%20%7B%0A%20%20%20%20%22rotation%22%3A%20Math.PI%2C%0A%20%20%20%20%22circumference%22%3A%20Math.PI%2C%0A%20%20%20%20%22cutoutPercentage%22%3A%2075%2C%0A%20%20%20%20%22plugins%22%3A%20%7B%0A%20%20%20%20%20%20%22datalabels%22%3A%20%7B%20%22display%22%3A%20false%20%7D%2C%0A%20%20%20%20%20%20%22doughnutlabel%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22labels%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22text%22%3A%20%22%5CnP2P%20vs%20Meetings%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22color%22%3A%20%22%23aaa%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22font%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22size%22%3A%20%2225%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22text%22%3A%20%22%5Cn${RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentage}%25%20-%20${RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentage}%25%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22font%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22size%22%3A%20%2240%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%09%09%0A%20%20%7D%0A%7D"
}
]
},
{
"type": "Column",
"width": "auto",
"items": [
{
"type": "TextBlock",
"text": "${if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange >= 1000, '1000+%', if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange >= 0, RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange + '%', RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange + '%')))}",
"horizontalAlignment": "Center",
"wrap": true,
"size": "ExtraLarge",
"color": "${if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange > 0, 'good', 'attention'))}"
},
{
"type": "TextBlock",
"text": "Meetings",
"horizontalAlignment": "Center",
"weight": "Bolder",
"wrap": true,
"spacing": "Small"
}
]
}
]
}
]
}