Using Workheets to report on Classes across multiple Layers

[Scook]

Hi There 

 

I am trying to create a worksheet that will generate a report on the quantity, cost and total weight of an item of theatrical scenery drawn in multiple classes over multiple layers.

 

 

Each Material class is reported on using the formula:

 

=VOLUME((C='Materials- Timber- Dar- 25x70') & (L='Flats- SL'))/1750000

 

I don't want to update the material (class) formula for every layer. Is there a way to use a reference cell to set the criteria and then use the cell number within the formula so that only the reference cell needs updating? Can a single formula contain multiple references to multiple layers?

 

For example I have a Doorway Flat (timber frame) on one layer and door on another to make the drawing easier whilst building the 3d model. However the construction drawing on a sheet would contain viewports from both layers showing the wall and the door . Ideally I would deploy the worksheet onto the sheet layer and generate costing/tech data from there. Any help with the syntax of the formula would be great

 

The only other possible solution I can think of would be to turn each finished model into a symbol and the use the INSYMBOL command  but this comes with its own additional considerations. Are there any obvious pitfalls with this approach?

 

I have tried to attach a sample .vwx showing my layer structure and worksheet but keep getting a -200 error

 

Cheers

Simon 

Edited August 21, 2018 by Scook

[Pat Stanford]

Hi Simon,

 

I am short on time today, so I can't give you the complete step by step, but this is the perfect case to use a Database Row in the worksheet.

 

Create a new worksheet

Right Click in the row header (I usually start with row 3 so I have room above to add the titles I always forget).

Select Create Report. Click the Advanced Criteria radio button and then the Edit Criteria button.

In the Criteria Dialog box set the Criteria to Class Is the class you want totals for.

If you don't want the objects on every layer, either enter a Layer IS or Layer Is Not criteria to limit the layers.

Click OK to close the Criteria Dialog

In the Columns section at the bottom choose from Functions:

   Class, Layer and click Add>>

Click OK to add the formulas.

Enter the weight per unit and cost per unit in two database header columns. VW Worksheets do not have a good way to do a lookup calculation, so the easiest way for now is to have a separate database row for each material and manually enter the weight and price.

Add a formula of =Volume to the database header row. This will get you the volume of all the items that are summarized in that row.

Add the formulas to the database header row to multiply the volume and Price and the volume and weight.

If you only want a single row for each material, click on the disclosure triangle in the Class database header row column and select the Summarize option. This will then give you a single row that contains all objects with that class (material). If you want a single row for each layer, instead Summarize the Layers column.

 

You can create the total Price and Weight cells by adding the values from the database header rows (which automatically total the values in the subrows for that database.

 

If you don't want to see the sums in the header rows, you can uncheck the View:Database Headers but still use the values there for other calculations.

 

Ask again if I have not been clear enough.

 

 

[Scook]

Hi Pat

Thanks for the reply, much appreciated. I managed to get the majority of this working correctly following your guidance but I think I have missed something. I couldn't find the create report option but the advanced criteria dialogue box opened automatically when I clicked Database in the row header so i just used that. If I have understood your instructions correctly there are more options available on the create report dialogue box, which is why I can't control the sheet in the same way you suggest. I also cannot for the life of me work out how to Summarise the materials in to a single row. I did find Create report in the spotlight menu but it only listed records or symbols as the possible columns but there was no advanced button.

Any Ideas where I went wrong?

 

 

 

Edited August 23, 2018 by Scook

[Pat Stanford]

Hi Scott,

 

What version of VW are you running? The interface changed a little for VW2018. You can still do it in earlier versions, just in a different fashion.

 

It looks like you are in VW2017 or earlier.  Select a Row in the database and then drag the "tile" (button) that says SUM to the Column Header (A, B, C, ...) of the column that you want to Summarize.  The other tiles work similarly but for sorting ascending/descending.

 

 

 

 

[Scook]

Brilliant! Thanks Pat I have it fully working now and with a minor tweak to the required layers I can rapidly report on materials rather than manually working it out. Its going to save hours of grunt work, as well as making the technicians happy as they will have a reasonable approximation of the weights for flying.

 

Thanks again for your help 🙂