Defect Age report

SQL
SELECT
bg_bug_id as "Defect ID",
BG_PROJECT as "Project" /*Defect.Project*/,
BG_USER_21 as "Cycle" /*Defect.Testing Cycle*/,
cast(min(au_time)as date) as FirstLog,
cast(max(au_time) as date) as LastLog ,
trunc (max(au_time)- min(au_time)) as "Processing Time in Days",
bg_status as CurrentStatus
FROM
bug left outer join audit_log
on bg_bug_id = au_entity_id
left outer join audit_properties
on au_action_id = ap_action_id
where au_entity_type = 'BUG'
group by bg_bug_id, bg_Status,bg_project,bg_user_21
having bg_user_21 in ( 'QA','UAT') and ( cast(min(au_time)as date) between TO_DATE('01-10-2018','DD-MM-YYYY') and TO_DATE('31-12-2018','DD-MM-YYYY'))
order by bg_bug_id
post processing code

Sub QC_PostProcessing()
Sheets("Query1").Select
Range("A1").Select
Range("A1:G1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("I1").Select
CreatePivotTable
CreatePivotChartEmbedded
Format_Data
End Sub
Sub CreatePivotTable()

Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
Dim wsMovies As Worksheet

Set wsMovies = ThisWorkbook.Sheets("Query1")

Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=wsMovies.Name & "!" & wsMovies.Range("A1").CurrentRegion.Address, _
Version:=xlPivotTableVersion15)

wsMovies.Range("I1").Select

Set pt = pc.CreatePivotTable( _
TableDestination:=ActiveCell, _
TableName:="MoviePivot")

wsMovies.Range("I1").Select

With ActiveSheet.PivotTables("MoviePivot").PivotFields("Cycle")
.Orientation = xlPageField
.Position = 1
End With

With ActiveSheet.PivotTables("MoviePivot").PivotFields( _
"Processing Time in Days")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("MoviePivot").PivotFields( _
"Project")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("MoviePivot").AddDataField ActiveSheet.PivotTables( _
"MoviePivot").PivotFields("Defect ID"), "Sum of Defect ID", xlSum
With ActiveSheet.PivotTables("MoviePivot").PivotFields("Sum of Defect ID")
.Caption = "Count of Defect ID"
.Function = xlCount
End With

Range("I7").Select
Selection.Group Start:=True, End:=True, By:=11

With ActiveSheet.PivotTables("MoviePivot").PivotFields("CURRENTSTATUS")
.Orientation = xlColumnField
.Position = 1
End With

ActiveSheet.PivotTables("MoviePivot").PivotFields("Cycle").CurrentPage = _
"(All)"
ActiveSheet.PivotTables("MoviePivot").PivotFields("Cycle"). _
EnableMultiplePageItems = True
Range("I6").Select
ActiveSheet.PivotTables("MoviePivot").NullString = "0"

ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveSheet.PivotTables("MoviePivot")
.DisplayErrorString = True
.ErrorString = "0"
.NullString = "0"
End With

End Sub

Sub CreatePivotChartEmbedded()

Dim sh As Shape
Dim s As Series
Dim ws As Worksheet
Dim ch As Chart
Dim pt As PivotTable
Set ws = Worksheets("Query1")
ws.Activate
Set sh = ws.Shapes.AddChart2( _
XlChartType:=XlChartType.xlColumnStacked, _
Width:=2500, Height:=450)

Set ch = sh.Chart

Set pt = ws.PivotTables("MoviePivot")
ch.SetSourceData pt.TableRange2

sh.Top = pt.TableRange2.Top
sh.Left = pt.TableRange2.Left + pt.TableRange2.Width + 10

With ch
.ChartType = xlColumnClustered
.HasLegend = True
.HasTitle = True
.ChartTitle.Text = "Project Wise Defect Aging Report for QA Cycle"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Project Name"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Defect Count Age Wise"

For Each s In .SeriesCollection
s.ApplyDataLabels

Next s

ch.SetElement (msoElementDataTableWithLegendKeys)

ch.FullSeriesCollection(1).DataLabels.Select
'Selection.Orientation = xlUpward
'Selection.Format.TextFrame2.Orientation = msoTextOrientationUpward

End With

End Sub

Sub Format_Data()
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

Selection.EntireColumn.AutoFit
Range("A1:G1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
End Sub