You’re offline. This is a read only version of the page.
14
In my Sharepoint, I have an issue tracker (see image 1). It includes a list of issues from different departments along with their status. I want to send my leadership a pivot table-style report each month detailing the amount of this issue for each department and its status (see image 2). Is there a way to use power automate to execute this action? I tried a few techniques utilizing the Create HTML action, but they didn't work for me.
| New | In Progress | Completed | Total | |
| Department 1 | 3 | 3 | 3 | 9 |
| Department 2 | 3 | 3 | 3 | 9 |
| Department 3 | 3 | 3 | 3 | 9 |
| Total | 9 | 9 | 9 | 27 |
Awesome! its a new learning experience. Thanks again for sharing such a cool stuff 🙂
Thanks & regards,
Manish Solanki
Hi @ManishSolanki , Thank you so much. It is fantastic, I got the result from my flow.
Hi @SudeepGhatakNZ , For the academic interest, can you please let me know how to get the result by sharepoint virew?
Good one @ManishSolanki , I was about to post my solution but you beat me to it.
I used a SharePoint view to group the list items by Department and Status.
That saves me from calculating totals in Power Automate. I'll blog my solution 🙂 now that you have this one covered.
Hi @psrini20
I tried creating a flow which will help you. In the sample flow, I have created a SharePoint list 'HelpDeskTickets' with two columns 'Department' (single line of text) and 'Status' (choice) fields:
Here is the flow:
1. Firstly, we will create a recurrence flow and set the frequency for it:
You may choose different trigger point as per your need.
2. Next, we will initialize 4 variables to store the values of Department, count for New/In progress/Completed tickets count:
3. Now, we will fetch the records or list items from SharePoint list using 'Get items' action:
You may write filter query to fetch past month data and so on.
4. We will use 'Select' action to get the value of Department field in a collection from the output of 'Get items' action. Click on 'Switch to text mode' and select Department field from Dynamic content window:
5. We will find the distinct Department using union() formula and then iterate the distinct Department items using 'Apply to each' action:
Expression used in above screenshot:
union(body('Select'),body('Select'))
We will store the value of each iterated department in 'Compose' action using expression as shown below:
Expression used in above screenshot:
item()
Continue inside 'Apply to each', now add 3 parallel action. In each action, we will count the no of tickets for each category (New, In progress & Completed) and overall total count for each category:
Branch 1 (New):
Using 'Filter array' action, we will filter the records on the basis of Department & status. Additionally, we will update the overall count variable for New tickets category:
To add filter expression, click on 'Edit on advanced mode' link at the bottom & add the below expression:
Share
Report
All responses (
Answers (
@and(equals(item()?['Department'], outputs('Compose')),equals(item()?['Status/Value'],'New'))Update the overall New category ticket count using 'Increment variable' action:
Expression used for finding the count is:
length(body('Filter_array'))
Branch 2 (In Progress):
Using 'Filter array' action, we will filter the records on the basis of Department & status. Additionally, we will update the overall count variable for 'In progress' tickets category:
To add filter expression, click on 'Edit on advanced mode' link at the bottom & add the below expression:
@and(equals(item()?['Department'], outputs('Compose')),equals(item()?['Status/Value'],'In progress'))Update the overall 'In Progress' category ticket count using 'Increment variable' action:
Expression used for finding the count is:
length(body('Filter_array_2'))
Branch 3 (Completed):
Using 'Filter array' action, we will filter the records on the basis of Department & status. Additionally, we will update the overall count variable for Completed tickets category:
To add filter expression, click on 'Edit on advanced mode' link at the bottom & add the below expression:
@and(equals(item()?['Department'], outputs('Compose')),equals(item()?['Status/Value'],'Completed'))Update the overall Completed category ticket count using 'Increment variable' action:
Expression used for finding the count is:
length(body('Filter_array_3'))
Inside 'Apply to each' action, now add 'Append to array' action to save the record for each department:
Following expression is added inside 'Append to array variable' action:
{
"Department": @{outputs('Compose')},
"New": @{length(body('Filter_array'))},
"InProgress": @{length(body('Filter_array_2'))},
"Completed": @{length(body('Filter_array_3'))}
}you may just copy & paste the above expression in your flow.
The structure of 'Apply to each' action will look like this:
6. After closing 'Apply to each' action, we will update the overall counts for all categories and append to the array. This action should be called outside the 'Apply to each' action:
Expression used in above screenshot is:
{
"Department": "Total",
"New": @{variables('varNewTicketCount')},
"InProgress": @{variables('varInProgressTicketCount')},
"Completed": @{variables('varCompletedCount')}
}
7. Finally, we will create an html table from the DepartmentStats variable using 'Create HTML table' action:
Expression used for each header is mentioned below:
(blank): item()?['Department']
New: item()?['New']
InProgress: item()?['InProgress']
Completed: item()?['Completed']
Total: add(add(item()?['New'],item()?['InProgress']),item()?['Completed'])
You can send the output of 'Create html table' action in the email body as required.
Here is the output generated by this flow:
I know the above flow is lengthy but this will give you some idea in designing the flow as per the requirement 🙂
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks
