Jira Integration - Need to create burndowns for Epics

Brook Waddell
Brook Waddell ✭✭✭
edited 06/14/22 in Add Ons and Integrations

I am new to Smartsheet and even newer to the integration tool we just purchased (This week). I am, however, relatively proficient with Excel and have been using it to create burndowns for years.

As I attempted to duplicate my Excel efforts within Smartsheet, I noticed that some of the Jira fields I use are no longer available (Such as the ability to obtain the aggregate of time estimated, spent or remaining at the story level). In an attempt to obtain this information I used the following JQL query:

parentEpic in (OF-240, OF-243, OF-244, OF-245) AND issuetype in (story, bug, Sub-task)

In these types of situations I would expect to have the ability to connect sub-task to stories, but the field (ParentLink?) that connects the sub-tasks to the stories and bugs doesn't appear to be available in the Connector. Just returning the values, in a flat file doesn't give me the hierarchy I need. If I choose the option to "Maintain JIRA hierarchy grouping", however, I get to see the connection via an indented result. Nice, but if I'm gonna' try to create these trend graphs, I need to detect the hierarchy.

Two questions:

  1. Is there a way for me to get the appropriate information out of Jira so I don't have to work some "Smartsheet Magic"?
  2. If I have to work the magic, what do I need to do to identify that a sub-task (i.e. OF-254 or OF-261) is a child of a story (i.e. OF-239 or OF-255)?

Any help would be greatly appreciated.

Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Brook Waddell

    "Maintain JIRA hierarchy grouping" is the option I would choose here, then yes, create a little bit of formula magic to identify the hierarchy!

    To do this, create a helper column in your sheet which will identify the Parent. Then simply add in this formula:

    =PARENT([Issue Key]@row)

    Make this a column formula. Then this will bring in the Parent's Issue Key into the Child Row. If there is no parent, (ex. if the row is the parent, OF-239) then the cell will be blank.

    You can use this formula to bring in any of the Parent information - you would just change out the column reference to pull other data.

    Will this work for you?