Tag Archives: volumes surfaces

Managing Surface Volumes in Civil 3D – Excel and the Volumes Dashboard

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.

image002

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

Loading