How Do I insert a Sum into this formula?

[mgkSHA]

Hello:

I have been struggling with this forever and I need one more step to complete this. I have a field on a report that is a string value and I need to add up the values into a sum within a running total field.

I have written the following formulas to convert the string into an integer and then back to the 00:00:00 format within the group footer. What I can't do is get the values to add up within the running total field. How can I do this, what steps did I miss? Where would this formula go? Any advice would be great, because I am ready to give up at this point. Thanks

formula one: in details section of report - - converts the string so I can add up to a sum.

WhilePrintingRecords;
numberVar result;

result := tonumber(left({ado.correctedduration},2))* 3600
+ tonumber(mid({ado.correctedduration},4,2)) * 60
+ tonumber(right({ado.correctedduration},2));

result;


formula two: in group footer of report, returns the value of the RTotal1 Running total field back to 00:00:00

whileprintingrecords;
numberVar dur:=Val({#RTotal1});
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs:=Truncate(Truncate(dur/60)/60);
min:=Remainder(Truncate(dur/60),60);
sec:=Remainder(dur,60);

hhmmss:=totext(hrs,"0&quot + ":" + totext(min, "00&quot + ":" + totext(sec, "00"

hhmmss

 

[MattReed]

I noticed something in your second formula that is telling you what your problem is. If you have to use...

Val{#RTotal1}

...to store RTotal1 in a numberVar, then that means that RTotal1 returns a string. You cannot sum on a String Field. I have a feeling that Crystal is seeing your + signs in the first equation as concatenation operators. Try storing each separate result in its own variable, then adding the variables.

WhilePrintingRecords;
numberVar result;
numberVar seconds;
numberVar minutesInSecond;
numberVar hoursInSecond;

hours := tonumber(left({ado.correctedduration},2))* 3600
minutes := tonumber(mid({ado.correctedduration},4,2)) * 60
seconds := tonumber(right({ado.correctedduration},2));
result := hours + minutes + seconds
result;

if this doesn't work, try manually converting RTotal1 to a number in another formula. Place this formula in the details band, and sum on that formula. Just remember that you will have to reformat the running total field manually with another formula.

Hope that helps

Matt Reed

 

[mgkSHA]

Matt:

My initial formula to parse the string into an int works, but I used your version and that works too. My problem remains the running total field. I can't get a running sum for the life of me. This is the current code I tried, I get the syntax error "Can not create the running total field/summary field"

Can you tell me how to get this running total field to become an integer? Will this solve my problem? You mentioned this in the last post. Please help if you can, I am very frusterated. I really need this project finished and this is the last report. Why won't my running total field get a sum of the parsed integers?

whileprintingrecords;


numberVar dur:=Sum({@@conversionToSeconds2},{#RTotal1});
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs:=Truncate(Truncate(dur/60)/60);
min:=Remainder(Truncate(dur/60),60);
sec:=Remainder(dur,60);

hhmmss:=totext(hrs,"0&quot + ":" + totext(min, "00&quot + ":" + totext(sec, "00"




hhmmss

 

[MattReed]

OK, I got something to work.
Create a New Formula, while keeping your original.


Global numberVar RunningTotal;

RunningTotal := RunningTotal + {RTotal1};
RunningTotal;


Insert this formula in he detail band, but also insert it into the GroupFooter Band where you want the Total Shown. Now Create another formula to clear the variable and place it in the GroupHeader

Global numberVar RunningTotal;

RunningTotal := 0;
RunningTotal;


Do not show this formula. It must be placed in the group header of the group you are totaling, or else the variable will not reset on the change of that group.

Hope this works for you.

Matt Reed

 

[mgkSHA]

Matt:

Thanks but this formula will not save, I get the error a boolean, string, or number is expected here:

Global numberVar RunningTotal;

RunningTotal := RunningTotal + {#RTotal1};
RunningTotal;

I don't know why, but Crystal won't accept that formula. I am ready to quit anyway, but I appreciate your help.

 

[MattReed]

Try This

Global numberVar RunningTotal;

RunningTotal := RunningTotal + tonumber{#RTotal1};
RunningTotal;

Dont give up!

Matt Reed

 

[mgkSHA]

Matt:

Now I am getting the error on the CRVIEWER load: "The string is non-numeric. I will attach what I have. I actually have two versions going, this one, and another which is using another formula as the running total field - - another programmer is also trying to help me. Both of you have been very helpful, and I'm hoping somehow I can get this to work. Again, I have been stuck with this since Friday and EVERYTHING I have tried has not worked. Here is the current rendition of this code I am using.

In the Group footer as you suggested: Formnula One
Global numberVar RunningTotal;

RunningTotal := 0;
RunningTotal;

(P.S. -how do you "hide" a formula?&quot

In the details section as you suggested: Formula Two

WhilePrintingRecords;
numberVar result;

result := tonumber(left({ado.correctedduration},2))* 3600
+ tonumber(mid({ado.correctedduration},4,2)) * 60
+ tonumber(right({ado.correctedduration},2));

result;

Also in the details section: Formula Three

Global numberVar RunningTotal;

RunningTotal := RunningTotal + tonumber({#RTotal1});
RunningTotal;


In the Group footer: Formula Four

whileprintingrecords;

numberVar dur:=Val({#RTotal1});
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs:=Truncate(Truncate(dur/60)/60);
min:=Remainder(Truncate(dur/60),60);
sec:=Remainder(dur,60);

hhmmss:=totext(hrs,"0&quot + ":" + totext(min, "00&quot + ":" + totext(sec, "00"

hhmmss

Also in the Group Footer: Formula Five

Global numberVar RunningTotal;

RunningTotal := RunningTotal + tonumber({#RTotal1});
RunningTotal;



Again, now the report won't load. Any ideas..the nightmare continues

 

[mgkSHA]

Matt:

That first formula is in the Group header not footer, I mis-typed.

 

[MattReed]

Remove Everything and start over with this code.
(You might want to save what you have as something else so you don't lose what you have done.)


Group Header:

Insert one formula for Initialization of the global variable. This is done so you start the running total for each group.

Global numberVar RunningTotal;
RunningTotal := 0;
RunningTotal;

Details:


Insert this formula as well to convert your string to a number.

numberVar AsSeconds;

AsSeconds := tonumber(left({ado.correctedduration},2))* 3600
+ tonumber(mid({ado.correctedduration},4,2)) * 60
+ tonumber(right({ado.correctedduration},2));

AsSeconds;


Insert One Formula to add the current value to the running total.

Global numberVar RunningTotal;
RunningTotal := RunningTotal + {NameOf};
RunningTotal;


Group Footer:

Insert a formula that returns the RunningTotal.

Global numberVar RunningTotal;
RunningTotal;



If all you have are these formulas, it should work. What kind of data source are you using?


This is really bugging me. It should work! It works on my computer, using an Access Database. I have a table called Duration.

Duration
ID - Unique Identifier
Person - Something To Group On
Duration - Duration as Text formatted "hh:mm:ss"

I connected to this database through an ODBC Connection.

It works fine here. There could be somehting up with your data source. Are you doing any joining in this Report?


Hope That Helps

Matt Reed

 

[mgkSHA]

Matt:

My main responsibilities here at work is VB programming: I just recently got into Crystal. I am using ADO as the recordsource, and I have never had a problem before, I have had some complex reports before..this is the first one to ever give me fits. I will try this and let you know what happens.

Martin

 

[mgkSHA]

Matt:

Two things:

You say to hide the first formula, how do you do that?

Secondly..again, Crystal will not accept this syntax:

Global numberVar RunningTotal;
RunningTotal:=RunningTotal + {#RTotal1} - err: a boolean, string, number is expected here.

I changed it to Val(#RTotal1) but again, this is a string so it only returns back a zero..I can't use the Val function here and if I say tonumber, I'll get that "the string is non-numeric error"

As usual, I can parse the details section into integers I just can't get the running total.

The other programmer who is trying to help me said it is impossible to get the running total field to do this sum for me. So instead we are making a formula that acts as a running total field. Right now, I still can't get that to do a total sum either, hoping he gets back to me soon.

Again, I am ready to quit...I have worked with many different programs and languages..Crystal by far is the most frusterating and difficult to work with.

My datasource isn't the problem, I can do everything else besides this utility. I am even passing dynamic recordsets and parameters via code (in the CRVIEWER, so I can use VB for that, no problems).

I can understand from a programming standpoint why RunningTotal:= RunningTotal + {#RTotal1} wouldn't work. We have declared a number variant that is adding a number to a blank string field. If it works for you, maybe the field you are counting is a number? I don't know, I do not have enough Crystal experience to know a way around this.

If you have any other ideas please let me know. Again, at this point, I am loosing the drive to work on this anymore. Thanks again.

Martin

 

[MattReed]

Right Click on the formula when it is placed on the report and select "format field". Check Suppress. This should hide the results of the form, and still allow you to make totals off of it.

If you click the "X+2 button with the pencil on it" , you can enter a boolean formula that will suppress if true, not suppress if false. You can do this for any property with the formula button assiciated with it.

Sorry That I couldn't be more help.

Matt Reed