Mingle Add-In for Excel 2010 and 2013

Comments

40 comments

  • Avatar
    Shanepaul

    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! 

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    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.


     basic_authentication_enabled: true
    0
    Comment actions Permalink
  • Avatar
    Shanepaul

    <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. :)

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    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. 

    0
    Comment actions Permalink
  • Avatar
    Shanepaul

    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. :)


     

    0
    Comment actions Permalink
  • Avatar
    Shanepaul

    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. :) 

    0
    Comment actions Permalink
  • Avatar
    Suzie Prince

    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

    0
    Comment actions Permalink
  • Avatar
    Shanepaul

    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. 


     

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    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.

    0
    Comment actions Permalink
  • Avatar
    Shanepaul

    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. :)


     

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    We actually wrote the add-in in C# and test it with Ruby. Source is here.

    0
    Comment actions Permalink
  • Avatar
    Paul Osborn

    I can't get beyond this when I run the setup (the setup ran once to install the Visual Studio / .NET components, but it doesn't look like the Add-In was installed).  The Excel security setting is appropriately set.

     

    ************** Exception Text **************
    System.Security.SecurityException: Customized functionality in this application will not work because the certificate used to sign the deployment manifest for Excel Add-In for Mingle or its location is not trusted. Contact your administrator for further assistance.

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Paul, make sure you've considered item 2 in the installation requirements:

     

    "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."

    0
    Comment actions Permalink
  • Avatar
    Paul Osborn

    Hey - thanks Mark.  Yes indeed - sorry - that was what I meant by "The Excel security setting is appropriately set."  I even changed my registry 'TrustManager' settings to enable escalation on all zones, ensured I am trying to install from a local drive (C), and tried running setup as an Administrator.  This is a bit of a puzzle ...but I am superkeen to get it working - up to now I've been using direct database access (MSSQL adaptor for PostGres, and then linked queries from MSSQL to Excel) to do my reporting!!  A colleague was actually able to make it work - so must be something local to me, but I can't figure out what yet... :(

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Paul, make sure you extract the zip file to a folder in a well known location like your downloads folder. Do not run the add-in installer from the zip file. I know this is a frustrating issue to resolve. i've been there myself. Google is your friend on this one.

    0
    Comment actions Permalink
  • Avatar
    Paul Osborn

    Yes Mark - done all that - extracted and copied to C.  Extensive googling led me to modify my registry, but this is in MyComputer zone anyway so elevation should be enabled automatically.  Even added C drive to my trusted zones (not that that is necessary of course).  Oh well.

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Paul, OK, I'm going to see what I can do with the packaging. Maybe an MSI installer.

    0
    Comment actions Permalink
  • Avatar
    Jensen, Rebecca

    Good Afternoon All,

    I keep having an issue with my queries disappearing. I completely understand that this sounds like user error, but I will write multiple queries across teams in a worksheet. I will hit save and it will be there... for a while. I have not yet found the consistency to what causes them to be lost but I will go back to run them again and they will all be gone. It is a pain to have to put them back in again randomly especially if I am trying to show off the add on.

     

    Other than that I love this add on and am creating some very helpful reports with it.

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Hi Rebecca,

    I'm glad to hear you like this add-in. Queries are saved in the workbook as a hidden worksheet. To help debug this it may be helpful to "unhide" this sheet and observe whether or if its contents get erased.

    According to the Excel 2010 doc, to unhide a sheet:

    1. On the Home tab, in the Cells group, click Format.
    2. Under Visibility, click Hide & Unhide, and then click Unhide Sheet.
    3. In the Unhide box, double-click the name of the hidden sheet you want to display. You can only unhide one sheet at a time.

    Off the top of my head I do not recall the name of the hidden queries sheet, but it should be obvious.

    Aside: Are you interested in seeing this add-in working with Excel 2013? I'm not committing to doing that soon, but I'd love to know if it's worth it to folks.

    0
    Comment actions Permalink
  • Avatar
    Jensen, Rebecca

    Mark,

    Thank you for replying so quickly. I had thought of that but the unhide sheet option is grayed out as though none existed even though there are queries in the workbook. Other thoughts? This happens to our other Mingle Admins as well. I love the functionality and if I can resolve this bit will be using it a lot going forward.

    Thank you, 

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Hi Rebecca,

    I want you to know that I have reproduced the problem you are having with "lost queries" while pursuing a little side project internally here. I'm going to dig in and see about publishing a fix. I suspect that the queries are actually still on the hidden queries worksheet and that the query editor window is simply not retrieving them for some reason.

    --mark

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Hi Rebecca,

    All right I think I know what may be happening. Queries are "tagged" with the Sheet Name when they are saved so that the editor can retrieve queries for the sheet that has focus. If you save queries and subsequently rename the sheet then the edit won't find any queries the next time it opens on that sheet. Please let me know if this is the same issue you are having.

    This is a limitation of the add-in in its current form. It is not something we will resolve soon. If you are seeing "disappearing queries" in other circumstances please do you best to describe the scenario(s) for me. Thanks.

     

    --mark

    0
    Comment actions Permalink
  • Avatar
    Jensen, Rebecca

    Aha!! 

    Mark, thank you so much for looking into that. It may very well be that that is the only time that they have disappeared on me as it's always as I'm building out reports. The funny thing is that it was probably self-perpetuating in that if I lost the queries on a page I sometimes created a new tab so I still had the data until I could build out new queries on another and then I would delete the first and rename the second.

    I'm perfectly ok with this now that I understand the issue and will know to do my naming up front. 

    Thank you again for your help!

    0
    Comment actions Permalink
  • Avatar
    Brian Habermann

    Hello,

     

    We are unable to log in. We receive an error:

     

    The Remote server returned an error: (401) unauthorized. 

     

    My config file on the server looks like this:

     

    password_format:
    basic_authentication_enabled: true
    basic_authentication:
    authentication:
    ldap_settings:
    #ldapserver:
    #ldapport:
    #ldapbinduser:
    #ldapbindpasswd:
    #ldapbasedn:
    #ldapfilter:
    #ldapobjectclass:
    #ldap_map_fullname:
    #ldap_map_mail:
    #ldapgroupobjectclass:
    #ldapgroupdn:
    #ldapgroupattribute:
    cas_settings:
    #cas_port:
    #cas_host:
    #cas_uri:

     

    attached is my log file.




    ExcelAddIn2010635174304505103386.log
    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Hi Brian,

    Since the Add-in uses Mingle’s RESTful API to communicate with Mingle you must have basic authenticationenabled 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.

    Refer to this.

    Mark

    0
    Comment actions Permalink
  • Avatar
    Brian Habermann

    In my post, you can clearly see that I've done that already: basic_authentication_enabled: true

    What else could it be?

    Thank you,

     

    Brian

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    Sorry, Brian. I missed it. From looking over the log I see that we are not finding any Mingle projects on the Mingle server, possibly because "bhabermann" does not have access to any. Could this be the issue? The solution would be to login to Mingle as an administrator and grant "bhabermann" access to one or more projects. It seems odd that this would be the case, but the log seems to indicate it.

    0
    Comment actions Permalink
  • Avatar
    Brian Habermann

    Hello,

     

    I am an Admin and can access all the projects through the web.

    0
    Comment actions Permalink
  • Avatar
    Mark Richter

    OK. Have you used "curl" before? I suggest trying to run the following query using curl like this:

    curl --user bhabermann:secret http://<your mingle server>:<your port>/api/v2/projects/<mingle project id>/cards/execute_mql.xml?mql=select count(*)

    where "your mingle server" is the base URI, "your port" is the port where Mingle is (if applicable), "mingle project id" is the project_id of your Mingle project (not the name). This query counts the number of cards in the project.

    The result should be XML that looks something like

    <results>

    <result>20</result>

    </results>

    If you get this result then email me a screen shot of the Login dialog from Excel just prior to clicking the connect button. And, of course, make double sure you're not mistyping the password. Also attach the log to the email.

    0
    Comment actions Permalink
  • Avatar
    Brian Habermann

    I'm getting incorrect username or passoword. I'm using the same username and password that I use to log into the web version. This is running on windows server 2003.

    0
    Comment actions Permalink

Please sign in to leave a comment.