Running total not capturing last record in group

[quinn23]

Hello all
I'm currently running Crystal Reports XIr2 against an oracle database. My report contains two groups, Group 1=type, Group 2 = study

I have a running total in the type group that needs to sum the latest study for type.

example
Type1
studyA =1
studyA =2
studyB =4
StudyC =10
StudyC =20
I'm trying to sum StudyA=2
StudyB=4
StudyC=20
so that the number of Studies by Type1 = 26.

I've come close in capturing this using the Next function in the formula builder of the running total, however it seems to omit the last study. This Running total is placed in the GroupFooter Type

next({Study})<> {Study}

Using the above it sums correctly by checking the next study however omits the last record. I've searched for a further condition onlastrecord however this does not give me the last record in the group.

Any suggestions?
Thanks in advance
Jeff

 

[dgillz]

This is because the last record has no next value. Change your formula to:

If OnLastRecord then <insert logic here> else <insert logic here>

This does beg the question of why you done build a simple running total that resets on change of group? If you do this there is no need for a formula to evaluate.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[quinn23]

Thanks dgillz for your quick response. I'm still a little confused.
I've tried to use the OnLastRecord however it doesn't work within a group, it only checks for the last record of the report. I had thought since I needed to capture the last study by type and then sum the study by type, the easiest way would be to create a running total. I show this in the GroupFooter Type
because there are many types.
Is there a way to check the last record in a group?
Thanks again for your reply

 

[MrBillSC]

If you want to capture the last record of the second group try "Use a formula" in the evaluate section:

// Check for Type changing from Null to a value.
(IsNull({Table.Type}) and
Next({Table.Type}) > "") or

// Check for Type changing values.
{Table.Type} <> Next({Table.Type}) or

// Check for study changing from Null to a value.
(IsNull({Table.Study}) and
Next({Table.Study}) > "") or

// Check for study changing values.
{Table.Study} <> Next({Study})

MrBill

 

[lbass]

You don't have to check the last record in the group. Your formula would only fail for the last record in the report. Try an evaluation formula like this:

(
onlastrecord or
{table.type} <> next({table.type})
)

-LB

 

[quinn23]

Thanks so much for everyone's responses, unfortunately I'm still confused. I'm not sure how the following suggestion could work in the formula builder of the running total.
(
onlastrecord or
{table.type} <> next({table.type})
)


should this be added to what I currently have in the formula?

I tried this but it still omits the final study in the type group

next({study})<> {study} or
(
onlastrecord or
{type} <> next({type})
)

This formula is contained in the Type Groupfooter of the runningtotal formula builder. I appreciate everyone's help and I'm probably missing something very basic

 

[quinn23]

If I apply the formula below in the reset portion of the formula builder, I only capture the last study.
(
onlastrecord or
{Type} <> next({Type})
)

I'm trying to show
Type1 in the Group footer as 26
studyA =1
studyA =2
studyB =4
StudyC =10
StudyC =20

Type2 in Group footer as 125
studyA =100
studyA= 50
studyB= 75

 

[lbass]

Sorry, try this:

(
onlastrecord or
{table.type} <> next({table.type})
) or
(
{table.type} = next({table.type}) and
{table.study} <> next({table.study})
)

-LB

 

[quinn23]

Thanks LB but I get the same result.
I applied the following in the formula builder
(
onlastrecord or
{table.type} <> next({table.type})
) or
(
{table.type} = next({table.type}) and
{table.study} <> next({table.study})
)

Is an additional formula necessary on the reset? Currently I have the reset on group Type should I change that?
Thanks for your help with this

 

[dgillz]

If you are resetting on change of group you need no formula at all.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Please show sample data (with multiple rows) and show the results you are getting with my latest suggestion. Be sure to identify report sections.

-LB

 

[quinn23]

Hi LBass
Listed below is the formula I used.

I applied the following in the running total formula builder
in the Type groupfooter
(
onlastrecord or
{table.type} <> next({table.type})
) or
(
{table.type} = next({table.type}) and
{table.study} <> next({table.study})
)

Sample Data:

I would expect to get 26 in the group footer but get a total of 6 instead, the last study in the group is StudyC and omits the 20. StudyC with the value of 20 is also the last record in the group
Type1
studyA =1
studyA =2
studyB =4
StudyC =10
StudyC =20
The running total for Type1 should calculate 2+4+20 = 26

Type2
studyA =1
studyA =30
studyB =10
StudyB =10
StudyC =80
The running total for Type2 should calculate 30+10+80 = 120
Thanks again for your help

 

[lbass]

How are you creating the summary in the study group? Is it an inserted summary? If so, of what? Where is it located--what section? Please explain what you are summarizing in the running total and what the summary is.

You didn't identify your group sections in your sample, nor did you add the running total so we could watch it accumulate. I think that would help.

-LB

 

[quinn23]

Hi LBass
The report has two groups

The first group is Type
The second group is Study

All summaries are displayed in the groupfooter of Group1_Type

Detail Section: contains the Study. I do an insert running total (sum)on Study, and apply the formula logic in the evaluate portion. The formula never gets reset.

Last I drag the running total into the Group1 Footer(Type)

Sample Data:
When the report is run this is how the data looks

GroupFooter1(Type): Total 50 studies
When I drilldown into the detail
detail section Study: A = 10
detail section Study: B = 20
detail section Study: B = 35
detail section Study: B = 40
detail section Study: C = 100
detail section Study: C = 150

This total should equal 200 (10 + 40 + 150)

 

[lbass]

What I am asking is how you are creating the numbers you are showing in the study group.

-LB

 

[quinn23]

I don't think I even need a study group. I'm doing an insert Running total of the Study field from the detail section and then moving that to the Group1_footer(Type)

 

[lbass]

You aren't using field names and I'm finding that confusing. Please show the actual fields you are using, for example, is there a field that distinguishes the studies? You say you are grouping on "study" and then you say you are summing "Study"--what field are you summing?

If you don't group on some study field, you would have to sort by it in order to evaluate the last record per study--last based on date?

If you are creating a running total and then placing it in the group footer type section with no reset on change of the type group, you realize that the sum will just keep accumulating right?

-LB

 

[quinn23]

Hi Lbass
There are two fields in the report I'm using

table1.type
table1.study

The report contains two groups one is on table1.type
the other is table1.study

From the detail section I create a running total(Sum) on study, and now reset on groupType change.
I then drag the running total into the Group1TypeFooter


Group1Type table1.Type
Group2Study table.Study

Detail Section:
Type1 StudyA value=10
Type1 StudyA value=20
Type1 StudyB value=5
Type1 StudyC value=50
Type1 StudyC value=100
The above represents three studies by the group Type

I'm trying to show in the GroupFooter of Type a total of
125
20+5+100 =125

 

[lbass]

I absolutely understand what you are trying to do, but you are not providing any new information here, and I cannot see why the current formula would not work. You also have not clarified what it is you are summing. Please place the running total in the detail section and show a sample with your values including the running total next to it to show how it is accumulating.

-LB

 

[quinn23]

The formula is now working as expected. I believe the report was corrupt. I simply started over and sumed study as a running total and placed in the groupfooter of the Type and it worked.

I appreciate everyone's suggestions, and help.
Thanks again