Adding Sums to Report Worksheet

[trashcan]

I have a bunch of Projectors with attached records. I want to create a report that shows the total weight per item. 

 

They all have the same record type. 

 

In the record of each object, I have 5 weight criteria.

1) Flange weight

2) Pipe Weight

3) Mount Weight

4) Projector Weight

5) Lens Weight

 

The formula would be something like =SUM(1,2,3,4,5) - how is it done?

 

It would seem to me that I'd need to add a Custom Field with formula:

 

But how do I add in the cell reference here? 

 

Also, does it annoy anyone else that Possible Columns is in alpha order and not in the order of the actual record?

[Pat Stanford]

Are you showing those fields separately on the report also? If so just add an extra column with a formula of =A3+B3+C3+D3+E3. Using the proper columns and the number that is the header row of the database section of the worksheet.

 

If you are not showing them separately you could also do a formula like

 

='Record'.'FlangeWgt'+'Record'.'PipeWgt'+....

 

Using the proper names for the records and field.

 

SUM is used to sum up columns, not across rows (I think).

 

This may be easier to do by editing the worksheet formulas directly than by trying to use the report generator.

[Dmitry]

On 11/2/2021 at 1:37 AM, Pat Stanford said:

Are you showing those fields separately on the report also? If so just add an extra column with a formula of =A3+B3+C3+D3+E3. Using the proper columns and the number that is the header row of the database section of the worksheet.

 

If you are not showing them separately you could also do a formula like

 

='Record'.'FlangeWgt'+'Record'.'PipeWgt'+....

 

Using the proper names for the records and field.

 

SUM is used to sum up columns, not across rows (I think).

 

This may be easier to do by editing the worksheet formulas directly than by trying to use the report generator.

Hello. 

I tried do this,  but it's not  working.

='Record'.'FlangeWgt'+'Record'.'PipeWgt'+....

For example

I do report for doors and I want merge to parameters (prefix and label) in one cell. How can I do that? 

Sorry for my English

[Pat Stanford]

First, Record should be 'Projector Weight'.  If it does not work with that change then my guess is that those values are being stored as strings instead of numbers.

 

If you create a column with just one of the values (='Projector Weight'.'FlangeWgt') does it just show numbers or is there a unit mark included.

 

If there is no unit mark try making a second column with (assuming you are using column C and the database header is in Row 3) =C3+100.  If that shows the weight plus 100 then you know it is stored as a number if that does not work then it is stored as a string.

 

If it is stored as a string then make column D have a formula of =Value('Projector Weight'.'FlangeWgt') and then in Column E try the =D3+100.

 

Value converts a string that expresses a number into an actual number you can do calculations with.

 

Ask again if this does not work.

 

Or post a very simple file with a couple of the objects in it and your worksheet and I will take a look.

[Dmitry]

13 minutes ago, Pat Stanford said:

First, Record should be 'Projector Weight'.  If it does not work with that change then my guess is that those values are being stored as strings instead of numbers.

 

If you create a column with just one of the values (='Projector Weight'.'FlangeWgt') does it just show numbers or is there a unit mark included.

 

If there is no unit mark try making a second column with (assuming you are using column C and the database header is in Row 3) =C3+100.  If that shows the weight plus 100 then you know it is stored as a number if that does not work then it is stored as a string.

 

If it is stored as a string then make column D have a formula of =Value('Projector Weight'.'FlangeWgt') and then in Column E try the =D3+100.

 

Value converts a string that expresses a number into an actual number you can do calculations with.

 

Ask again if this does not work.

 

Or post a very simple file with a couple of the objects in it and your worksheet and I will take a look.

is not working (

[Dmitry]

Report problem.vwx

[Pat Stanford]

So since those are definitely string fields and not numbers, you don't want to use the + (which does addition of numbers) you want to Concat (short for Concatenate) to combine the two text strings together.

 

=Concat('Door'.'IDPrefix', 'Door'.'IDLabel)

 

You may need to use a semicolon [ ;  ] between the values to be concerted instead of the comma [ , ] that is used in the US. It will depend on the decimal separator of your operating system.

 

HTH

 

[Dmitry]

3 minutes ago, Pat Stanford said:

So since those are definitely string fields and not numbers, you don't want to use the + (which does addition of numbers) you want to Concat (short for Concatenate) to combine the two text strings together.

 

=Concat('Door'.'IDPrefix', 'Door'.'IDLabel)

 

You may need to use a semicolon [ ;  ] between the values to be concerted instead of the comma [ , ] that is used in the US. It will depend on the decimal separator of your operating system.

 

HTH

 

Unbelivible!
I tried only [ , ] with = concat . It's works with [ ; ] !!!
Thx so much!
But I have another problem with Door and windows. How can I get sill Heigh to my report? 
For exsample I need to show sill heigh relative to ground floor zero. On second floor sill heigh will be +3.700 (for exsample)

[Pat Stanford]

Create a new Vectorscript in the file. The Palette name does not matter. Name the script AbsDoorZVal.  Paste the script below in.

 

Enter a formula in the database header row of  =RunScript('AbsDoorZVal')

 

This will give you the Z value of the layer that the door is on.  If you need some other field to offset it from that value you will have to let me know what that offset is and we can adjust the script.

 

This only works on Door in Wall objects.  If you have a door by itself on a layer it will return three dashes instead.

 

Procedure DoorAbsZVal;

{February 25,2022}
{©2022 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}
{No warranty expressed or implied. Use at your own risk}
{Test before using on real data}

VAR	H1, H2, H3	:Handle;
	R1,R2		:Real;
	B1			:Boolean;
	
BEGIN
	H1:=WSScript_GetObject;
	H2:=GetParent(H1);
	If GetType(H2)<>68 then 
		WSScript_SetResStr('---')
	Else
		BEGIN
			H3:=GetLayer(H1);
			GetLayerElevation(H1, R1, R2);
			WSScript_SetResReal(R2);
		End;
End;

Run(DoorAbsZVal);

 

[Dmitry]

8 hours ago, Pat Stanford said:

This will give you the Z value of the layer that the door is on.  If you need some other field to offset it from that value you will have to let me know what that offset is and we can adjust the script.

Hello!
This script is not working.

[Dmitry]

I tried solve this problem with formula. But it doesn't work (
I think I don't understant logic of program. Mb somebody can give manual? (((