Jump to content
Sign in to follow this  
Wyle C

Spread Sheet Reports

Recommended Posts

Tobias and Petri,

In 2009, four new Functions have been added to the Database Worksheet to handle roofs.

RoofArea_Heated

RoofArea_HeatedProj

RoofArea_Total

RoofArea_TotalProj

These will quantify the ?heated? and ?total? areas of a roof plane, calculated either along the slope of the roof, or projected on the ground plane. The ?heated? area is the area that does not include an overhang, which is defined here as the area upslope of the projected roof reference line. The ?ROOFAREA_TOTALPROJ? area is currently what is returned from a roof-slab using the AREA() function. See the attached illustration.

Share this post


Link to post

Tobias,

Sorry, at this time, including 2008 and 2009, you can't query the length of a path (NURBS Curve) inside the Extrude Along Path PIO. You can use "=LENGTH" to get a NURBS Curve length in a standalone case, just not inside the PIO. I will add this as a bug ans see how soon we can get it taken care of.

This is a good catch and I agree that it is important to further utilizing an effective BIM workflow.

Share this post


Link to post
Tobias and Petri,

In 2009, four new Functions have been added to the Database Worksheet to handle roofs.

Right, OK. I've noticed these new functions; now I know what the are. And they appear to be very good! Now If I only could get roofs reported in the first place...

Share this post


Link to post

This is a good catch and I agree that it is important to further utilizing an effective BIM workflow.

Areas and volumes, too! But how?

Actually I believe that this entire aspect belongs to the PIO programmer. In my objects I may have the relevant parameter fields, often hidden in the OI, but able to be reported.

I don't use many (in fact use hardly any) NNA's objects. I have the recollection that eg. path objects display & report areas & perimeters wrongly. I hope I'm wrong here...

EDIT

I realise that EAP is not a PIO per se, but maybe all complex constructs could be considered as objects, not as primitives? Just a thought...

Edited by panta rhei

Share this post


Link to post

Thanks to Pat Stanford for showing the CONCAT( ) and IF( ) statements. I've been using a RoomName/Number/Finishes symbol (to do Finish Schedules) with two title lines, for years and have had to resort to a separate line in the Record Format for the on-schedule name that uses a third manually entered line to combine the two title lines for the schedule listing.

The statement looks for a name-top entry and if it exists it concatenates it with the name-bottom entry to display the full room name on the schedule. It could have been done more simply by using only the CONCT( ) statement, but it puts a space in front of the room name if there is no name-top entry. It took a while to get the syntax correct, but here it is:

=IF(('room name/no/finish'.'name top'<>''), CONCAT('room name/no/finish'.'name top', ' ', 'room name/no/finish'.'name bottom'), 'room name/no/finish'.'name bottom')

Thanks again Pat?that's bugged me for years.

Share this post


Link to post
Tobias,

Sorry, at this time, including 2008 and 2009, you can't query the length of a path (NURBS Curve) inside the Extrude Along Path PIO. You can use "=LENGTH" to get a NURBS Curve length in a standalone case, just not inside the PIO. I will add this as a bug ans see how soon we can get it taken care of.

This is a good catch and I agree that it is important to further utilizing an effective BIM workflow.

Jeffery, Thank you for your response. I don't however think I need to query the length of a nurbs curve, or extrude along path (although I can see that that would be useful as well.) I just want to get the ∆X,∆Y and ∆Z as they show up in the PIO of a simple extrude. Or are we saying the same thing?

Share this post


Link to post

Similar, but different.

The ∆X and ∆Y are the same as =Width and =Height, based on how the original object of the extrude is oriented. so, if I draw a x=2 and y=3 rectangle, my =Width and =Height values are 2 and 3, accordingly. If I rotate the object 90˚, then these values flip to =Width is 3 and =Height is 2.

Currently, we can't query the "extrude" value. Another good catch. I'll enter that one into the bug system immediately.

The "Extrude" command and the "Extrude Along Path" commands are different. The second is more complex and has different parts to the data structure. Fortunately, this discussion has brought up good points for future development to meet obvious needs for reporting/querying both object types and more.

Edited by Jeffrey W Ouellette

Share this post


Link to post

So.. is there a way to get the gross area of a roof object into a worksheet (preferably in VW 2008, I cannot upgrade at this time)? I can't seem to work it out. Any help would be appreciated.

-Tobias

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! ;)

We share a language (for the most part).

Thank God we don't share a measurement system.

Surely feet/inches/yards with a mixture of fractions and decimals means you guys make calculation errors all the time?

I can't understand why you hang on to this archaic system when the rest of the world has gone metric. It would certainly ease your spreadsheet formulas...

Share this post


Link to post
Tobias and Petri,

In 2009, four new Functions have been added to the Database Worksheet to handle roofs.

RoofArea_Heated

RoofArea_HeatedProj

RoofArea_Total

RoofArea_TotalProj

These will quantify the ?heated? and ?total? areas of a roof plane, calculated either along the slope of the roof, or projected on the ground plane. The ?heated? area is the area that does not include an overhang, which is defined here as the area upslope of the projected roof reference line. The ?ROOFAREA_TOTALPROJ? area is currently what is returned from a roof-slab using the AREA() function. See the attached illustration.

