Format Excel sheets export by JIRA-(JIRA custom reports)

Question

I need to generate custom report in jira with transition time tracking details. this jira plugin can generate the time tracking details. but I need to add these time tracking details to the jira default all field export report.without copy paste the values I need to find a way to merge these data.

jira default all field report enter image description here

report can be generate by plugin I mentioned above (Sheet A) enter image description here

what I needed data from above report (Sheet B)

enter image description here

then I need to merge this table into the jira default all field export report

what I have found

1.write macro code

2.converting these data into sql then use sql queries.

is there any other ways to do this? or should I select a above option? or what would be the easiest way to achieve this?.

There is a way. You have to join `jiraaction` to `jiraissue`, then use pivot functions. — Koshinae, May 16 '15 at 18:34
I didn't get you. its really helpful if u can explain little bit more.. — Nuwan, May 17 '15 at 01:40

score 1 · Answer 1 · answered May 18 '15 at 08:27

Rotating rows to columns, or adding columns dynamically is called pivoting, and is not part of the SQL standard and usually involves consistent headaches :-)
As I use oracle behind jira, I can only give you an oracle-specific answer:

SELECT * FROM (
SELECT
    JI.ID AS ISSUEID,
    P.PNAME || '-' || JI.ISSUENUM AS PKEY,
    IST.PNAME AS ISSUESTATUS,
    CG.CREATED AS CHANGED,
    TO_CHAR(CI.NEWSTRING) AS NEWSTATUS
FROM JIRAISSUE JI
INNER JOIN ISSUESTATUS IST ON JI.ISSUESTATUS = IST.ID
INNER JOIN PROJECT P ON JI.PROJECT = P.ID
LEFT JOIN CHANGEGROUP CG ON CG.ISSUEID = JI.ID
INNER JOIN CHANGEITEM CI ON CI.GROUPID = CG.ID AND CI.FIELDTYPE = 'jira' AND CI.FIELD = 'status'
-- WHERE JI.ID = 278217
) PIVOT (MIN(CHANGED) FOR NEWSTATUS IN('Find Solution', 'Development Done'))

The only problem is, that the "dynamic column" part (IN clause inside the PIVOT) has to be known. Except if you use the XML mode...

Nuwan · Accepted Answer · 2015-05-23T08:13:27.290

After searching may ways to get an answer for the question I have found a way to solve this using macros.

Step 1: I have created a new empty work sheet called "Sheet B" in the same Excel work book generated by the plugin.

Step 2: write a macro code for Sheet A for generate the data into Sheet B`

Function FindColumn(StartColumn As Integer, EndColumn As Integer, WantedString As String) As Integer

Dim LastColumn As Integer
Dim ReturnValue As Integer

i = 0
ReturnValue = 0

    LastColumn = EndColumn - StartColumn
    Do While i <= LastColumn

    If (Worksheets("Sheet A").Cells(1, i + StartColumn) = WantedString) Then
        ReturnValue = i + StartColumn
        Exit Do
    End If
    i = i + 1
    Loop

    FindColumn = ReturnValue


End Function

Sub NewTime()

SetAll1to1ColumnsTime

End Sub

Sub SetAll1to1ColumnsTime()
Dim CurrentLine As Integer
Dim NROfColumns As Integer
Dim NROfLines As Integer
Dim Str As String
Dim Str2 As String
Dim Str3 As String


    NROfColumns = Worksheets("Sheet A").UsedRange.Columns.Count

    NROfLines = Worksheets("Sheet A").UsedRange.Rows.Count


    CurrentLine = 2

    Do While CurrentLine <= NROfLines


        Column = FindColumn(1, NROfColumns, "Issue Key")
        Worksheets("Sheet B").Cells(CurrentLine, 1).Value = Worksheets("Sheet A").Cells(CurrentLine, Column).Value

        Column = FindColumn(1, NROfColumns, "Status")
        Worksheets("Sheet B").Cells(CurrentLine, 2).Value = Worksheets("Sheet A").Cells(CurrentLine, Column).Value

        Column = FindColumn(1, NROfColumns, "Status")
        Str = Worksheets("Sheet A").Cells(CurrentLine, Column).Value

            If Str = "Development Done" Then

                Str2 = Worksheets("Sheet A").Cells(CurrentLine, 4).Value
                Worksheets("Sheet B").Cells(CurrentLine, 4).Value = Str2

            End If

            If Str = "Find Solution" Then

                Str3 = Worksheets("Sheet A").Cells(CurrentLine, 3).Value
                Worksheets("Sheet B").Cells(CurrentLine, 3).Value = Str3

            End If


        CurrentLine = CurrentLine + 1
    Loop


    End Sub

Step 3:Then I write a macro code for the generated Sheet B for format the data as I needed.
Generated Sheet B using macro code enter image description here

this is the macro code for Sheet B

Sub mergeCategoryValues()
Dim lngRow As Long

With ActiveSheet
    lngRow = .Cells(65536, 1).End(xlUp).Row
    .Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes

    Do
        If .Cells(lngRow, 1) = .Cells(lngRow - 1, 1) Then
            .Cells(lngRow - 1, 4) = .Cells(lngRow - 1, 4) & "" & .Cells(lngRow, 4)
            .Rows(lngRow).Delete
        End If

        lngRow = lngRow - 1
    Loop Until lngRow = 1
End With
End Sub

work Done! Note:Do not mind about the column B

enter image description here

Format Excel sheets export by JIRA-(JIRA custom reports)

2 Answers2