Jump to content
Sign in to follow this  
Wyle C

Spread Sheet Reports

Recommended Posts

I am attempting to set up separate reports for each layer to calculate the walls(i,e concrete footings, then walls, flat work, etc.) total Area in Square feet then Cubic Yards of concrete Material Cost per Square Foot and Total Labor Cost per Square Foot. Can anyone direct me to specific instructions on how to accomplish this task? Thanks!

Share this post


Link to post

This will all depend on how the drawing is put togheter. If you used spaces for the square feet of rooms, then you can get all spaces in a spreadsheet etc...

Maybe you can post your file?

Share this post


Link to post

I would urge you to consider this as just an approximation,

not a substitute for a thorough materials take-off.

Below I go through setting up a worksheet for walls, but

not foundations or anything else in your list.

You should also be spending some time reading the

Help files on this, and getting familiar with worksheet

formulas and criteria.

Open a new worksheet

Turn on the Database Headers...

Right-click on the Database Row,

picture4hs5.png

Select Edit Criteria to "Type Is Wall" and "Layer is Your Design Layer #1"

picture7ad3.png

Using the down-arrow you can select "Paste Function" where it has some

of the wall functions listed.

picture8op7.png

I'm picking Wall Area Gross here.

picture9js3.png

Now Edit the cell format, change the Number style to a Dimension Area

picture10fw9.png

Keep fiddling until you get what you want.

Note in this case that the header row gives you the sum for the Gross Wall

Area, but that the other items in that row may not be relevant for your needs.

I wrote the formulas I used in each column for clarity. There are more than just

the ones I listed here though.

picture12op1.png

Share this post


Link to post

Thanks 18,

I set the report as you instructed and I do get an area report. I opened a "practice project" to practice various aspects of VWArchitech. For the report I already had a concrete footing wall created for the concrete foundation wall.

The wall design is 1'6"(horizontal dimension) wide and 8" thick or high(Vertical dimension). For the purpose of this exercise I made the length 48'(horizontal dimension). A standard residential footing. The report gave me an area of 32.000. This is incorrect. Length X Width = Square Feet which in this case is 72. I know what the report is doing. It is calculating 48' X .666(8" or 2/3rds of a foot) which is 31.968, which now makes me think I should maybe look for "Floor Area" formulas instead of wall area. If I change the height or thickness to 8' I get 384 area which is correct. If I click volumetric properties on the same footing I get 210 Square Feet and 48 Cubic Feet. where it gets that I don't know!

I have been doing construction for quite some time and I am always willing to learn and I thought it would be great to draw blueprints and have reports that fit my needs automatically created. I have VW DVD tutorials, manuals, and even Microsoft Excel manuals and I have been researching this for three days. I just have not found the specific info I need yet. I'll get there!

18 Thanks Again. I really appreciate it!

Wyle!

Share this post


Link to post

DWorks,

Thanks for your response, I drew a simple practice project that is a concrete footing. 4 sides and one center footing. It is 48 feet x 24 feet. In essence I have 3 48 foot and 1 45(center) foot lengths by 1'6"(wide). This all comes to 283.5 square feet. VWs considers this a wall that is 8" "thick" or "wide". This is a standard residential footing that I created in the "wall style" section of VWs. It is a wall that is laying on its side. VWs computes the square feet by the length x the "height" which is 8" or .667 or 2/3rds of a foot not the "thickness"(1'6") which will work in calculating the cubic yards of concrete needed to complete the footing. I have always calculated the concrete by the Length x the width divided by 12 x the "thickness" divided by 27( the cubic feet in 1 yard of concrete). In this case it is 189 x 1.5 =283.5(sq ft)divided by 12 times 8(thickness) divided by 27 = 7 Cu Yds concrete.

The Point is....

A report that auto-generates:

1. Calculates Sq feet or surface area in Sq ft of a concrete footing or wall.

2. Calculates Cu yds concrete needed

3.Calculates cost of material( concrete is sold by the cubic yard)

4.Calculates cost of labor(charged by the sq ft)

5 Totals of each.

I have VWs 2009, DVD tutorial, the manual and MS Excel manual.

VWs DVD only shows what a report is capable of by managing plants and shrubs. I am not a landscaper. I have not found the specific information I need to formulate the custom report that fits my needs. So the search begins. As I have done before, one just keeps looking until you find what you need. I might look for someone who is proficient in Excel and then import a template.

DWorks, again, thanks for your help! I appreciate it!

Wyle

Share this post


Link to post

