SUM of calculated detail field

Lars Pelzer

Hi all,

I've added a field in a query layout which contains a calculation of several other fields in the layout. The field works perfectly, but now I would like a total of that field in the grouping and a total of that field for the whole document. I can use an other field in a fields Value- or GenerateScript-property with a little code that looks like "Textbox13.Value". I can't use that code in the definition of the databands Totals or Aggregates and that's why I can't get the sum of that column in the group footer or in the document footer.....

Can anybody help me with this?
Thanks in advance!

Greetz
Lars

Giovanni Carletti

Hi Lars,
to solve your problem for example in the databand you can add in the collection Total a new member as:
AggregateFunction=sum,
Expression= GetData(your field),
Name= (Mytotal)
and in the group band footer , put a textbox named TextBoxTotal with the value: GetTotal (Mytotal)
Bye
Giovanni

Lars Pelzer

[quote]"cargio"]
Hi Lars,
to solve your problem for example in the databand you can add in the collection Total a new member as:
AggregateFunction=sum,
Expression= GetData(your field),
Name= (Mytotal)
and in the group band footer , put a textbox named TextBoxTotal with the value: GetTotal (Mytotal)
Bye
Giovanni
[/quote]

Sry giovanni, but it doesn't work :( ...
Designer keeps falling about the definition of the expression in the total (or in the aggegrate)...

I tried
GetData("Textbox46")
GetData(Databand("Textbox46")
GetData(Textbox46.ToString())

What am I doing wrong?

Thanks in advance!

Greetz
Lars

Giovanni Carletti

Hi Lars,
in Expression property, write GetData("xxx"), where xxx is your field of the databand (for me was GetData("B1_Data_examplequery_Cardname") )
If you're boring about sintax, I suggest to open your layout definition, save a copy in a file .rst, then from menu file, choose new-> standard report, then follow the screen instruction to select your query, your fields, the grouped fields you want and the aggregate function.
At the end when you have your layout definition, you can check the correct syntax for each object !
I hope it can be helpful!
Greetings
[color=green]Giovanni

Lars Pelzer

Hi Giovanni,

Thanks for your explannation! I know that it works for the fields you have in your query, but I would like to know how I can address a field on the layout in that expression. For instance a textbox...
Textbox1 and Textbox2 are assigned to a query field.
Textbox3 is not assigned to a queryfield and is a calculation of the first 2 fields. I've added a groupheader and groupfooter.

DetailRow1:
Textbox1 = 12
Textbox2 = 10
Textbox3 = Textbox1 - Textbox2 = 2
DetailRow2:
Textbox1 = 20
Textbox2 = 15
Textbox3 = Textbox1 - Textbox2 = 5

Through Totals or Aggegrates on the databand I can add totals in de groupfooter for the sum of Textbox1 fields and a sum of the Textbox2 fields.
Now I would like to add a field for the sum of the textbox3 fields.... And I can´t get it done, because I can´t call upon that field because it is not assigned to a queryfield. I would like to do the calculation of the layoutfields. You understand that I'm using an example right now and that the calculation behind Textbox3 is much more complicated :S ....

Can you help me with this?
Thanks in advance!

Greetz
Lars

Giovanni Carletti

Ok Lars,
now the scenario is clear: if you have many groups, I don't know how to do using Total collection: Layout designer seems not to show this possibility.
Here some other ideas:
1) for the first level of grouping, use a global var, in the detail generate script increment it and in the group footer assign this value to the textbox
For the document level total, you can copy your databand and past it below, use a new global var, in the detail increment it with the textbox value , put visible to false to each fields, insert a footer where you put a textbox with the var value.
2) calculate the field in SQL (i can help you..)
Greetings
[color=green]Giovanni

Lars Pelzer

Thanks Giovanni!

In the query isn't an option, because it would mean a lot of code :S ... And I'm trying to avoid that... So I'm working on the other option:

In the Common Script of the document:
dim GT as double

In the detail generate script:
GT = GT + Textbox46

Value of the textbox in the footer:
GT


This doesn't work :( .....
What am I doing wrong?

Thanks again in advance!

Greetz
Lars

Giovanni Carletti

Hi Lars,

sorry I was out of office in these weeks..

Could you forget data conversion instruction or perhaps you just write me the idea?

Try with..
GT = GT + CDBL(Textbox46.Text)

bye
[color=green]giovanni

Lars Pelzer

Hey Giovanni!

I've implemented a combination of our solutions.
I've put in the aggregates of the databand the expression "CDBL(Textbox46.Text)" and it works!!! I can group on cardcode for example or make summaries for the whole document.

Thank you very much for all your time and effort!!!

Greetz
Lars
0

Comments

0 comments

Please sign in to leave a comment.

Didn't find what you were looking for?

New post