Now that the data is sorted and grouped, we will add code to calculate subtotals. With the subtotals, the user will be able to see what products are selling more, in what regions, and which salespeople are doing a good job.

Adding subtotals to a VSFlexGrid control is easy. The Subtotal method handles most of the details.

The subtotals need to be recalculated after each sort, so we will add the necessary code to the AfterMoveColumn event. Here is the revised code:

    Private Sub fg_AfterMoveColumn(ByVal Col As Long, Position As Long)

   

        ' suspend repainting to get more speed

        fg.Redraw = False

   

        ' sort the data from first to last column

        fg.Select 1, 0, 1, fa.Cols – 1

        fg.Sort = flexSortGenericAscending

        fg.Select 1, 0

   

        ' calculate subtotals

        fg.Subtotal flexSTClear

        fg.Subtotal flexSTSum, -1, 3, , 1, vbWhite, True

        fg.Subtotal flexSTSum, 0, 3, , vbRed, vbWhite, True

        fg.Subtotal flexSTSum, 1, 3, , vbBlue, vbWhite, True

   

        ' autosize

        fg.AutoSize 0, fa.Cols - 1, , 300

   

        ' turn repainting back on

        fg.Redraw = True

   

    End Sub

This code starts by setting the Redraw property to False. This suspends all repainting while we work on the grid, which avoids flicker and increases speed.

Then the subtotals are calculated using the Subtotal method. The first call removes any existing subtotal rows, cleaning up the grid. The next three calls add subtotal rows. We start by adding a grand total, then subtotals on sales grouped by columns 0 and 1. (For now, we are assuming that sales figures will be on column 3.)

After adding the subtotals, we use the AutoSize method to make sure all columns are wide enough to display the new data.

Finally, the Redraw property is set back to True, at which point the changes become visible.

If you run the project now, you will see that it almost works. The problem is that we are assuming that sales figures will be on column 3, and if the user moves the figures to the left, the subtotals will just add up to zero.

To prevent this from happening, we can trap the BeforeMoveColumn event and prevent the user from moving the sales figure column.

Here is the code:

    Private Sub fg_BeforeMoveColumn(ByVal Col As Long, Position As Long)

   

        ' don't move sales figures

        If Col = fg.Cols - 1 Then Position = -1

    

    End Sub

We should also prevent the sales column from having merged cells. Merging these values could be confusing because identical amounts would be merged and appear to be a single entry. To do this, we need to go back to the Form_Load event handler and add one line of code:

    Private Sub Form_Load()

   

        ' initialize the control

        ' …

        ' define some sample data

        ' …

        ' populate the control with the data

        ' …

   

        ' set up cell merging (all columns)

        fa.MergeCells = flexMergeRestrictAll

        fa.MergeCol(-1) = True

        fa.MergeCol(fa.Cols - 1) = False

        ' organize the data

        ' …

    End Sub

We are done with the subtotals. If you run the project now, you will see how easy it is to understand the picture behind the sales figures. You can organize the data by product, by region, or by salesperson and quickly see who is selling what and where.

We are now almost done with this demo. The last step is to add outlining to the control, so users can hide or show details and get an even clearer picture.

Copyright (c) GrapeCity, inc. All rights reserved.