I doubt an Excel expert will help you much. The hard part here is figuring out how to use the VW specific functions and terms to return the data that you want.

Try this:

Make a new Worksheet and make row 4 a database and set the criteria to Kind is Walls. You probably want to add additional criteria to specify the layer also, but not critical in a simple single layer test file.

In cell A1 type = $/Sq. FT.

In cell B1 type = 10

In cell A2 type $/Cu. Yd.

In cell B2 type 100

In cell C1 type Wall Area

In cell C2 type Labor $

In cell C3 type Wall Volume

In cell c4 type Concrete $

These are your labor and concrete rates. I used 10 and 100 as it is easy to see what they are doing. We will set the formulas so you can change the values in column B and have the calcs change.

In the database header row put in:

Column A =2*WallGrossArea

Column B =A4*$B$1

Column C =CriteriaVolume/27

Column D =C4*$B$2

The database header row will give you the total for each column. The individual rows will give the value for that section.

I think you made a mistake in your manual calcs. The "side" walls are not 24' long, but 21' long. This make the volume somewhat less than 7 cu. yds.

Another thing to think about is how and if you join walls. If you let the walls join, the calculated length of the walls will be the average length (48' on the outside, 45' on the inside giving 46.5' average). This will make it harder to see the exact numbers for each segment. If you don't join the walls, then you can control the exact length of each segment. The total will be the same in either case. For estimating purposes, either is probably acceptable.

One more point. You talk about a wall "laying on tis side", VW walls can not do that. the wall height will always be up from the ground plane. The length will always be along the ground plane. I know what you mean, but it doesn't help much to talk about walls in VW that way.

Pat

Share this post


Link to post

Getting there!

I entered the values and I did get concrete materials needed and price. Less tax of course! It did not show labor cost, I check to make sure I typed cells correctly. I also checked it against the walls. For example one wall(approximately 48'Lx4'H x8"W) had 189.333 sq ft which comes to 4.67 Cu yds concrete. You are right about my calculations. I always calculate the full dimensions to compensate for spillage, distance from plant, etc. and I always order from 1/4 - 1 yard extra since concrete comes by the 1/4 yard.

I copied and pasted what the report looked like after entering the information! I changed the 10 to 20 to see what would happen. I change the 100 to 200 and all the values in the D column doubled. I tried adding the = sign and various other things and columns A & B stayed the same. Just #VALUE in column A and #CVAL? in column B. Maybe you can see if I entered anything incorrectly?

Thanks Pat..Really!

Wyle

=$/Sq. FT. 20.00 WallArea

$/Cu._Yd. 100.00 Labor_$

Wall_Volume

0.00 0.00 20.89 2089.09

#VALUE! #CVAL? 4.67 467.49

#VALUE! #CVAL? 2.30 230.45

#VALUE! #CVAL? 4.67 467.49

#VALUE! #CVAL? 2.30 230.45

#VALUE! #CVAL? 1.75 175.31

#VALUE! #CVAL? 0.86 86.42

#VALUE! #CVAL? 1.75 175.31

#VALUE! #CVAL? 0.86 86.42

#VALUE! #CVAL? 1.70 169.75

Share this post


Link to post

I gave you the wrong formula for Column a of the database. Try changing it to:

=2* WALLAREA_GROSS

You could make the formulas as complex as you want to automatically round up to the nearest yard or quarter yard and to include a factor for spillage, etc. It just depands on how accurate you want to be.

Pat

Share this post


Link to post

I made the length 48'...

The wall design is 1'6"(horizontal dimension) wide and 8" thick or high(Vertical dimension). (1.5'x 2/3' is 1 sq. ft., so 1 cubic ft/lin. ft.)

(snip)

If I click volumetric properties on the same footing I get ... 48 Cubic Feet.

Looks right to me...

Share this post


Link to post

Pat, That worked! I am assuming I can add those formulas to the wall worksheet that was generated from the drawing? Is it possible to have a separate worksheet for each layer? I drew the footings on one layer and the walls on one layer, but the wall report is being generated from both layers.

Thanks again Pat..really! I just need to tweak it now.

Terry

Share this post


Link to post

Yes Chad,

That is true. one can figure this several different ways

1. 48'x1.5 / 12 x 8"(thick) /27=1.77 Cubic yards

2. 1.5' x8"(.667)/12x576(inches in 48')/27=1.77 Cubic yards.

Of course in the second formula there is one more step due to one having to calculate ho many inches there are in 48 feet(576)

3. Length x the width divided by 81=(4inch concrete)54=6inch 40=8 inch or 27= 12inch or 1foot. I have been using the first formula for years and I am just use to calculating that way.

I am just use to looking at a footing and seeing length and width and knowing the "thickness" I calculate it that way. When I look at a wall, a basement wall for instance, I see length and height and knowing the thickness and calculate it from that point of view!

Share this post


Link to post
Yes Chad,

That is true. one can figure this several different ways

1. 48'x1.5 / 12 x 8"(thick) /27=1.77 Cubic yards

2. 1.5' x8"(.667)/12x576(inches in 48')/27=1.77 Cubic yards.

