Subtotal in reports

[halfcoupler]

Hi @ all,

I want to count the instances of symbols separately for each design layer.

I'm just wondering whether it is possible to have subtotals of counting symbol instances per several layers in one report, or do I always have to make one report with criteria for each layer ? That would be 15 reports for 15 layers, - a bit cumbersome ... I'm shure there must be a better way.

[Pat Stanford]

How about using a simple Count function.

 

=COUNT((((L='Design Layer-1') & (S='Symbol-1'))))

 

You can use the Worksheet:Insert:Criteria to build the criteria that needs to be in the inside.

[halfcoupler]

I'm afraid it's a bit more complicated than just counting. In the end I want to have something like a pivot-table, where colums are the layers and rows are the symbols. The corresponding fields sum up the amount of the symbol in the layer ( see attached sample )

 

From a database-view it's just the problem of a n:m ( many to many ) relationship between tables: A table of layers can be connected to several symbols in the symbol-table and vice-versa. In report software of databases this is usually solved by subgroups.

 

In the meantime I have made 16 reports for 16 layers to finish my project...   

 

Table00.pdf

Edited June 7, 2017 by halfcouple

[RickR]

I think this is very do-able.

Criteria would be Type=symbol, maybe class, etc

Column 1 = Layer name (sub total on that)

Column 2-x  would hold Pats formula and should sum up.

 

Does that make sense? This should be very similar to totaling fixtures by position.

 

Note: Formulas are conceptual, I haven't opened VW to verify the formatting.

[halfcoupler]

maybe I'm making things more complicated than tey really are, but can't get it to work:

- Im using a report with database headers

- Pat's formula does not seem to work in a database header row, since 'Design Layer 1' and 'Symbol 1' are Text values, but they should automatically refer to the symbol name that is called in the row and the layer name that is called in the column.

 

of course it works in a single cell, but I dont want to manually create a calculation formula for any symbol with any layer.

 

Edited June 8, 2017 by halfcouple

[Pat Stanford]

The =Count() [without criteria] in a database cell will return the number of items in the row. If you are using a SUMmerize tile, it will show the number of subrows that are SUMmarized together.

 

I think I can write you a custom Worksheet Script that will allow you to get what you want.

 

Would this work?

 

Create a database with criteria to give you the symbols on all layers. Create a column that displays the symbol name. Put the layer names in cells above the database in the proper column. Put a custom script that will read the symbol name from the  database row and the layer name from the labels above and use that data to calculate the number of symbols on the layer and then return that information into the database cell.

 

If that sounds reasonable, I will see what I can come up with.

[halfcoupler]

'Create a database with criteria to give you the symbols on all layers. '

DONE

'Create a column that displays the symbol name. '

DONE

'Put the layer names in cells above the database in the proper column. '

DONE

'Put a custom script that will read the symbol name from the  database row and the layer name from the labels above and use that data to calculate the number of symbols on the layer and then return that information into the database cell. '

FAILED

So this must be something  like     =SUM(((L=THIS COLUMN) & S=THIS ROW))      ?????     How do I refer to certain cells, colums and rows in a formula ?

[Pat Stanford]

Yeah, the custom script would be in Python of (more likely) Vectorscript and the formula would look something like =RunScript(SumIntersection,D,6).  But the script has not been written yet. You would have to put a version of the function in every column where there was a layer above.  Hopefully next week.  My next 4 days are non-stop.

[RickR]

Did you mean something like this?

Sum by Layer.vwx

[Pat Stanford]

Nice divergent thinking and it gets the data out, but what I was thinking was more like this mockup:

 

 

[halfcoupler]

Hey Rick !

Yes thats it !!!   So easy !!! So the trick is simply to sum up BOTH columns, Layer-collumn  AND Symbol-column. Didn't see that !

 

But the pivot table would be of course more comfortable, since it has no repetition of the layer names. I think that automatically generating the columns for the layers will be the most complicated thing to do in this task.

 

 

Edited June 10, 2017 by halfcouple

[Pat Stanford]

I don't think we can do the pivot table thing right now.

 

There are no Functions or formulas to get the current cell that the formula is working in, so there is no way to get the relative cell that holds the "Symbol Name" in my mockup above.

 

I have submitted a request for a function to be able to get the cell a function/script is operating in, but I am not holding my breath.

 

Sorry.