Custom charts and graphs

This guide contains:

Create the chart

Start out with the finished product! Add a placeholder chart to your report template.

  1. In Word, navigate to the Insert tab, then click Chart and select the type of chart (bar, column, pie, etc) that you want to add to your reports:

    Reports 166
  2. Update the chart so that it looks the way you want! Work on the colors, borders, layout, legend, sizes and more. Don't forget about the data either! Make sure to open up the Excel sheet (right click and select Edit Data > Edit data in Excel) and edit the data with some placeholder labels and data. Make sure to update Cell A1 with a single word (e.g. Rating), we'll be using this later!

    Reports 167

Count the Issues

  1. Now that you have your placeholder chart in your report template, we have to make sure we can update that chart with the project-specific data!

    The easiest way to do this is usually with IssueCounter controls in a placeholder table that has the same layout as the Excel sheet hidden behind the chart.

    In the example above, we have an Excel sheet with 2 columns and 5 rows so we'll create the same size placeholder table:

    Reports 168
  2. Finally, make sure that this placeholder table contains a bookmark so that we can reference this placeholder table later with our macro.

    In Word, navigate to the Insert tab and click Links > Bookmarks and give it the name tblData:

    Reports 169

Update the chart with a custom macro

Note, this macro will not work if you are running Word for Mac. Read more about the VBA limitations of Word for Mac in our FAQs.

  1. Add the following macro to your report template:

    Sub UpdateChart()
        Dim dataTable As Table
        
        Dim objShape As InlineShape
        Dim salesChart As Chart
        Dim chartWorkSheet As Excel.worksheet
        
        ' Iterates each inline shape in the active document.
        For Each objShape In ActiveDocument.InlineShapes
            ' If the inline shape contains a chart, display the
            ' data and minimize the application
            If objShape.HasChart Then
     
                Set salesChart = objShape.Chart
    
                salesChart.ChartData.Activate
                
                salesChart.ChartData.Workbook.Application.WindowState = -4140
            
                Set chartWorkSheet = salesChart.ChartData.Workbook.WorkSheets(1)
                
                If chartWorkSheet.Range("A1").FormulaR1C1 = "Rating" Then
                    Call UpdateByRatingChart(chartWorkSheet)
                End If
            End If
        Next
    End Sub
            

    Make sure to update this line: If chartWorkSheet.Range("A1").FormulaR1C1 = "Rating" Then as needed so that the macro is looking for the actual value you used cell A1 when you created the chart.

    More than one chart in your report?

    Use ElseIf to differentiate between charts:

    If chartWorkSheet.Range("A1").FormulaR1C1 = "Rating" Then
        Call UpdateRatingChart(chartWorkSheet)
    ElseIf chartWorkSheet.Range("A1").FormulaR1C1 = "Cat" Then
        Call UpdateCategoryChart(chartWorkSheet)
    End If
              
  2. Now, you see how we're calling UpdateByRatingChart in our previous macro? It's time to define that macro next!

    Private Sub UpdateByRatingChart(worksheet As Excel.worksheet)
    
        Dim tblData As Table
        Dim intR As Integer
    
        Set tblData = ActiveDocument.GoTo(What:=wdGoToBookmark, Name:="tblData").Tables(1)
        For intR = 2 to 5
          worksheet.Range("B" & intR).FormulaR1C1 = CleanCellText(tblData.Cell(intR, 2))
        Next intR
        
        ActiveDocument.GoTo(What:=wdGoToBookmark, Name:="tblData").Tables(1).Delete
        
    End Sub
    
    Private Function CleanCellText(objCell As Cell)
        Dim strCellText As String
        
        strCellText = objCell.Range.Text
        CleanCellText = Trim(Left(strCellText, Len(strCellText) - 2))
    End Function
            

    Notice that we're using that tblData bookmark heavily here so that the macro knows which table to pull the chart data from. We're also including a separate function to clean out the cell text.

    Don't want to delete the placeholder table?

    Remove this line or comment it out with a '

    ActiveDocument.GoTo(What:=wdGoToBookmark, Name:="tblData").Tables(1).Delete

Once you have all of the pieces in place, export a sample report and test out your macro.

As always, the Dradis support team is here to help. Contact us if you're having trouble with a report template and we can help!

Next help article: Node content controls →