Can you add two running totals together in a group footer?

[JanVolleyM]

I'm using Crystal X. I have a subreport that contains two groups. In group footer 2, I have created two running total fields. I want to display, however, the sum of these two running total fields in group footer 2. Is this possible? If so, how?

Here are how my running total fields are defined:

RT Field Type Evaluate Reset
----------- ---- -------- -----
RTAdmin Sum Use formula: Never
{Command.lessonTitle} = "Admin"
RTNotAdmin Maximum Use formula: Never
{Command.lessonTitle} <> "Admin"


I want to display {#RTAdmin} + {#RTNotAdmin} in group footer 2. I tried creating a formula to do this, but it wouldn't allow me to. Is there a way to do this?

Jan

 

[kray4660]

You have a sum and a Max, not sure how you would combine those.

Lots of luck.

 

[JanVolleyM]

Are you saying this is not possible?

I want to get the sum of all quantities where the lesson title <> "Admin" and the max of all quantities where the lesson title = "Admin" and then add those two values together and display that total in the group footer. I want to do this by group.

 

[JanVolleyM]

I don't have to use running totals, I can use all formulas if that's how I can get it to work.

 

[kray4660]

Not sure. I think it might be possible through creating a running total through a formula and using that value plus your 'Max' running total. I have never tried before, it would take some trial and error.

 

[JanVolleyM]

If I had a Max and a Max instead of a Sum and a Max, would I then be able to combine those?

 

[kray4660]

I just did a test with a RT of a sum and a RT of a max in a formula and it did it without an issue. Does it give an error message?

 

[JanVolleyM]

Could you send me the code for your formula please? Maybe I'm doing something stupid since I haven't been using Crystal for very long. My running totals are reset on change of group (my original post has changed) and I want to put the value of the formula in the group footer.

 

[lbass]

You absolutely can add running totals. However, if you want these per group#2, then the resets should be on Group#2. Create the formula in the field explorer->formula->new:

{#RTAdmin}+{#RTNotAdmin}

-LB

 

[kray4660]

What I saying before is that I had never done a sum and max in a formula before (I never can trust CR to do what I think should be easy). I tried it and it works. The only question that remains is what is the data type that JanVolleyM is using in the RT's as long as they are numbers or currency, then there should not be an issue (I used currency for my test).

 

[JanVolleyM]

When I create a formula with the following:

{#RTAdmin}+{#RTNotAdmin}

I get the following error: "This field cannot be used because it must be evaluated later."

Maybe I'm not creating the formula correctly? I selected 'Group Selection' and put the above code there.

I created a running total called RTAdmin which sums the Command.quantity field, it evaluates using a formula: UpperCase({Command.lessonTitle}) = "COURSE ADMIN" and it resets on change of group#2. I created a running total called RTNotAdmin which gets the max of the Command.quantity field, it evaluates using a formula: UpperCase({Command.lessonTitle}) <> "COURSE ADMIN" and it resets on change of group#2.

kray4660, what exactly did you try that worked? My datatypes are all integers so that's not an issue.

 

[JanVolleyM]

Ok, I apparently don't know how to create a formula. I re-created my formula and I was able to create it without error now. For some reason before, my running total vars were not showing up in the formula workshop window to select. So, I was able to create the formula TotalQty as:

{#RTAdmin}+{#RTNotAdmin}

but in my output, most of the TotalQty values are blank. My quantity field that I am doing the running total on is a NOT NULL column in the database, so there is always a value there. Why is my output of the TotalQty blank?

 

[JanVolleyM]

Looks like the TotalQty values only display when there are records that meet the Evaluate formula in both running totals.

 

[JanVolleyM]

Ok, I got this to work. I had to create the funcation as follows to account for nulls:

(IF ISNULL({#RTNotAdmin}) THEN 0 ELSE {#RTNotAdmin})
+ (IF ISNULL({#RTAdmin}) THEN 0 ELSE {#RTAdmin})

Everything works now.

Thanks.