Has anybody ever tried to create a pivot chart in Excel based upon the workflow tasks list in SharePoint?
One of my users is requesting a chart showing all users associated with active or completed workflows AND they want to see the time between when it was assigned and when it was completed.
I figured this would be easy, things I have tried:
- Pulling list in view odata: success
- Creating chart from imported area: failure (workflow task list only has ID.)
- Pull in Userinformationlist and create a relationship between assigned ID and name in user informatiion list: failure (cannot merge named ranges from external data)
- Create calculated fields in workflow task list: failure (excel throws error importing a calculated field)
I would use the history list, but this gets purged in our system every 60 days, and I cannot change.
Not trying to re-invent the wheel here, but the easiest way would seem to just create an event handler to write the values I want to another list on every change of the workflow history list, then pull that into my Excel sheet later. I am not an expert with pivot tables and I have not been able to lookup the UserID value against the UIC with any success.