We are often exporting volumes from our models and for a lot of designers and drafters it is an adhoc affair. Engineer asks for volumes, so you make a few volume surfaces quickly, export volumes into excel, format a bit and then send. Some of the slightly more organized types might even save the excel spreadsheet into their modelling folder in case they need to use it again! But more often than not you will be asked to do this several times over the life of the project. So why not come up with a decent system for handling this process?

 

I would like to present a more efficient method, that not only is easy to update and manage but will improve the performance of your modelling files as well.

The workflow is quite simple, it may take slightly longer to setup. But I promise, you will thank yourself later. They key element been the use of the volumes dashboard.

A few simple rules to follow to ensure you maintain a nice link.

1. Never create all your volumes in the modelling DWG. Create them all in a separate DWG using data shortcuted surfaces (The odd one is fine).

2. Don’t manipulate the raw data you copy from the volumes dashboard (other than a sort). Link to it from another table in excel.

3. Don’t “Cut data over”/ Copy Data over the top in excel. Otherwise your links break!

Volume surfaces quickly increase the size of modelling dwg’s and will decrease overall performance when working in them. Unless you don’t work in your modelling DWG’s very often, which I highly doubt, keep your volumes surface in a separate dwg and data shortcut all the surfaces you need in. This also makes it easier to extract all the volumes you need in one hit rather then prowling around your dwg’s looking for quantities.

Once you have created your volumes DWG and you have data shortcutted the surfaces you need in. Start creating Volumes Surface Pairs. I strongly suggest maintaining a simple but consistent naming system. My system is as follows

<identifiers>-<object>_<material_name>

So for example:

OP2-NTH-ROAD_gravel

OP2-NTH-ROAD_base

OP2-NTH-ROAD_ subbase etc..

This makes your life a lot easier when creating links in excel. It also helps prevent the desire to shoot yourself due to confusion later on :p.

Now you have pairs, it’s time to setup an excel file. My pairs looks like so:

Right Click on any surface in the dashboard to “Copy to Clipboard”

Open Excel, an begin by creating two tabs “Volumes”,”Data”. Data is where we will copy the raw data from your volumes dashboard (copy your raw output now). Volumes is where we will create the links to the volumes dashboard data so it is easier to update later.

Your data is now on the data tab. Do a sort on it (Volumes Dashboard doesn’t have a sort function!)

Your data should sort by surface name! This will save headaches later when you want to update the table or add new surfaces.

Now switch over to the Volumes tab and start creating a table like the one below. With various identifiers you need for each material volume you want to calculate. In the last column (2nd last in my case) you want to link the QTY back to the “data” tab that contains your raw pasted volumes dashboard data.

Now this table you create will eventually contain all the volumes you want, but not exactly in the best format for visualizing. This last step can be quite confusing for a lot of people. So hopefully the video link at the top helps.

Because the table is arranged in a “basic record format” like Microsoft Access or any SQL etc. we can use this table to create “Pivot Tables” and “Pivot Charts”.

Firstly covert your table into a “Named Range” by using the format as table function

Now select anywhere in your nicely formatted table and go to Insert -> Pivottable.

You will be confronted with a blank screen and most likely see all your column headers on the right.

By drag-droping your tables headers into the fields as required you can make your table display exactly as you need it. (I highly suggest watching the video to see this in action)

But for example say I wanted a table to display the materials as the column headers, the time as rows and calculate the quantities with respect to these columns. I could do the following

Then you will get this

But you can adjust these to suit your needs. Better, you can adjust to suit other people’s needs. You can create as many pivot tables as you want to display different views of your data and they are all stay linked back to the original source..

Now when it comes time to update your volumes from Civil 3D all you have to do is copy your volumes dashboard output… sort and paste over the top in your data tab!

The end result wonderfully formatted, dynamic tables. That can update almost at the click of a button…

Component	Time-Height	Embankment	Storage	Decant Rock	Floor Cut	Floor Fill	Wall Excavate	Windrows	Minewaste	Dry Tailings
3 – NORTH	Month 00 – 487.82 m – DOWN	16,600	500	5,900	5,200	6,100	500
Month 06 – 487.82 m – DOWN	39,900	500
Month 06 – 489.92 m – DOWN	27,700	500
Month 12 – 493.02 m – DOWN	116,500	60,800	1,000	600
Month 21 – 493.02 m – DOWN	101,500
3 – NORTH Total	160,800	102,200	2,000	5,900	5,200	6,100	1,600
4 – SOUTHEAST	Month 06 – 489 m – DOWN	125,100	600
Month 18 – 489 m – DOWN	325,100
4 – SOUTHEAST Total	125,100	325,100	600
5 – MINEWASTE	Month 18 – 497.7 m – DOWN	545,600
Month 24 – 497.7 m – STACK	405,900
Month 30 – 497.7 m – STACK	600,800
Month 36 – 497.7 m – STACK	390,700
5 – MINEWASTE Total	1,943,000
6 – DRY STACK	Month 12 – 485.2 m – STACK	190,600
Month 18 – 485.2 m – STACK	45,100
Month 24 – 487 m – STACK	47,200
Month 30 – 489 m – STACK	46,000
Month 36 – 491.8 m – STACK	52,300
6 – DRY STACK Total	190,600	190,600
Grand Total	285,900	527,300	2,000	196,500	5,200	6,100	2,200	1,943,000	190,600