Of course in the second formula there is one more step due to one having to calculate ho many inches there are in 48 feet(576)

3. Length x the width divided by 81=(4inch concrete)54=6inch 40=8 inch or 27= 12inch or 1foot. I have been using the first formula for years and I am just use to calculating that way.

Wow, the wonders of modern feet and inches! ;)

Share this post


Link to post

Truer words have never been spoken! Unfortunate you can't enter those formulas into a spread sheet that way!

Share this post


Link to post
Pat, That worked! I am assuming I can add those formulas to the wall worksheet that was generated from the drawing? Is it possible to have a separate worksheet for each layer? I drew the footings on one layer and the walls on one layer, but the wall report is being generated from both layers.

Thanks again Pat..really! I just need to tweak it now.

Terry

Yes, you can move those formulas to anywhere you want. To limit is to just a single layer, you have a couple of options.

The easiest is to modify the criteria of the database to limit it to a single layer, but then you will need to create a separate database row for each layer.

Another option is to add another column with a formula of =L. This will then give you the layer of each wall. It will be harder to get a total for each layer doing it this way.

I'm glad it worked.

Pat

Share this post


Link to post
Yes Chad,

That is true. one can figure this several different ways

1. 48'x1.5 / 12 x 8"(thick) /27=1.77 Cubic yards

2. 1.5' x8"(.667)/12x576(inches in 48')/27=1.77 Cubic yards.

Of course in the second formula there is one more step due to one having to calculate ho many inches there are in 48 feet(576)

3. Length x the width divided by 81=(4inch concrete)54=6inch 40=8 inch or 27= 12inch or 1foot. I have been using the first formula for years and I am just use to calculating that way.

I am just use to looking at a footing and seeing length and width and knowing the "thickness" I calculate it that way. When I look at a wall, a basement wall for instance, I see length and height and knowing the thickness and calculate it from that point of view!

The point was, in the post I quoted, you seemed to be implying that the volume reported was incorrect.

If you need the wall's footprint area, you can use the wall's volume ( a cell with "=VOLUME") divided by the wall's average height (a cell with "=WALLAVERAGEHEIGHT"). I used an additional column that said something like "=D3/B3".

The rest is just factors applied to those values as you need.

This seems a lot simpler than you all are making it out to be, but I remain a spreadsheet ignoramus despite occasional efforts to beat additional knowledge into my head.

Share this post


Link to post

Chad, Yes My head also seems to build pressure when trying to beat something into it! I am sure it is more simple than my brain can process! :-) Somewhere along the educational line I will finally see it! Not only have I been picking up pointers from you and others but I have been scouring the manuals and help files and tutorials. I need some air!

Thanks Chad!

Share this post


Link to post

I've been working with Terry offline to whip a worksheet into shape. First, I'll post the worksheet/file and then some step-by-steps with images to help others get there.

Edited by Jeffrey W Ouellette

Share this post


Link to post

Step 1. Getting Started

You could start with a blank worksheet, or you could use the menu command "Tools > Reports > VA Create Schedule" in the Architect Workspace and select the "Wall Area" schedule.

Step 2. Setting database criteria

Reformat the criteria of the worksheet to delimit the database to only one design layer per section, but include multiple databases, thus multiple floors in one worksheet.

Select the Database header row, 3, and right-click on the row number to open the contextual menu items. Select "Set Criteria". [Worksheet_edit-1].

When the "Criteria" dialog opens, adjust the criteria to include "Layer > is > Mod-CIP Footing" (our your own layer).

Select "More Choices" and include "Type > is > Wall"

Select OK.

The worksheet should have changed. [Worksheet_edit-2]

Next, expand the size of the worksheet by moving the cursor to the bottom right-hand corner of the bottom right-hand cell. The cursor should change to an "angle" shape. Left click and hold the left mouse button and drag down and right. You should see a ghost outline appear to indicate how far the worksheet is expanding. [Worksheet_edit-3]. I've expanded it to 10 columns.

