Jump to content
Sign in to follow this  
Wyle C

Spread Sheet Reports

Recommended Posts

Jeffrey and others,

What you guys have done here is phenomenal

About 18 months ago I quantified using Worksheets a landscaping project ? 23 houses

I could not have done it without the help of our distributors here in Australia

We calculated turf /m2, soil /m3, timber edging / lm, mulch / m3, boulders / number and m3, paving slabs for stepping stones / number, concrete under paving / m3, pavers / number and m2, sand under pavers / m3, concrete for edge restraining of pavers / m3 etc etc and for seeded lawn areas even lawn seed and lawn starter in kgs

We did these totals for individual lots and then added them for the whole project

I used location polys for each lot to enable calculations within those lot boundaries and Class Structures to identify each of the landscape elements

Incredible tool that took a good deal of work to set up but once I had it a very valuable resource into the future

The manner in which all of this is done is clunky and very non user friendly and therefore very probably why lots of users may not go there

When I draw now ? I have hatches and other finishes for things like mulch and turf ? within my design layer work I have hatches turned off in my Classes ? just keeps my drawing simple to use ? I use Sheet Layer Viewport overrides to turn them on ? normally I duplicate my Viewports and have one for Planting ? no hatches but colourful plants and the other for what I call Surfaces and Finishes ? no plants but hatches with a Legend to identify them ? it all works well

What is cool is I can draw with a closed poly an area of say mulch and voila it automatically calculates whatever I want is to ? even the soil quantity underneath

I have a ?master Standards File? I use ? I start with a Template File that is very simple and as I work if I want say Class ? Paving-Brick I ?import it? from my Standards File ? works well too

The power of Worksheets is amazing especially considering all of the focus on BIM

The ease of using them in my view needs to be addressed and the manner in which people can customise VW for their own purposes ? examples storing reusable Viewport Class Overrides thought through, being able to turn Stipples off in Viewport Class Overrides etc

Perhaps I should expand this and post it into Wish list Items

What you guys have done though with this thread as a resource is phenomenal

Share this post


Link to post

I too believe that customized reports can be a a powerful tool when use in concert with VWs drawing capabilities. Jeffrey and Pat were great along with input from everyone listed on this topic. And in a short time the ball was rolling. First post was on the 18th. The reports were one of the main reasons I purchased VWsA 2009. I have been spending more time on reports than the actual drawing.

Now all VWs needs is a mental interface so it just does what you think! :-)

Terry

Share this post


Link to post

Just wanted to add a few thoughts. Users have beens asking for better documentation and a simpler interface for years. Not to let Nemetscheck off the hook on this, bit that is not a trivial undertaking.

I think one of the main problems is how flexible they are. For EG this thread started with Terry's drawing structure of different wall types on different layers, and separate reports to deal with them. And that's what he got. He might have started with a structure that has everything on one layer, and he would have a completely different worksheet set-up.

There are many ways that a data-base row can distinguish one object from another: Class, Line-Weight, Name and info in attached records to name a few that I use a lot. A user needs to think deeply about what data they need to extract and how they want to manipulate that data before setting out on this journey.

Personally I don't want a worksheet that relies so heavily on actual drawing structure. I want to be free to draw anything anywhere, and do the data work when I'm happy with the drawing.

One possible strategy might involve attaching a record to each pour. The record format could have fields to identify the different kinds, and the costs involved with each, including forming labour, clean-up costs and so on. A data-base can find objects based on records attached and fields within, and can grab any info that is contained in the those fields.

Or you might have a few record formats: one for each type of pour. The cost fields could have their default values pre-set for each situation. Then it's pretty much a one click operation to attach the right record, and feed the data to the worksheet. If you have a one-of-a-kind situation and need to make an adjustment to one particular pour you just tweak a number in the record.

My main point is that to truly explain and instruct on this subject you'd need a very robust book. Something like FilemakerPro for Dummies...if there is such a thing.

I think there's no end to what you can do with worksheets once you get started.

Thanks for listening,

Charles

Share this post


Link to post

What does #OBJ? or #CVAL? mean when entering a formula into formula line/cell? I use the same formula in other cells and the information I want is imported, however for some reason this doesn't work in this row of cells. It's not a header row, there are no header rows, it's only a spreadsheet.

Edited by Wyle C

Share this post


Link to post

#OBJ will mean that the formula is not correct and that VW can't seem to find the objects.

#CVAL means that the value can not be calculated because the formula is not correct or other reason.

Share this post


Link to post

Is it possible to concatenate 2 cells in 1 cell?

Example:

cell B2 has the text 'Project' in it.

cell B3 has the text 'Alfre' in it.

What I want:

cell B4 need to be 'Project - Alfre'.

Can I do this?

I really need to do this through multiple worksheets, So one has all the info and in others I need to put those info in some cells.

Share this post


Link to post

I have a novice question (I think). I have set up a worksheet that lists my exterior walls and the gross square feet, and the sq foot pricing for the different types (cool). I would like to have all the walls of the same class listed in a single worksheet, but have them broken up by floor (layers). This is easy enough using =layer, but I would like to have the floor listed in plainer language than I use for my layers, for example instead of listing it as my layer "Mod-Floor-1", I would like it to read as "1st Floor"

I hoping to do this with _if_. Am I on the right track?

