Jira Integration - Need to create burndowns for Epics
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:
- Is there a way for me to get the appropriate information out of Jira so I don't have to work some "Smartsheet Magic"?
- 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
-
"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?
Cheers,
Genevieve
Answers
-
"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?
Cheers,
Genevieve
-
Thank you so much! That worked perfectly!
-
No problem! 🙂
