Trello task reporting using SQL Server 2012.
Trello – let’s say you want to organize some tasks, like say anything-you-can-think-of, you can use Trello.
We use it to manage our Tasks and Projects in IT. It really is an awesome tool and the price is great, free, my type of budget that, but we need to expand it a bit so we can report it to death. In that way we can prove we are really busy and our nice fat bonuses are justified, and that new HP Elitebook 850.. and my home DSL.. and my cellphone.
So in order to see if I can get a nicer chair and bigger coffee mug we have endeavored to publish our Trello data via SSRS in SharePoint.
1. What do we need?
You will need PowerShell 3.0 on the machine you want to run this one on. You can code some hectic PS stuff and invoke a command on a remote machine with PS 3 installed if you cannot install version 3, but I doubt you will encounter an issue like that.
PS C:\Users\services> $PSVersionTable.PSVersion
Major Minor Build Revision —– —– —– ——– 3 0 -1 -1
You will need SQL Server any flavour, I cannot vouch for all versions, but I assume this will go from 2005 upwards, if it does not, just let me know.
2. Trello
Well, this won’t work if you don’t have it.
3. An understanding of what I am doing.
Each person has a list, see mine “ANS – Adriaan Sullivan”
We have a master “Completed/Achievements” list, when something is finalized we more if from our lists to the Completed list, we don’t close it yet, then at the end of the week we close all the cards in a list from the list dropdown.
You get the picture, but all we want to see is stuff done this week. So if something is in the completed list, well done, if it is not there then I didn’t do anything in the week, open issues are fine, but they need closing, and that is what we are measuring.
4. An understanding of what NOT to do.
What most reporting scripts do for Trello is to useJavascript to parse through the the JSON. It looks something like the following:
For each Board get all lists (1 web call)
For each List get all cards (1 web call)
For each card get card details (1 web call)
Looks simple enough, but we have 2000 cards, so that is 2000 web calls.. and for us this takes around 15 minutes to run.
We love SQL, so SET BASED is better, and faster, using this PowerShell SQL combo our new data populates in 15 seconds.
5. Scripts
You now need some scripts to make life easier, and at your own leisure you can go through them and see if it makes sense to you.
Run the setup SQL script to setup the DB and tables, drop the ps1 in the location you wish to use, makes sure you reference the paths properly in the Trello.ps1 file and the TrelloSQLStuff.sql files.
Adrian