Crystal Reports Total

Hello

I am trying to produce a running total field in a crystal report that does not include the current line i.e. the sum up to but not including the current record. I have to print a percentage of the total so far, which then has to included in the real running total field.

When I try to use the 'real' running total it is obviously a circular reference and Crystal bombs out. I have tried the options of calculating the total before reading the data and assigning this value to a variable and then using the variable after the total has been calculated but it still bombs out.

Any ideas?


Thanks :confused:

Do you need the current record on the report at all? I might tackle this by creating the running total field and subtracting the current value before printing it....

Several things: are you using a .dsr or a .rpt file. What version of Crystal are you using. Are you using ADO within VB?

Thanks for the reply

Unfortunately I do need the line on the report. I am using DAO with .rpt. I pass a recordset the the report which is created from a .ttx file at design time.

I was wondering if I could use the RecordCount field as the basis for a range and calculate the total for a range of records? I am not sure if this is the answer but I am trying to make it work.

DRW :confused:

OK, are you using VB with this project? You say you are using DAO for this...what database connection are you using for Crystal. I would recommend using the Active Data driver (then select the DAO option and create your connection) so you get the best "dynamic" performance in terms of passing recordsets, conditions, etc at run-time.

How are you using your running totals: by Group, by each individual record, etc. I'm trying to get all the facts and see what I can come up with based on that :). Do you have any code you are currently using..can you post it?

When you say current record, do you mean the most recently added record to the table?

Hello again

There are no real performance issues. I have other reports in the project which are fine and this report runs fine as long as I don't try to put in this calculation field.

The project (Vb6) is used for cost estimating. Basically the calculation is cost * qty which is fine, but ocasionally the cost is applied to the running total. e.g. as profit or margin. This calculation does not always occurr as the last line of the report. It can be applied on any line. My field calculation looks like this at the moment.

If {Estimate_ttx.CostApply} = "Item" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer} else