Turns out these are useless functions in non-McMansions, but certainly do what is promised. (Actually turned out that way a long time ago, I just didn't have the energy to report it.)

Firstly, one cannot generate a report of roofs. Secondly, surely there are situations, even in the U.S. of A., where unheated areas are under the roof.

Nice try, but no cigar.

Share this post


Link to post
Similar, but different.

The ∆X and ∆Y are the same as =Width and =Height, based on how the original object of the extrude is oriented. so, if I draw a x=2 and y=3 rectangle, my =Width and =Height values are 2 and 3, accordingly. If I rotate the object 90˚, then these values flip to =Width is 3 and =Height is 2.

Currently, we can't query the "extrude" value. Another good catch. I'll enter that one into the bug system immediately.

The "Extrude" command and the "Extrude Along Path" commands are different. The second is more complex and has different parts to the data structure. Fortunately, this discussion has brought up good points for future development to meet obvious needs for reporting/querying both object types and more.

I'm using 2009.

=Width and =Height are dependent of the view, not the object.

Try changing the view and recalculating the Worksheet.

This makes using this criteria unpredictable.

=CRITERIAVOLUME/CRITERIAAREA will give you the height of an extrude (i Think, it works for me on simple extrudes with a rectangle profile)

As Xtruds can have complex profiles, there is no such thing as 'width' or 'height' of the Xtrud.

If the object is a simple box, you can use the 3D box and search for

=(('Box Object'.'x'))

As usual there is a work around, but often not intuitive.

If you want control learn to use VectorScript.

Share this post


Link to post

WISH LIST:

When Searching Criteria it would be good to be able to exclude objects inside View Ports.

Share this post


Link to post

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

Great that you acknowledge this Jeffrey. Now a few months have past can you post where this is documented.

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

That worked! I'm assuming I can add individuals formulas towards the wall worksheet that was produced in the drawing? Is it feasible to possess a separate worksheet for every layer? I drew the footings on 1 layer and also the walls on 1 layer, however the wall report is becoming produced from the two layers.

Thanks once again Pat..truly! I just require to tweak it now.

John

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

That worked! I'm assuming I can add individuals formulas towards the wall worksheet that was produced in the drawing? Is it feasible to possess a separate worksheet for every layer? I drew the footings on 1 layer and also the walls on 1 layer, however the wall report is becoming produced from the two layers.

Thanks once again Pat..truly! I just require to tweak it now.

John

Did this come from a different thread? I think I am a little lost here. What do you mean by "add individuals formulas towards the wall worksheet"?

You can certainly have a different different database for each layer. They can either be in one worksheet or multiple worksheets. Select the database header row (ie 4, not 4.1. If you don't see it go to the triangle pull down menu and select Database Headers.) and right click in the row header and Edit Criteria. Then you can add an extra criteria to specify the layer for the database to select objects from.

You should also be able to use the total for the database in other calculations

by just referencing the correct cell (again 4 not 4.1, 4.2, etc.)

You can also reference cells in other worksheets. Check the documentation for the proper format.

Share this post


Link to post

Can you area calcs for componets, ie sheathing, siding, GWB etc?

Share this post


Link to post

If you are using Styled Walls with components in VW2001 you can use the ComponentArea, ComponentName and ComponentVolume functions in a database section of a worksheet to get the information you are looking for.

Share this post


Link to post
Guest mazda

Thanks for the info.....

Share this post


Link to post

Using VW 2009, just created an active worksheet (dumbly located as "VA create report" under Tools) imported a tab delimited Excel file and it was badly mangled as to be useless. Imported a .pdf of the fle instead.

Theres got to be a better way.

The VA Create report allows only a bunch of pre-created templates (Door Schedule, etc.). Can I create a blank worksheet?

Surely someone has done this sort of import before with success?

Novice user,

RJC

Share this post


Link to post

Worksheets are Resources. You create a new blank worksheet in the Resource Browser. Choose the second little black "disclosure triangle" and your should see options to create any type of resource that you want.

The VA Create Report is a shortcut to generating a worksheet reporting the data from Vectorworks Architect objects that have data records attached.

Share this post


Link to post

Thanks, that helps.

However, I've attached the pdf as a referenced file meaning when I update the orignal .xls file it updates automatically in VectorWorks. This is working for me except I don't understand what circumstances VW uses to auto-update the file (when it opens?) nor do I know how to tell VW to update the referenced files, which I'd like to do directly after changing the xls file so I see how it looks on the VW page.

Any advice on that?

Cheers!

Share this post


Link to post

Is it possible to set the criteria for a database row from a worksheet cell?

I am preparing an area summary for a project that has multiple storeys. I would like to be able to enter the floor (or any other criteria for that matter) in a cell on the worksheet and for the various database rows that I have to then use that input.

ie. at the moment the database criteria is (('Space'.'11_Floor'='X')) where X is the floor number manually inputed. I woudl like to enter a cell reference in place of X however this doesn't seem to work. I have tried 'B2', '=B2' and =B2 to no avail. I have also changed the formating of B2 to text to see if that helped.

This seems to be related to the number formating of the cell - it works fine for text based input as just =B2 (eg if I set the Floor value to A in my space and enter A in B2 then the formula calculates correctly.

On a similar topic when I use the CriteriaArea function for the gross areas of spaces my results are all out by a factor of 2 - does anyone know what I am doing wrong?

Edited by Tim Harland

Share this post


Link to post

Criteria are not dynamic in VW2012, so no, you can't just change a cell and have the criteria change.

Criteria can be set by Vectorscript, so you could write a script that would read the contents of a cell and then change the criteria to match, but you would have to manually run the script to get the worksheet to update and recalculate.

The down side of this is that it would be possible to change the cell, but not run the script. When you print it it says it is for Floor 2, but the data is still for floor 1.

If the script idea would help you, send me a file with the worksheet (and some sample data) and a description of what you would like to happen and I will see what I can do.

As for the area being off by a factor of 2, are your rows SUMMED? If so, do you have viewports in your drawing? If so, it is probably counting both the original object on the design layer and the ghost copy in the viewport. You will need to edit the criteria to exclude one of those or to only include a specific design layer. Or is it possible that you actually have a second copy on the design layer?

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.

×