Posted on

Parcel Area Tables with Summation and Grouping

Parcel Area Tables with Summation and Grouping

parcel area table document preview

If you have ever tried to create an area table for a collection of parcel entities in AutoCAD Civil 3D, you most likely share the same frustration we had with the process when we first tried it – there is no automatic way to sum the areas listed in the table. In order to achieve this seemingly simple thing, you must go through a convoluted process such as exporting to Excel, running the summation there, and then inserting the spreadsheet back into the drawing. We’ve seen others that just fill the table out by hand. Neither solution is particularly good.

Thankfully, there is another option, and it is actually quite easy to accomplish. The solution is to generate the table as a ‘report’ using Visual Report Designer , which allows you to create the table template using a drag-and-drop process, and includes easy to use summation and grouping features.

parcel area table - editing the report template in Visual Report Designer

The report consists of a header, which contains the table title (which is bound to a ‘Title’ parameter…more on this later), a ‘Detail Report’ band which is data-bound to the parcel entities in the current drawing, a group header band which shows column headers and the common value used to form the group, and finally a ‘Report Footer’ band which will be used to hold the grand total for the table. We will make this table report the area in two different units so it will work for both imperial and metric projects. We will also make it so that the user can choose what field should be used to ‘group’ the entities by. Note that in this example, we are intentionally not including a group subtotal – this will be left as a subject for a future post. Also, while the following explanation includes a significant amount of detail, actually creating the report takes only a few minutes, and there are areas which can be simplified or excluded entirely if desired but are included in order to demonstrate features/capabilities.

First, the report parameters:

Visual Report Designer - Parameter Collection Editor

We have two parameters in this report: Title and GroupBy.

Title is just an arbitrary text value and is used to set the displayed title in the generated report document…there’s nothing particularly special about this.

GroupBy is a little different. This parameter will be used to select which parcel field should be used to group the parcels. We’ll implement this as a ‘static list of values, meaning that it is a pre-defined list of values that doesn’t change that the user is able to select from.

Visual Report Designer - Parameter Collection Editor - Look-up Values

Next, we’ll need to set up some calculated fields as the parcel fields will not work for our purposes without modification, and we’ll need to create special fields for implementing the grouping feature.

Visual Report Designer - Calculated Fields Editor

The two ‘area’ fields are similar in implementation. The only difference is in the conversion factors. The way these work is to first convert the area from ‘square drawing distance units’ (i.e. either ‘ft’ or ‘m’) to square meters to ‘normalize’ the data to a known unit. This is accomplished using the function _ToMetricDistanceConversionFactor(), which is called twice (i.e. the value is ‘squared’) to effectively get a ‘to metric area conversion factor’. Once the value is known to be in square meters, we can then multiply by the correct conversion factor to convert it to acres or hectares, or whatever else we desire.

Visual Report Designer - Expression Editor

The GroupByValue calculated field will be used to determine the value that will be used for grouping the parcels when generating the report. Because we are offering the user a choice we cannot just directly reference a specific field…instead we create an expression which checks the user’s choice, and conditionally retrieves the value of the field associated with that choice.

Visual Report Designer - Expression Editor

Note that if you only want to group by one particular field instead of allowing the user a choice, this (and the GroupByHeader calculated field can be omitted from the report, as can all of the other steps related to these items). Also, if ‘None’ is selected by the user, the default value (basically, an empty string/text value) in the last line of the expression will be returned.

The GroupByHeader calculated field is similar to the GroupByValue calculated field except that instead of extracting a value from an actual parcel field, we just return a text value with the name of the field:

Visual Report Designer - Expression Editor - nested conditional expression returning plain text

Now that we have all of our parameters and calculated fields set up, we can create the report template. To do that, we do the following:

  1. Drag and drop the Title parameter from the field list into the report header; adjust title label size, font, etc.
  2. Add a Detail Report band bound to the ‘Parcels’ entity collection to the default detail band. In here we add 3 fields to the child detail band (Detail 1) by dragging from the field list: Name, and the 2 ‘Area’ expressions we previously defined. We also adjust label size, alignment, and font as needed.
  3. The precision of the ‘area’ labels can be set using a ‘format string’ via the label task pane:
    Visual Report Designer - Argumented Label Tasks - Format String
  4. With the detail band Detail 1 selected, we add a group and a child sort operation. The group operation should key on the calculated field GroupByValue and the option to show the header should be enabled. The sort operation should use the parcel Name field – this will sort the entities within each group by Name. Both operations should use the Ascending sort order option.Visual Report Designer - Group and Sort - Parcels
  5. Next, we add the column headers to the GroupHeader band created in the previous step. The Name and ‘Area’ column headers are simply labels with static text and are created by inserting labels from the Tool Box; the two in the center which contain the group information are data-bound labels which show the values returned by the GroupByHeader and GroupByValue calculated fields. These last two are created by dragging the calculated fields from the field list and dropping into the appropriate location in the report template. Of course, font, alignment, and label size should be adjusted as needed for consistency with the rest of the report template.
  6. After setting up the main body of the table, we still must add the summation function. To do that we drag and drop the two ‘Area’ calculated fields into the report footer to create data-bound labels, and then align to the appropriate columns in the main table/report body. We then use the task pane to configure a ‘summation’ operation for these labels (see below). Once that is done, we can add the plain text label containing the header ‘TOTAL’, and can configure alignment, font, precision/numeric formatting etc.
    Visual Report Designer - Argumented Label Tasks - Summation
  7. Add horizontal and vertical lines and/or cross-band lines to complete the table formatting.

The end result looks like this:

Visual Report Designer - Report Template

You may notice that this report also includes an empty image control that we have yet to mention…you can easily add an image from the Tool Box and bind it to a file containing your company logo, etc if you like.  It can just as easily be omitted.

From here we can ‘preview’ the report and test the features we implemented. Be sure to save first.

When you click on the Print Preview tab, Visual Report Designer will initiate a report preview process. You will first be shown the Query Editor window where you may choose which entities to run the report on, and may modify the report parameters:

Visual Report Designer - Edit Queries - Root Queries Tab

Visual Report Designer - Edit Queries - Report Parameters Tab

For the example here, we’ll edit the GroupBy parameter value to have the report group the entities by the SiteName field, and we’ll leave the query and selection set settings alone so the report will automatically pick up ALL parcels in the current DWG. Once this is set, we click on the OK button to generate the report preview document:

parcel area table document preview

As previously mentioned, it is possible to do quite a bit more with this report. For example, we might want to add a group subtotal summation. Through scripting we can even make a conditional group subtotal summation that only appears if more than one parcel is in a group, but again, we’ll save this for a future post.

 

The web store has been temporarily placed in demo/test mode while we update and test some new features. If you see this message and wish to make a purchase please contact sales@civilplus.net. Thank you.