Thanks for any help.

-Tobias

Share this post


Link to post

Two options:

1. You can put multiple databases in a single worksheet. Just set the criteria for each one to specify the layer. Then you can label them outside of the database section in the regular worksheet cells.

2. If you have a reasonable number of layers you can do a fancy nested IF function something like

=if((L='Mod-Floor-1'),'Floor 1',If((L='Mod-Floor-2'),'Floor 2',''));

Continue out the pattern for each floor.

Pat

Share this post


Link to post

Pat, Can't seem to get 'if' to work. I think your first suggestion will work better anyway. Could be the late hour...

I did however notice some odd behavior. When VW calculates net square footage, it will subtract such things as base cabinets from the gross wall area, if the cabinet is inserted in the wall. (I usually don't put them in the walls, but sometimes they slip in anyway) I notice this because VW was giving me some negative net square foot numbers for some walls, which is impossible in real life.

The file illustrates the behavior.

Share this post


Link to post

I think the secret to the IF is to make sure the criteria portion is in a separate set of parentheses.

=if((Criteria),'Criteria True','Criteria False')

So each IF needs to have two sets of parens.

Depending on what you are trying to do, you can replace either the Criteria True or the Criteria False part with another IF statement. If you replace the False, it is like have an If, The, Else statement.

I did actually try it two layers deep in VW2009 and it worked fine.

The actual formula I used is:

=IF((L='Mod-Floor-1'), 'Flooor 1', IF((L='Mod-Floor-2'), 'Flr. 2', ''))

I can see uses for the and why the Net wall area is lowered when you have objects inserted in the wall. That is why you have both WallArea_Gross and WallArea_Net functions in VW2009.

I don't know of a good way to get the length, width and height of 3D objects into a worksheet, even in VW2009.

Pat

Share this post


Link to post

To everyone in this thread:

As a lurker on this topic, I really appreciate all the great suggestions and help given. I tried to create some spreadsheets in the past to attempt what you have illustrated, but never got nearly as far as you have (even with some MS Excel experience). I think it's time for me to re-visit the use of spreadsheets in our projects. If set up correctly, I can really see this being a very helpful tool for our estimator.

Thanks again for all your collective input.

Share this post


Link to post
Pat, Can't seem to get 'if' to work. I think your first suggestion will work better anyway. Could be the late hour...

I did however notice some odd behavior. When VW calculates net square footage, it will subtract such things as base cabinets from the gross wall area, if the cabinet is inserted in the wall. (I usually don't put them in the walls, but sometimes they slip in anyway) I notice this because VW was giving me some negative net square foot numbers for some walls, which is impossible in real life.

The file illustrates the behavior.

Tobias,

Please file a bug on this one:

http://www.nemetschek.net/support/bugsubmit.php

Share this post


Link to post

Pat, Thanks for all your help, I am now able to get some useful worksheets happening. I am going to mess around with'if' tonight.

Jeffrey, I will submit a bug report if you tell me how to get an extrudes height,width and thickness into a worksheet, or at least respond to the question.

Share this post


Link to post

I don't know of a good way to get the length, width and height of 3D objects into a worksheet, even in VW2009.

Do these even exist? What would be the "length" of a 3D object?

Share this post


Link to post

Does anyone know what directory the "viewnamestds,classnamestds, and layernamestds files are in?

Share this post


Link to post

If you create a simple extrude, say a rectangle 24 units x 12 units and extrude it to 6 units, the PIO will list delta X = 24, delta Y = 12 and extrusion =6. Those values remain consistent no matter how the extrude is oriented on the page. They will also update if you change the size of the extrusion. I want to get those values into a worksheet.

Share this post


Link to post

I am having trouble getting the sq ft of the roofs in my model into a worksheet. The 'Area' function works for 'Roof Face' objects, but not for the 'Roof' objects created by the 'Create Roof...' command. I get the proper sq ft numbers for 'Roof Faces', but I get 0 Sq Ft for all 'Roof' objects.

If I use the 'SurfaceArea' function, I get Sq Ft numbers for all 6 sides (Top, Bottom,Sides) of both the Roof Faces and Roof objects.

Is there a way to solve this?

Share this post


Link to post
I am having trouble getting the sq ft of the roofs in my model into a worksheet.

Not that I'd have any use for the roof object, but...

I can't get roofs reported in a worksheet at all! I'm looking at a Roof. I'm saying "Create Report", "Objects with a Record". No "Roof" available in the pop-up.

VW 2009, SP 1.

Share this post


Link to post

Hey, Mr. N. of N. A.!

This seems to be a very, very good discussion on an important subject. A certain Mr. Carr, of Sydney, NSW, wrote an excellent article about it for Janis Kent's last (?) book many a moon ago. You would do a great service for the community (and reap the benefits) should you commission Julian to update his treatise to be incorporated in VW documentation.

On a retainer, mind you, with Mr. Stanford as a collaborator & contributur.

So, now we have "concat"... I may get even "ifs" to work (but what about "buts"? No, this is not actually flippant or frivolous! The key word is "set theory".)

Share this post


Link to post

for people that want to get started with worksheets, my Essential Tutorial manual has a section on worksheets. so if you have my manual for VectorWorks 11, 12, or 2008, then you will find the worksheet section at the back.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
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.

×