Spread Sheet Reports

[Ozzie]

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

[Wyle C]

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

[ccroft]

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

[Wyle C]

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 November 12, 2008 by Wyle C

[Dieter @ DWorks]

#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.

[Dieter @ DWorks]

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.

[Jeffrey W Ouellette]

This would be a good Wishlist forum submittal

[Pat Stanford]

How about concat()

=concat(B2,' - ',B3)

Pat

[Dieter @ DWorks]

concat() doesn't work here.

[Hugues]

concat() is new for VW2009.

[Tobias]

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

[Pat Stanford]

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

[Tobias]

Pat, thank you for the quick response and the assistance. The "if" syntax is what I was looking for. Do you have any idea how I can call up the length and width of extrudes?

Problem outlined here:

http://techboard.nemetschek.net/ubbthreads/ubbthreads.php?ubb=showflat&Main=24006&Number=112773#Post112773

[Tobias]

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.

[Pat Stanford]

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

[atari2600]

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.

[Jeffrey W Ouellette]

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

[Tobias]

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.

[panta rhei]

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?

[Wyle C]

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

[Tobias]

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.

[Tobias]

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?

[panta rhei]

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.

[panta rhei]

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".)

[Jonathan Pickup]

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.