Formual to count a field that cannot be summarised

[CARLEENHOP]

Hi

Using crystal v11.

I have a 3rd grouping where I display data. This shows if an order was received or delivered. Where delivered I have 3 columns to show if ontime, 1-2 days late or later. This is based on the date difference between the order date and the charge date. In the 3 columms I have it displaying a 1 if it is true. E.g out of 10 records I might have 2 showing a 1 in the one time, 6 showing a 1 in the 1-2 days late and 3 showing a one in the later column. I have done this using the suggested syntax from Ian on this website (see below)

whileprintingrecords;

global numbervar datediff;
global numbervar datediffgrp;



if {SORD.TYP} = 2 and {@Date Difference} =0 then
datediff:= datediff + 1 ;

if {SORD.TYP} = 2 and {@Date Difference} = 0 then
datediffgrp:= datediffgrp + 1 ;

As mentiond this is displaying in my 3rd Group Footer.

I need to total these values for my 2nd group footer and my 1st group footer. However I cannot for the life of me work it out.

Please help!

Thanks

 

[lbass]

You need separate variables for each group level, and then you need to add reset formula in the corresponding group headers.

-LB

 

[CARLEENHOP]

Hi, thx for you response. I have tried this but not having any luck. My other two group footers print zero. See code below. I can get it to print a one t against he transaction number record and reset OK. If I dont reset I can see how many records the next group should print as it dispalys 1, 2, 3 etc against the transaction record, however the group prints as zero when there is say 6. I am just displaying this same field in the group, is that right? Does it matter the order of the formuals below. E.g. I have ordered it highest grouping to lowest?

Thx

whileprintingrecords;

global numbervar datediff12;
global numbervar datediff12grpnum;
global numbervar datediff12grpdept;
global numbervar datediff12grpbrand;


if {SORD.TYP} = 2 and {@Date Difference} = -1 or {@Date Difference} = -2 then
datediff12:= datediff12 + 1 ;

if {SORD.TYP} = 2 and {@Date Difference}= -1 or {@Date Difference} = -2 then
datediff12grpbrand:= datediff12grpbrand + 1 ;

if {SORD.TYP} = 2 and {@Date Difference}= -1 or {@Date Difference} = -2 then
datediff12grpdept:= datediff12grpdept + 1 ;

if {SORD.TYP} = 2 and {@Date Difference}= -1 or {@Date Difference} = -2 then
datediff12grpnum:= datediff12grpnum + 1 ;

 

[CARLEENHOP]

I have managed to get one step further but still not sure how I got there!

I have 3 groups so created the following variables (3) is that right?


whileprintingrecords;

global numbervar datediff;
global numbervar datediffgrpnum;
global numbervar datediffgrpnumber;


if {SORD.TYP} = 2 and {@Date Difference} < -2 then
datediff:= datediff + 1 ;

if {SORD.TYP} = 2 and {@Date Difference} < -2 then
datediffgrpnum:= datediffgrpnum + 1 ;

if {SORD.TYP} = 2 and {@Date Difference} < -2 then
datediffgrpnumber:= datediffgrpnumber + 1 ;

I have it now printing an amount for group 2 (however it is one more than what it should), how can I fix this?

It is resetting for this group OK as I have this formula in the group 2 header (where I want to reset) thus being as follows

whileprintingrecords;


global numbervar datediffgrpnumber:=0;

What I cannot get it to do is print the correct amount for group 1. Its continueing from the last group 2 total (even though I have the reset after each group, its like its not resetting from the last group)?

I have tried adding another variable so I have four and I have tried resetting in group header 1 but I am not clear on what variable I should be resetting either.

I am so close, please help!

Thanks

 

[lbass]

You need to place the accumulation formula in the detail section, and then for each group, you need a reset formula (just like you did for group #2, but with the variable name for that group) in the group header, and a display formula in the group footer:

//{@group#2footerdisplay}:
whileprintingrecords;
numbervar datediffgrpnumber;

You must NOT add the accumulation formula to the group footer or it will accumulate once more.

Your reset formulas should look like this:

//{@group#1reset}:
whileprintingrecords;
numbervar grp1no;
if not inrepeatedgroupheader then
grp1no := 0;

I didn't which variable you were using for group #1, so made up grp1no.

Note also that your formulas like the below wouldn't evaluate correctly because you didn't set off the "or" clauses with parens:

if {SORD.TYP} = 2 and
(
{@Date Difference} = -1 or
{@Date Difference} = -2
) then
datediff12:= datediff12 + 1;

-LB

 

[CARLEENHOP]

This is awesome I managed to get all 3 fields working correctly as the explanation about each section really helped.

I am now trying to do a similar thing, but in this instance I am using a shared variable (field from a subreport). I have noted that I can only use this variable after the section the sub report is in, so created a detailed section a for the subreport and the details are all in detail b.

It returns what I need it do when I am showing detail a with subreport, however when I suppress this section my value is false.

1. Variable from sub report is:

Shared NumberVar
OrderQty := {SA_DELIVERY.SOZD_COMPL_QTY_1}

2. Formula in main report is:

Shared NumberVar

OrderQty:=OrderQty

3. Formula for detail section b is:

whileprintingrecords;

global numbervar OrderQty;

if {@Completed Qty} <> 0

then 1

else 0

I then want to use this value later to work out the % that have 1 out of the total, so I had planned to follow a similar set up with the group display footers and header resests with my count.

However I cant get the intial returned value to be correct when the subreport is suppresed.

Can you help?

Thanks

 

[lbass]

whileprintingrecords;
[red]shared[/red] numbervar OrderQty;
if {@Completed Qty} <> 0
then 1
else 0

The type of variable must be referenced in the same way in both sub and main report.

Note also that you cannot suppress a subreport OR the section it is in if you want to pass a shared variable. You can instead suppress all sections WITHIN the sub, format the subreport->subreport tab->check "suppress blank subreport", remove the borders from the sub, and then in the main report->section expert->detail_a->check "suppress blank section", and detail_a will disappear, but the variable will still be passed.

-LB

 

[CARLEENHOP]

Hi, thanks again, it all helped and I am one step closer. I couldnt do what you said with the subreport as its never blank, so never suppresses? What I did was make it really small and made sure it coudnt grow and removed borders and made font white. Prob not the best but it worked!

I have one other problem... I now have how many orders delivered, ordered, in full and ontime etc by group but what I want to do is work out the % in full which is a calc based on infull/delievered. Seems straight forward but I cant get it to work (even with the same structure as the others). orders delivered is a running total so I only have the value of it at the group level, where as the way the others work I need the value in the detail where the initial formula is placed?

Any help is very much appreciated.

 

[CARLEENHOP]

Dont worry I worked it out by creating the formula in the display! Yay maybe some of it is sinking in.