if {Estimate.ttx.CostApply} = "Tree" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/{{Estimate.ttx.QtyPerTree} Else

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/100)*({#TotalCost})


When the CostApply field = "Running Total" the {#TotalCost} is included in the calculation and the {#TotalCost} is the running total of this field.

It is referencing itself and bombing out.

I need to get the value of the total so far, without including the current line. I don't seem to be able to do it with a range of record numbers.

:-\

Yes, a formula can not reference itself. I see your issue. Basically, you need to reference your running total field within the formula of the running total field.

I would suggest creating an unbound field that represents your runningtotal field. As in, create an unbound field (or formula). Let's name it {@numbertotal}

Then, in the formula editor of this new formula write something very simple basically saying whileprintingrecords this formula equals the value of the runningtotal field..such as



Whileprintingrecords;

{#Rtotal} 'set this formula equal to our running total




then in the code of your final IF statement instead of referencing the runningtotal field itself, reference this new formula. Does this make sense. I just tested this...I created a running total and an unbound formula like I mentioned. I put the two next to each other so I could compare: when the running total was 15, the new formula was 15, etc.

I have followed your advice and set up a formula field to assign the running total to a global variable (RT) while printing records.

This seems to work ok but all my values where this variable is being used are printing as zero. I have included RT onto the report so that I can check its value and it prints ok, allthough it seems to be a boolean field, which is a bit confusing.

hmmm..not sure what you mean.

Again, I have a running total field that is counting the number of fields. I have put this in the details section..my test report shows this in the details section

recorddata recorddata recorddata, etc
1

recorddata recorddata recorddata, etc
2

*******************************************
then I have my @unbound formula I made that mirrors this running total, if I put this formula next to the runningtotal field and this occurs, which is correct

recorddata recorddata recorddata, etc
1 1

recorddata recorddata recorddata, etc
2 2

***************************************************

now if I move this @unbound formula to the report footer I get the number 3,718.00. This is the number that is running total ends at when the last record is printed. So I get numbers 1-3,718.00 in the details section and one field in the report footer with the same value. Basically, this technique is working.

Not sure what you mean when you say you are getting a boolean value..can you explain a little more.

OK

My @SETRT formula field looks like this

WhilePrintingRecords
NumberVar RT:
RT={#TotalCost}

Where {#TotalCost} is my true running total.

When I come to a line on the report where the total cost uses RT then the report prints zero. The calculation looks like this.

NumberVar RT;

If {Estimate_ttx.CostApply} = "Item" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer} else

if {Estimate.ttx.CostApply} = "Tree" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/{{Estimate.ttx.QtyPerTree} Else

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/100)*RT


The last If always produces zero result.

I have put the real running total on the report and it does total correctly.

I have also put @SETRT on the report and it prints False for every line.

If I try to format @SETRT it is a boolean field.

We are missing each other somewhere :). OK, on your rpt file make a new formula field, since you have a name, call it @SETRT.

Now this is what you have:
**************************************************
My @SETRT formula field looks like this

WhilePrintingRecords
NumberVar RT:
RT={#TotalCost}
***************************************************
this will always be false because you are trying to equate a numbervar Rwith your runningtotal. In the formula you just created this should be all you need



Whileprintingrecords;

{#RTotal0}



this will equate the field with your running total. this is all you need. Then in the existing IF construct you need:

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/100)*{@SETRT}


{SETRT} is automatically representing your running total

Hello again

It is getting late in the day here and I have been programming for 9 or 10 hours so please allow me a little license with the tired grey matter.

I have code as follows:

@CostQty=

If {Estimate_ttx.CostApply} = "Item" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer} else

if {Estimate.ttx.CostApply} = "Tree" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/{{Estimate.ttx.QtyPerTree} Else

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{SETRT})/100)

@SETRT=

WhilePrintingRecords;
{#TotalCost}

Where

{#TotalCost} is the running total of @CostQty

I think I am missing something. It bombs out, I think it is referencing itself again. :(

OK, I am actually leaving for the day shortly myself, but, we need to work on your Crystal syntax/formula structure

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{SETRT})/100)


OK, Crystal has no idea what ="Running Total" means, unless you have this defined as a stringvar..which you don't.

I think what you mean to say is:



if {Estimate.ttx.CostApply} ={#TotalCost} then 'if runningtotal and field match
(({Estimate_ttx.Amount}*{@SETRT})/100)



make sure you are including any # or @ that in the formulas name, that is how they reference each other. In your code the runningtotal isn't calling itself at all...but in this final If statement you are saying if this field is equal to the string "Running Total" do this..you are not referencing the actual running total field

I am unsure about the field {#RTotal}?

I do not have this field in my code. Do you mean {#TotalCost}

Also

If {Estimate.ttx_CostApply} = "Running =Total"...

The value of the Field {Estimate.ttx_CostApply} is = "Running Total" when I want the % calculation to be applied. e.g.

If {Estimate_ttx.CostApply} = "Item" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer} else

if {Estimate.ttx.CostApply} = "Tree" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/{{Estimate.ttx.QtyPerTree} Else

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{SETRT})/100)

:confused:

OK, now I understand what you are saying.

If the field = "this string" do this calculation

If the field = "running total" (the actual string word running total) then do this calculation.

Gotcha.

I don't see where you are referencing the actual {#totalcount} in this formula. I'll tell you what...can you post this .rpt file? You seem to be getting frusterated....I am leaving soon, but I will look this over when I can. I will repost it after looking it over.

Sorry If I sounded frustrated. No offence intended.

I have attached the .rpt as a .zip

Many thanks for your help. I will keep bashing away at it for a while.

The formula fields CostQty1 and SETRT are not inserted into the report because the report bombs out when viewing the report with these fields inserted.

Thanks again and Happy Eater.

Hello:

OK, well, looking at your report I see no grouping here. You also didn't attach any formulas and I can not see the .ttx data source (that I figured anyway) so it hard to get the full picture. However, I am not sure where you are putting this formula:



@CostQty=

If {Estimate_ttx.CostApply} = "Item" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer} else

if {Estimate.ttx.CostApply} = "Tree" then
({Estimate_ttx.Amount}*{Estimate.ttx.CostPer}/{{Estimate.ttx.QtyPerTree} Else

if {Estimate.ttx.CostApply} = "Running Total" then
(({Estimate_ttx.Amount}*{SETRT})/100)



I think the problem here is how we are defining the formula structure. What is the formula populating, what field? I am assuming you want this formula to populate( or represent) your "Amount field". Your running total field is used to count records on a report or a group. You aren't going to be able to use this code within the running total itself. See, we should use a formula to HOLD the actual results we need. Anyway, what we want is to populate a specific field with this data.

*If a CostApply = "ITEM" Then do this calculation and insert this value, etc.

So, since I don't have your data, I put together several test variations within reports I have. Basically, you need to leave your running total alone. You say you are referencing it within itself, I don't see that.

I created a standard runningtotal field: #RTotal0, this just counts my records
I created a formula field: I called it @finalAmount

*my goal is to populate my field "Amount" with the proper calculations based on a string value passed in the CostApply field or based on what my running total is (two variations)

Now this @finalAmount formula WILL ACT LIKE my actual Amount field. We can suppress the actual Amount field or set the visibility to false. Then, in the code of this formula I wrote this test formula:



Whileprintingrecords;

If {#RTotal0} = 1 'if I have one record, do this
Then "NO PROFIT" 'my Formula returns this string message

Else

If {#RTotal0} > 1 'OK, we have sold some stuff
Then ToText({#RTotal0} * {#RTotal0}) 'do this calculation and display it (use the toText() conversion)



this works how I want it to. If my runningtotal is a certain amount, I do a specific calculation. I suppress my Amount field and this formula acts in its place. So, all the way down my report I see:

"NO PROFIT"
4.00
256.00
"NO PROFIT"
etc.

Now again, I am still no sure what your structure is. If you want to base this formula NOT on your running total but on your CostApply Field, again the same concept. In the @finalAmount formula:



Whileprintingrecords;

If {ado.CostApply} = "myString" Then 'ado is my test datasource
"here is a value"

Else

If {ado.CostApply} = "thisString" Then
totext({#RTotal0} * {#RTotal0}) 'again reference the running total



this also produces the results I need. If "myString is present the formula (again acting as my field) produces "here is a value". If it represents "thisString" I get the proper calculations.

I hope this makes more sense. I am not sure how fluent you are in Crystal so if I lost you somewhere please let me know.