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.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
    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 function next! In the same macro, add the following lines:

    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
  3. Lastly, since we're setting referencing the Excel worksheets in our macros, we need to include the Excel library. In the macro edit view, click on Tools > References. A window should appear and enable the entry: Microsoft Excel X.Y Object Library. Click OK to save your changes.

    Reports 188

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 →

Streamline InfoSec Project Delivery

Learn practical tips to reduce the overhead that drags down security assessment delivery with this 5-day course. These proven, innovative, and straightforward techniques will optimize all areas of your next engagement including:

  • Scoping
  • Scheduling
  • Project Planning
  • Delivery
  • Intra-team Collaboration
  • Reporting and much more...

Your email is kept private. We don't do the spam thing.