Trouble in Counting Formula field 2

[bnands]

I am new to Crystal report. I am having trouble in counting the formula field.
For example

Group 1 Office
Group 2 SSN
________________________________________________________________
SSN | Name | startdate | Enddate | workHrs |meetinghrs

123455 XXXX Sdate1 EndDate1 10hrs Yes

012345 YYYY Sdate2 Endate2 14hrs No
_____________________________________________________________
group2 Footer
Total number of Hrs met = XXX
Total Numberof Hrs not Met = XXX
____________________________________________________________


I am calculating the Meeting Hrs using a formula
and displaying whether its Yes or No according to the condition.

My problem is i want to count how many "Yes" in Group 1 and
Number of "No" in Group 1


Since i want to count a formula its not allowing me to do Summarize. Is there any way to count them ??

Thanks for your help !

 

[MCuthill]

bnands,

I would think a runningtotal your best approach - you will need 3x variables to acheive this result.

{@Variable_Reset-MeetingCounter} << I would think this goes in your SSN group header (whichever group is where you would want to reset your counter)

WhilePrintingRecords;
Shared NumberVar MeetingCounter:=0;

{@Variable_Set-MeetingCounter} <<Goes in details

WhilePrintingRecords;
IF {@MeetingHours}="Yes" THEN MeetingCounter:=MeetingCounter+1;

*Note: if this doesn't work, replace the reference to {@MeetingHours} with the same condition that triggers {@MeetingHours} to be "Yes"

Lastly to display the results, place this in your Group Footer:
{@Variable_Display-MeetingCounter}

WhilePrintingRecords;
Shared NumberVar MeetingCounter;

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Please show the content of your formula.

-LB

 

[bnands]

Hi MCuthill
thanks for the reply. when i used the

WhilePrintingRecords;
IF {@MeetingHours}="Yes" THEN MeetingCounter:=MeetingCounter+1;

in the detail section it is giving me an error
"A number,Currency Amount,Boolean,date,time, date-time, or string is expected here."

Do i need to declare the variable as Global NumberVar ??

 

[bnands]

lbass

Formula Name : {@MeetingHours}
Formula used :

//calculating the WorkHrs for a Month so multiplying with number of weeks

Local NumberVar WorkHrs := {table.reqhrs}*{@numberOfweeks};

If not isNull({#RTotal1}) and ({#RTotal1} >= WorkHrs )then
'Y'
else
'N'

This is the Formula i used.
I am calculating the RunningTotal for a field and if it is greater than or Equal to the WorkHrs(For month) then i am meeting my Hours so i have to put YES or else its No.
then i have count how many "yes" i am getting for Parti
cular Office..

Thanks for your help Lbass !

 

[lbass]

Please explain the exact set up of the running total and show the content of {@numberofweeks}.

-LB

 

[bnands]

Lbass,
I am having 2 fields of Hrs
one is Actual Hrs and another is Required Works hrs
Calculating the Actual hrs using Running total
and Required Workshrs is calculated by
{table.reqhrs}*{@numberOfweeks};

then i am comparing both the hrs .. if it meets the condition
then i have to say Yes or else its NO..


Running total information:

I am counting the weekly hrs for a particular person

Type of Summary : SUM
Evaluate : used date range(from First friday to last friday of month )
Reset : on Change of Group SSN

Our database is having Weekend hrs(workHRs) and weekend date (all fridays)
I have to calculate the weekend hrs for that month(weekendDate has to be in that particular month )

Finding Number of Week {@numberOfweeks}:

DateDiff ('ww',{?StartDate}-1,{?EndDate},6 )

I want to find how many fridays in a month.So i used the above formula. and Muliplying with the ReqworkHrs..


This is a Federal monthly report so i am multiplying the reqWorksHrs by Number of weeks to get the monthly ReqHrs.

I hope i not confusing you too much.

This report has too much calculation .. that's how the user's want.


Thanks for the Help !!

 

[lbass]

You could have used conditional formulas instead of running totals, but you would still need to use variables as Mike suggested, so you might as well just proceed that way. Create formulas like this:

//{@reset} in the local office group header:
whileprintingrecords;
numbervar y;
numbervar n;
if not inrepeatedgroupheader then(
y := 0;
n :=0
);

//{@accum} in the ssn group footer:
whileprintingrecords;
numbervar y;
numbervar n;
if {@MeetingHours} = "Y" then
y := y + 1 else
n := n + 1;

//{@yes} to be placed in the office group footer:
whileprintingrecords;
numbervar y;

//{@no} to be placed in the office group footer:
whileprintingrecords;
numbervar n;

-LB

 

[bnands]

Lbass

Thank You Soo much .. It worked for me ..
I am able to get the right count now

You are a Genius !!!

 

[MCuthill]

Thanks LBass!

My eyes totally missed reading about counting the "No"s as well.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bnands]

Having Another problem ..Can you pls help ??.. this is my layout
I am doing a Drilldown on office level !

GroupHeader#1a - Office

OfficeName | TotalNumberofDCN | TotalMeetHrs | totalNonMeetHrs
__________________________________________________________________
Officename 1 | 12 | [COLOR=red ]10[/color] (Yes) | [COLOR=red ]8[/color] (No)


GroupHeader#1b - Office
__________________________________________________________
SSN | Name | startdate | Enddate | workHrs |meetinghrs
Group 2 - DCN
Detail section
123455 XXXX Sdate1 EndDate1 10hrs Yes
____________________________________________________________
Group2 Footer
___________________________________________
GroupFooter#1 - Office

Total Number of Yes = 8
Total Number of No = 4


I am not able to display the total which i am geting in the GroupFooter#1 to the GroupHeader#1a ??

I am getting different count on the header as i mention in red color.. why is that ??

Can you pls Explain pls !!

 

[lbass]

The results are being collected sequentially, so they are only correct in the group footer #1--but even with drilldown, the user can drilldown on group header #1 and see the results in the GF#1.

Where did you put the reset formula? It must be in GH#1.

-LB

 

[bnands]

Yes i added the reset formula in GH#1 only ..

we are having too many office's so it has to like this

OfficeName | TotalNumberofDCN | TotalMeetHrs | totalNonMeetHrs
__________________________________________________________________
Officename 1 | 12 | 10 (Yes) | 8 (No)
Officename 2 | 14 | 10 (Yes) | 8 (No)
Officename 3 | 18 | 15 (Yes) | 4 (No)
Officename 4 | 20 | 12 (Yes) | 7 (No)

So i need to bring the total count to the header..

in this case is it a way to bring the total to the Header ??

 

[lbass]

Just drag the office name into the group footer next to the display of the variables. You can still drill down on the group #1 footers to get the details. You could also add the groupname back to GH#1 and then suppress it so it only appears during drilldown using this formula:

drilldowngrouplevel=0

-LB

 

[bnands]

Thanks for the reply Lbass

Yes i did it but my Total counts are adding up to the previous total on office level .


OfficeName | TotalNumberofDCN | TotalMeetHrs | totalNonMeetHrs
__________________________________________________________________
Officename 1 | 12 | 8 (Yes) | 4 (No)
Officename 2 | 4 | 10 (Yes) | 6 (No)


For example :

If Office Name1 total count of YEs = 8 and No = 4
and For office Name 2 yes= 2 and No = 2

but it is showing like OfficeName2 : Yes= 8+2 NO =4+2

its adding up tp the previous office..
I think i need to reset the count when office name changes ..

 

[lbass]

The reset should be in the office group header, and the office name and the summaries should be in the office group footer.

-LB

 

[bnands]

It worked ! thanks Lbass