Jira Integration - Need to create burndowns for Epics

Brook Waddell

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!

Genevieve P.

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?

Cheers,

Genevieve

Genevieve P.

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?

Cheers,

Genevieve

Brook Waddell

Thank you so much! That worked perfectly!

Genevieve P.

No problem! 🙂