Trusted Contributor.. mherbert_1 Trusted Contributor..
Trusted Contributor..
477 views

Need help with an MS Excel Report

Jump to solution

HI,

I need some help putting an MS Excel report together for the defects module.  I can't seem to get the query syntax correct.  Attached is a photo of what the desired report output should look like.  The yellow columns are columns that would be added to the report based on what is pulled from the audit table (as wel as colums for calculating the number of days that the "Discovery Phase" value was set to a specific value before being changed).  Below is an example of the code that I have in place so far.  It isn't much. Just barely a start. Any guidance is appreciated, especailly as it pertains to pulling from the History table and doing the calculations.  

Best regards,
Mark

SELECT
BUG.BG_BUG_ID as 'QC Number',
BUG.BG_DETECTION_DATE as 'Entered Date',
BUG.BG_USER_28 as 'PV #',
BUG.BG_SUMMARY as 'Project Name',
BUG.BG_STATUS as 'Discovery Phase',
BUG.BG_USER_05 as 'Discovery BA',
BUG.BG_USER_26 as 'Assigned Service Line(s)',
BUG.BG_USER_34 as 'Additional SLO(s)'
FROM td.BUG
*left join
(select al.AU_ENTITY_ID, max( AU_TIME ) as READYTIMESTAMP
from td.AUDIT_LOG al join td.AUDIT_PROPERTIES ap on al.AU_ACTION_ID = ap.AP_ACTION_ID
where al.AU_ENTITY_TYPE = 'BUG'
and ap.AP_FIELD_NAME = 'BG_STATUS'
and ap.AP_NEW_VALUE = 'Discovery'
group by al.au_entity_id) aud on aud.AU_ENTITY_ID = bg.BG_BUG_ID
ORDER BY BUG.BG_BUG_ID


Excel_Report_Example.JPG

Mark Herbert
Software Quality Manager / Engineer
Micro Focus Accredited Solutions Expert (ASE)
Micro Focus Certified Instructor (CI)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Knowledge Partner
Knowledge Partner

Re: Need help with an MS Excel Report

Jump to solution

Hi Mherbert,

Modified query as below.

select BUG.BG_BUG_ID as 'QC Number',
BUG.BG_DETECTION_DATE as 'Entered Date',
-- BUG.BG_USER_28 as 'PV #',
BUG.BG_SUMMARY as 'Project Name',
BUG.BG_STATUS as 'Discovery Phase',
BUG.BG_USER_05 as 'Discovery BA',
-- BUG.BG_USER_26 as 'Assigned Service Line(s)',
-- BUG.BG_USER_34 as 'Additional SLO(s)',
al.AU_ENTITY_ID, AU_TIME as READYTIMESTAMP,ap.AP_FIELD_NAME,ap.AP_OLD_VALUE ,ap.AP_NEW_VALUE
from td.AUDIT_LOG al join td.AUDIT_PROPERTIES ap on al.AU_ACTION_ID = ap.AP_ACTION_ID
right outer join bug on al.AU_Entity_ID = bg_bug_id
where al.AU_ENTITY_TYPE = 'BUG'
and ap.AP_FIELD_NAME = 'BG_STATUS'
order by BUG.BG_BUG_ID
-- and ap.AP_NEW_VALUE = 'Discovery'
-- group by al.au_entity_id

I believe, what you want to accomplish can be acheived by using "Post Processing" functionality in Excel Query reports.

Once we have the output of above query in excel "Sheet1" .

We can further write logic in "Post Processing" section in such a way that, if there are multiple records with Same Defect ID in Sheet1 then denormalize those records into a single Defect ID record in Sheet2.

This Denormalized defect ID's record should have the all the changes recorded at end of the record.

for ex:

Say below is the output in 'Sheet1"

duplicate records.jpg

In Sheet2 it should be

81 change1timestamp "" "New" change2timestamp "New" "Rejected" etc

Regards,Srihari

3 Replies
Highlighted
Knowledge Partner
Knowledge Partner

Re: Need help with an MS Excel Report

Jump to solution

Hi Mherbert,

Modified query as below.

select BUG.BG_BUG_ID as 'QC Number',
BUG.BG_DETECTION_DATE as 'Entered Date',
-- BUG.BG_USER_28 as 'PV #',
BUG.BG_SUMMARY as 'Project Name',
BUG.BG_STATUS as 'Discovery Phase',
BUG.BG_USER_05 as 'Discovery BA',
-- BUG.BG_USER_26 as 'Assigned Service Line(s)',
-- BUG.BG_USER_34 as 'Additional SLO(s)',
al.AU_ENTITY_ID, AU_TIME as READYTIMESTAMP,ap.AP_FIELD_NAME,ap.AP_OLD_VALUE ,ap.AP_NEW_VALUE
from td.AUDIT_LOG al join td.AUDIT_PROPERTIES ap on al.AU_ACTION_ID = ap.AP_ACTION_ID
right outer join bug on al.AU_Entity_ID = bg_bug_id
where al.AU_ENTITY_TYPE = 'BUG'
and ap.AP_FIELD_NAME = 'BG_STATUS'
order by BUG.BG_BUG_ID
-- and ap.AP_NEW_VALUE = 'Discovery'
-- group by al.au_entity_id

I believe, what you want to accomplish can be acheived by using "Post Processing" functionality in Excel Query reports.

Once we have the output of above query in excel "Sheet1" .

