Add-Ons and Extras
Exchange Mingle Extras with other Minglers around the world
Mingle Add-On
Mingle Add-In for Excel 2010
Mingle Add-On posted September 26, 2011 by Mark Richter, last edited March 30, 2012 , tagged Business Analysis, Continuous Integration, Development, Project Management, QA, TW Approved
2163 Views, 21 Comments
Name:
Mingle Add-In for Excel 2010
Overview:
The Mingle Add-In for Excel 2010 extends Microsoft Excel with direct integration to Mingle. This add-in provides a convenient ribbon bar in the Excel user interface that you use to access features of the add-in. From there you can create, name, save and run MQL queries that extract data from Mingle and bring it into Excel for analysis using all the power of Excel.
- Connect to Mingle directly from Excel
- Optionally run more than one query per worksheet
- Save queries with Excel workbooks (optionally several queries per worksheet)
- Run the same queries again later (refersh)
Basic use:
- Install the add-in
- You may need to adjust the permissions in Excel to allow the add-in to be loaded.
- Click on the Mingle tab on the Excel ribbon.
- Click on the orange Login button, supply the requested information, and click Connect.
- After a few moments the Fetch Cards button on the Mingle ribbon should illuminate. Click it and you'll see a UI for entering MQL commands.
The add-in keeps a log under %USERPROFILE%\AppData\Local\ThoughtWorks\Excel[randomnumber].log. If you need help post here or send an email to me at the adress on my profile and I'll either help you myself or find someone who can. A copy of the log is helpful in such cases.
Installation Guide:
The Mingle Add-In for Excel 2010 uses the One-Click installation technique available for Microsoft Office Add-Ins.
- Since the Add-in uses Mingle’s RESTful API to communicate with Mingle you must have basic authentication enabled on your Mingle server. To enable basic authentication, you need to set the basic_authentication_enabled configuration option to true in the Mingle data directory\config\auth_config.yml file, where Mingle data directory is the path to the Mingle data directory. Only a Mingle server administrator can do this.
- Since this add-in is “unsigned” you’ll need to tell Excel to not to require a signing certificate for this add-in. To do this use File -> Options -> Trust Center -> Add-Ins -> Trust Center Settings and uncheck Require Application Add-ins to be signed by Trusted Publisher.
- Unzip the ExcelAddIn.zip file into a folder anywhere on your hard drive.
- Open this directory and run Setup.exe. Setup may offer to install a couple of software packages used by the Add-in. You need to install these if prompted to do so. These are: Visual Studio Tools for Office 2010 and Microsoft .NET Framework 4 Client Profile.
- Start Excel 2010. You should see a Mingle tab that exposes the Mingle Ribbon.
Installer:
ExcelAddIn.146.zip (329KB)
Author / Copyright Owner:
ThoughtWorks, Inc.
Year Written:
2011
Comment
Hi,
Might be a silly question but I have installed the plugin and I have the ribbon etc. But where do I put the MQL?
I did not see a new formula or anything. Obviously when I click fetch nothing happens as there is no query. I have not seen a readme in the download?
Thanks!
Shane, you need to make sure basic authentication is enabled on the Mingle server. Depending on the version of the Excel add-in that you have it is possible that the connection error from Mingle (an http 401) is being eaten by the add-in and not reported to you. When you click Fetch Cards you shuold see a dialog where you enter MQL. The latest version of the add-in on this thread should report this error.
To enable basic authentication, you need to set the basic_authentication_enabled configuration option to true in the Mingle data directory/config/auth_config.yml file, where Mingle data directory is the path to the Mingle data directory. This is on the Mingle server.
E.g.
<EDITED>
I have found the problem! I had set up basic authentication but I turned on plugin errors in excel and got the below error. A bit cryptic but then by chance I tried making the URL verbose. (I was using "localhost:8888" as the URL)
It turns out that you must insert "http://" at the start of the URL or it will give the below error. :)
Shane, the add-in writes a log here: c:\users\[you]\AppData\Local\ThoughtWorks called ExcelAddIn[random number].log. If you post it here or email it to me I'll take a look right away. The image you posted is an unhandled exception in the add-in. That's not good.
I have built and uploaded a new version of the add-in (build 134) that handles the error you are getting and gives you a message.
Hi Mark,
I have recreated the original bug and the the log file was empty unfortunately. So uncaught and unlogged - very naughty! ;)
When I fix the problem as mentioned in my post above I get success logging so logging is working fine:
C:\Users\spaul\AppData\Local\ThoughtWorks\ExcelAddIn2010634565192685686395.log*** Log opened at 10:54:28 a.m. ***
10/11/2011 10:54:36 a.m. ThoughtWorksMingleLib.MingleServer.Response: Calling Mingle using GET with http://localhost:8888/api/v2/projects.xml
[etc]
Thank you very much for your help! Looking forward to using this plugin. :)
So the plugin has been a really great so far. It is saving me a lot of time. :)
One thing I did notice is that it appears as if a LOT of "recalculating" of your entire spreadsheet occurs when you run or save a query. I can see the calculating status message flash multiple times in excel while it is running.
I have a moderately complex spreadsheet but it only takes fractions of a second to calculate. Refreshing or saving of a simple query would take a minute plus. (a lot more if the result contains a large number of rows)
As a workaround I have turned off "automatic calculation" and this has solves the problem. The queries execute very quickly. Of course I just have to always remember to hit the "calculate now" button once it is done.
One feature that would be very handy would be a "refresh all" button. I have a few spreadsheets with queries on them and the ability to refresh them all in one hit would be neat. :)
Hi Shane
We would be very interested to know what you are doing with your spreadsheet.
Can you let us know what information you are pulling from Mingle and what calculations are you doing?
Thanks
- Suzie
Hi Suzie,
Basically I am just dumping the story data for each release into seperate worksheets. On another sheet I place all the releases that are currently in mingle.
The combination of the two allows me to calculate the various release properties and graph the expected/current velocity for that release etc. The calculations use things such as a "10 day windows" to track and predict progress.
I also calculate things such as 90% confidence of my velocity, what the expected future release progress will be (even if resource numbers change) and of course what the expected production release date of the release is after testing.
The neat thing about this addon is that with only small modifications it has allowed me to reuse my old spreadsheet's calculations with the mingle data. Very pleased about that. :)
I am not sure that this would be possible in mingle itself without me learning Ruby and writing a module which is a future prospect (I was a developer once) but not something I have the time for just now.
The main reason for this is that the "formula properties" I can assign to Cards are quite limited in their expressiveness. They are restricted to very simply mathematical calculations. (probably for good reasons of course) Mql is a little more powerful but still does not have the ability to create the data and therefore charts I need.
Excel helps me get around that limitation while Mingle helps me get around the limitation of excel that working with our ever changing priority lists and backlogs is next to impossible to visualise, update and table properly.
Hi Shane,
The Add-In does not do anything with Excel functions or calculations. All it does is stuff data into a worksheet. If there is a lot of data with your own formulae in play then it's probably the interaction between adding data and the calculation engine that's slow for you. If the calculation engine re-calculates the whole sheet each time we move from cell to cell loading data then, well, things might be slow. :)
Your suggestion for a "refresh the workbook" function is a good one. It would not be hard to do this. All the queries in a workbook are in one well-hidden worksheet.
Thanks for all your feedback It' invaluable.
I think you may be right. Excel does have many foibles after all. If only there was a way to turn off auto calc before the function ran and then on at the end. Unfortunately I know as much about VB in Excel as I do about Ruby! :)
It runs very fast now anyway, I just have to remember to click which is no big deal. :)
We actually wrote the add-in in C# and test it with Ruby. Source is here.
Hi Mark ,
I was fetching the data through Mingle API for my Test Status Report and I am successfully able to pull out data from mingle server by changing the 'basic_authentication' key in Mingle data directory/config/auth_config.yml file to 'true' .
However now when I am trying to deploy the same in production the code is failing because 'basic_authentication' value in Mingle data directory/config/auth_config.yml file is set to 'false' . So I have two questions over here before I ask my client (managing mingle deployment) to change this value
Firstly , Why need to change the value of 'basic_authentication' key in Mingle data directory/config/auth_config.yml file to 'true' ??
Secondly , if we do that then in that case is there any change in the login mechanism and what other repercussion are there ??
Please help me out . Thanks in advance
Kapil
Hi Kapil, the reason basic_authentication is required is so that Mingle will accept requests through its RESTful API. The Excel Add-In uses this API. Here's the documentation. There are no real side effects of this so long as Mingle is using SSL if its traffic is over the internet.'
Thnx Mark for the quick reply .
Hi Mark ,
Is there any other way by which I can get mingle data hitting the APIs without actually turning on value of 'basic_authentication' key in Mingle data directory/config/auth_config.yml file to 'true'
I need to get the data and dump the data in to Excel (my TSR report) on daily basis thus saving the lot of efforts in generation reports .
Thanks
Kapil