Select row 7 and right click for context menu, select "Database", then set criteria again in dialog, just like before, but this time choose another layer (e.g. Mod-Foundation). Results should look like "Worksheet_edit-4".

Next slect the header cells A3 thru E3 and Copy, then select header cells A7 thru E7 and Paste. [Worksheet_edit-5]. Voila.

next lesson tomorrow...

Edited by Jeffrey W Ouellette

Share this post


Link to post

Step 3. Expanding scope of the worksheet.

Start with labeling the new columns [Worksheet_edit-6]

Then, insert six new rows just between the title in the first row and the column label row. Do this by selecting a row header, holding down the left mouse button and dragging down to include highlighting row 7. Then select the small menu triangle and select "Insert... > Rows". [Worksheet_edit-7]

Enter a number of labels and fields for "cost variables". [Worksheet_edit-8].

Now enter formulas/functions into the header row for the first database row [Worksheet_edit-9 thru 13].

Step 4. Format the database cells to report the desired units.

By default, worksheet database cells with numerical entries are decimal "un-formatted".

Click-drag the cells under the "Area Gross" label. With the cells highlighted, right click while your cursor is inside the highlighted area. Choose "Format Cells..." from the contextual menu. [Worksheet_edit-14]

The "Format Cells" dialog will appear. Select the "Number" tab if it is not already active. Then select the "Dimension Area" radio button. [Worksheet_edit-15]

Repeat for the other columns. For costs/currency formatting, use the "Decimal" setting with (2) Dec. Places and "Use Commas" checked. Add a $ sign and a couple blank spaces in the "Leader:" field. [Worksheet_edit-15]

Step 5. Format the text and style of the borders. Highlight cells, right click for the contextual menu and select "Format Cells..." again, this time cycle through the other tabs to set the font type, size and styles, as well as borders.

Finale tomorrow...

Share this post


Link to post

Hey Jeffrey, this looks great, but could you post the VWX file in 2008 version?

Thanks

Share this post


Link to post

The Finale, as promised....

Step 6. Totals

With formatting complete create a new "Totals" row, Row 19. In Cell F19, enter the formula, =F12+F16, to add the two worksheet database totals. [Worksheet_edit-16]

Extra Credit:

Some of you have noticed some formulas missing from documentation. Sorry about that. We aren't intentionally hiding it from you. Some things have slipped through the cracks over the years since worksheets were first introduced in 1988 (MiniCad+ 1) and database worksheets in 1991 (MiniCad+ 3).

Want to report wall height? Try:

=WALLAVERAGEHEIGHT

See some strange values from wall length? Try:

=LENGTH/12

and format the cells to "Dimension". This should give you the correct feet and inches length. Yeah, its a known bug.

Want Cu Yd instead of Cu Ft from wall volume? Try:

=CRITERIAVOLUME/27

and format the cell to "Decimal" with desired decimal places and Trailer label.

Another tip. Remember that formulas in VW worksheets follow algebraic rules of hierarchy; that is, using brackets in formulas will solve the value inside a pair of bracket before "moving outward" and solving the next part of the equation. Try this:

=((C28*12)-4.5)/12

First, C28 multiplied by 12 is solved, that value then has 4.5 subtracted from it and that value is then divided by 12.

Hope this all helps. Thank Terry (Wyle C) for prompting the discussion and the others (like Pat, Chad and gmm18) for pitching in...

Share this post


Link to post

I have a question related to this. I can't find the answer in the help files.

What's the difference between:

Volume and CriteriaVolume?

Area and CriteriaArea?

VolArea and CriteriaVolArea?

EDIT: already found it out, Criteria... will give you the Volume,... but with the Volumesetting under units.

Edited by DWorks

Share this post


Link to post

I more thing....

Offline, I've been helping Terry further tweak and expand the capabilities of this sample worksheet. It has given me some ideas about additional reports to include in future versions of VW.

Please feel free, with Terry's blessing, to witness the results of this particular collaboration...

Share this post


Link to post

I would also Like to thank Pat, Jeffrey, gmm18, Chad and Charles, for all the input. It was and is a great discussion. I never realized what a fabulous tool a customized worksheet could be.

I have another question: Is it possible to save these customized reports as "templates" so when you create a report or schedule that name is in the dialog box list? And the resource browser would be great if you could create folders in each of the sections of the browser e.g. "Worksheets" to organize and save space.

Terry

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×
×
  • Create New...