This field cannot be summarized 1

[ABetha]

My formula is called {@DuplicateRep 2}

if not onfirstrecord and {vSerialNo.SerialNum} = previousvalue ({vSerialNo.SerialNum}) then
formula= 1
else
formula = 0
end if

I am trying to sum my formula but it says that this field cannot be summarized. Any help would be appreciated

 

[lbass]

You can use a variable to sum this. Using Crystal syntax, you would set it up like this:

whileprintingrecords;
numbervar sumcnt;
if not onfirstrecord and
{vSerialNo.SerialNum} = previous({vSerialNo.SerialNum}) then
sumcnt := sumcnt + 1;

In the report footer, use the following to display the result:

whileprintingrecords;
numbervar sumcnt;

If you want the count at a group level, move the display formula to the group footer and add a reset formula to the group header:

whileprintingrecords;
numbervar sumcnt;
if not inrepeatedgroupheader then
sumcnt := 0;

-LB

 

[ABetha]

LBass thanks

Heres's my next issue I have two groups one for country and one for customer number. I am attempting to view the sum in the group header and not the footer is there a way to display this?

 

[lbass]

The display formula ONLY works in the footer, so the only way you could get it in the group header would be if you saved the report as a subreport that you placed in the group header.

For two different groups, you need separately named variables, with the reset and display formulas in the corresponding group header/footer. You can accumulate both in the same accumulation formula if you wish though.

-LB

 

[ABetha]

Ok LBass I have never created a subreport before. So I created the subreport and now I am trying to get the numbers that I have in the subreport in the main report but they all come in as zero any reason why?

 

[lbass]

Why not just suppress all parts of the subreport except the footer where the display formula is? You don't really need to pass the results to the main report unless you need to work with the summaries further.

I'm assuming you placed the subreport in the group header and that you linked the sub to the main report on the group field. If you need to pass a shared variable, then change the previous formulas to say: shared numbervar sumcnt. In the main report you would have to insert a GH_b section where you would reference the shared variable, assuming the sub is in GH_a.

-LB

 

[ABetha]

Ok
LBass this is what I did instead of doing a subreport to have the summaries in the group header I left them in the footer and moved all of the summaries to the footer as well.

But also I was trying to now summarize the summarized field and place it in the Country group header.
I want it to reset on country I tried to use the same formulas and change the variable names but that does not work.

 

[lbass]

But you can't use variables and still place the result in a group header. If you meant you are trying to use variables to get results in the Country group footer, then please show the content of the formulas you used.

-LB

 

[ABetha]

Here' the formula I use to get the total amount for a total of credits per location.
{@credit}
formula = count ({@visit}, {vSerialNo.CustNo})- Sum ({@visit}, {vSerialNo.CustNo})- Sum ({@Hours }, {vSerialNo.CustNo}) - Sum ({@outofWarranty}, {vSerialNo.CustNo}) - ({@DisplayResult})

Below are the content of the formulas that i am using.
{@visit}
if ({vSerialNo.ReportDate}-{vSerialNo.Date} >= 180) then
formula = 1
else
formula = 0
End if

{@Hours}
if val({vSerialNperation})>= 1 then
formula = 0
else
formula = 1
end if

{@outofwarranty}
if {@warrend} < {vSerialNo.Date} then
formula = 1
else
formula = 0
end if

{@DisplayResult} is the formulas that you help me with earlier.

I meant the Country group footer.

 

[ABetha]

LBass I a question going back to my initial post as well. The count works but if there are say three serial numbers and two of them are considered duplicates it only counts one I would think that it would count every record.

 

[lbass]

You didn't implement my suggestion at all. Please try it. Change to Crystal syntax in the formula editor and then use my formulas as they are.

-LB

 

[ABetha]

LB I did implement your formula it works for the first question that I asked with one hiccup. If I have a triplicate or quadrupled entry it is only summing the first duplicated entry.

My next question was trying to summerize another formula field called {@credit}that I have in the same report. I tried to use the same three formula strategy. But the numbers are coming out wrong.
I was trying to explain the content of the formulas that I was using to get the {@credit} to see if you could spot what I was doing wrong.

 

[lbass]

You seem to be talking about two different things. Is the issue getting the correct result at a different group level? If so, I was asking you to show me how you implemented the three formula solution for that. Or is this a separate issue? What is your group structure?

-LB

 

[ABetha]

1st issue
I used the three formulas
GH1- Country
GH2-CustNo
{@ResetFormula}
WhilePrintingRecords;
numbervar runningtotal;
if not inrepeatedgroupheader then
runningtotal := 0

Summary Function
if not onfirstrecord and {vSerialNo.SerialNum} = previous({vSerialNo.SerialNum})and {vSerialNo.date} = previous({vSerialNo.date}) then
runningtotal :=runningtotal + 1;
whileprintingrecords;
numbervar runningtotal;


GF2-CustNo
{@DisplayResult}
WhilePrintingRecords;
numbervar runningtotal;

For every custNo the calculation is correct except for 3. For these particular custNo's the record is tripled not double and the formula is not counting the second and third record only the first one.


My second issue is I am trying to calculate a credit field I have used the same three formula structure but I am not getting the correct sum. Here is what I have

Display formula
WhilePrintingRecords;
numbervar Sumcredit;

Reset formula
WhilePrintingRecords;
numbervar runningtotal;
if not inrepeatedgroupheader then
runningtotal := 0

the summary function
whileprintingrecords;
numbervar Sumcredit;
if {@creditCalc} = 1 then
Sumcredit :=Sumcredit +1 ;

This is the @creditCalc contents
if {@outofWarranty} = 0 and {@DuplicateRep 2} = 0 and {@Hours } = 0 and {@visit} = 0 then
1
else
0

 

[lbass]

Please rewrite this to:

//Summary Function
whileprintingrecords;
numbervar runningtotal;
if not onfirstrecord and
{vSerialNo.SerialNum} = previous({vSerialNo.SerialNum})and
{vSerialNo.date} = previous({vSerialNo.date}) then
runningtotal :=runningtotal + 1;

This would count duplicate records in the group when the serial number and the date are the same. Note that this will count records that you might have suppressed also, unless you explicitly eliminate them in the formula. I'm really not clear on the purpose of this calculation.

I don't know where you have placed the formulas re: the second issue, but assuming they are placed correctly, the issue would have to be in your nested formulas--which are very confusing. Again, I can't really troubleshoot this without understanding your intent or seeing how the data is displayed. I'm wondering again if you might have suppressed records that you are hoping to exclude.

-LB

 

[ABetha]

LBass first issue resolved.

Second issue
This is how the data is displayed
Summary Report
Country- CANADA VisitClaim DupRpt InvalidHour Credit

Cust No. :VIC004 14 90 2 0 85

Cust No. :VIC004 24 3 0 0 3

Cust No. :VIC004 3 65 0 2 63

Subtotal: 158 2 2 151


Details Report
Serial# warrEnd Dup Past180days InvalidHours outWarr Cre
N5061848 01/29/2013 0 0 0 0 1

N5105508 08/30/2014 0 1 0 0 0


I was trying to calculate the credits based on the formula listed earlier
if dup = 0 and past180days = 0 and invalidhours = and outwarr = 0
then cred = 1
else
cred = 0
I then want to sum this by custNo and then by country and then a grandtotal for company