We can further write logic in "Post Processing" section in such a way that, if there are multiple records with Same Defect ID in Sheet1 then denormalize those records into a single Defect ID record in Sheet2.

This Denormalized defect ID's record should have the all the changes recorded at end of the record.

for ex:

Say below is the output in 'Sheet1"

duplicate records.jpg

In Sheet2 it should be

81 change1timestamp "" "New" change2timestamp "New" "Rejected" etc

Regards,Srihari

Trusted Contributor.. mherbert_1 Trusted Contributor..
Trusted Contributor..

Re: Need help with an MS Excel Report

Jump to solution

Thanks so much!  The query works perfectly.  Thanks a lot for the help with that piece!  

I follow what you are saying about trying to use Post Processing, however I am not quite sure how to make that work.  Can you ellaborate further on what you mean by that, and maybe extend the code example that you give?

Best regards,

Mark

Mark Herbert
Software Quality Manager / Engineer
Micro Focus Accredited Solutions Expert (ASE)
Micro Focus Certified Instructor (CI)
0 Likes
Knowledge Partner
Knowledge Partner

Re: Need help with an MS Excel Report

Jump to solution

Hi Mark,

I tried something like below, this code needs to be further enhanced and tested properly.

Private Sub Form_Load()

    Dim Excel_Row_Number, LastRow, LastCol As Integer
    Dim Defect_ID, Next_Defect_ID, Previous_Value As Integer
    Dim Position1, Position2, Hook_ID, Found_Count As Integer
    Dim Previous_Defect_ID As Integer
    
    Excel_Row_Number = 1
    Hook_ID = 0
    
    LastRow = ThisWorkbook.Sheets("Query1").UsedRange.rows.Count
    LastCol = ThisWorkbook.Sheets("Query1").UsedRange.Columns.Count

On Error Resume Next
    Previous_Defect_ID = 0
    For m = 2 To LastRow
        
        If Trim(Sheets("Query1").Range("A" & m)) <> "" And CInt(Sheets("Query1").Range("A" & m)) <> Previous_Defect_ID Then
           Found_Count = 1
           Hook_ID = CInt(Sheets("Query1").Range("A" & m))
            For n = m + 1 To LastRow
                If CInt(Sheets("Query1").Range("A" & n)) = Hook_ID Then
                    Found_Count = Found_Count + 1
                End If
            Next
                Debug.Print "Defect ID:" & Hook_ID & "  found: " & Found_Count
                
                If Found_Count = 1 Then
                    Sheets("Sheet1").Range("A" & m) = Sheets("Query1").Range("A" & m)
                    Sheets("Sheet1").Range("B" & m) = Sheets("Query1").Range("B" & m)
                    Sheets("Sheet1").Range("C" & m) = Sheets("Query1").Range("C" & m)
                    Sheets("Sheet1").Range("D" & m) = Sheets("Query1").Range("D" & m)
                    Sheets("Sheet1").Range("E" & m) = Sheets("Query1").Range("E" & m)
                ElseIf Found_Count = 2 Then
                    Sheets("Sheet1").Range("A" & m) = Sheets("Query1").Range("A" & m)
                    Sheets("Sheet1").Range("B" & m) = Sheets("Query1").Range("B" & m)
                    Sheets("Sheet1").Range("C" & m) = Sheets("Query1").Range("C" & m)
                    Sheets("Sheet1").Range("D" & m) = Sheets("Query1").Range("D" & m)
                    Sheets("Sheet1").Range("E" & m) = Sheets("Query1").Range("E" & m)
                    Sheets("Sheet1").Range("F" & m) = Sheets("Query1").Range("B" & m + 1)
                    Sheets("Sheet1").Range("G" & m) = Sheets("Query1").Range("D" & m + 1)
                    Sheets("Sheet1").Range("H" & m) = Sheets("Query1").Range("E" & m + 1)
                ElseIf Found_Count = 3 Then
                    Sheets("Sheet1").Range("A" & m) = Sheets("Query1").Range("A" & m)
                    Sheets("Sheet1").Range("B" & m) = Sheets("Query1").Range("B" & m)
                    Sheets("Sheet1").Range("C" & m) = Sheets("Query1").Range("C" & m)
                    Sheets("Sheet1").Range("D" & m) = Sheets("Query1").Range("D" & m)
                    Sheets("Sheet1").Range("E" & m) = Sheets("Query1").Range("E" & m)
                    Sheets("Sheet1").Range("F" & m) = Sheets("Query1").Range("B" & m + 1)
                    Sheets("Sheet1").Range("G" & m) = Sheets("Query1").Range("D" & m + 1)
                    Sheets("Sheet1").Range("H" & m) = Sheets("Query1").Range("E" & m + 1)
                    Sheets("Sheet1").Range("I" & m) = Sheets("Query1").Range("B" & m + 2)
                    Sheets("Sheet1").Range("J" & m) = Sheets("Query1").Range("D" & m + 2)
                    Sheets("Sheet1").Range("K" & m) = Sheets("Query1").Range("E" & m + 2)
                End If
        End If
        Previous_Defect_ID = Hook_ID
    Next
   On Error Resume Next
    ActiveWorkbook.Worksheets("Sheet1").Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   On Error GoTo 0
End Sub

Input:

Input.jpg

Output:

Output1.jpg

 Hope this helps as a starting point.

Regards, Srihari

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.