Help with formula in Group Footer

[eliasc]

I have ten different formulas, all are the same for the exception that each one only puts information in the group footer if the date is in the specified date range. The problem I am having is that it is not putting hte correct information in the group footer. Below is one of the formulas. This is done in Crystal Reports 10. The Group Header / Footer is grouped by CompanyName.

If DateValue({Invoice.PostDate}) in CurrentDate - 49 to CurrentDate - 43 then
If {Memo.MemoID}="CONTACT" then
"CON"
Else If {Memo.MemoID}="BIO" then
"INV"
ELSE
" ";

As I mentioned, the above formula is not bringing back the correct data for the timeframe. Can anyone please tell me why? Thank you for your help.

 

[lbass]

This formula will show only the value of the last detail record in the group. Place the formula in the detail section and observe the results you get per record.

If you explain what you are trying to do with this formula, I could make a recommendation, but I'd have to also see a sample of how your results are displaying at the detail level, and then a sample of how you would like the corresponding final report to look.

-LB

 

[Madawc]

I'd suggest a pair of running totals, each counting for records with a condition, one using the first test and the other the second. Then in the group footer, check for values - likely to be null if none are found, so check for that first. Have the running totals reset for each group.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[eliasc]

I have enclosed the formulas and screen shots. I think it may be a null issue, but I put in the formula a not isnull(fld), but it seems to ignore it. Please help me.

Thanks,
Chris

 

[lbass]

Don't know what .docx is--but it doesn't work. Please just create a little sample of the details directly in the thread, along with a sample of how you want it to look.

-LB

 

[eliasc]

Sorry about that. .docx is Word 2007. Enclosed is a Word 2003 .doc attachment which has everything I am looking for as well as the formulas.

Thanks for your help.

 

[lbass]

This still doesn't explain what you expect to see in the group footer. Do you want to show "CON" in the group footer if ANY detail records contain "CON"? Or?

-LB

 

[eliasc]

Every week in the perfect world, the salespeople are suppose to contact their customers. When they contact their customers they make a note in MAS 500 which is using SQL Server. The notes will have a Memo ID of Contact or Bio. Under each week, I need to show on the report if they Contacted or made a sale. Below is what I am looking for.

BIO (Sale) = INV
Contact = CON

01/08 01/15 01/22 01/29 02/05 02/12
Customer A CON INV CON
Customer B CON INV
Customer C
Customer D CON

The report is grouped by Customer Name. I hope this helps. Please let me know if you have any other questions.

 

[lbass]

If there is both a con and an inv in one week (what about bio?), does one have priority over another? (It appears that you don't want to show both).

-LB

 

[eliasc]

INV would be the priority over CON.

Thanks

 

[lbass]

You didn't say how Bio fits in, but place your original formula in the detail section and insert a maximum on it at the group level. This would give priority to what is shown in the group footer in this order:

Inv
Con
Bio

-LB

 

[eliasc]

Below is the code I used.

WhileReadingRecords;

If (Not IsNull({@PostDate})) and (Not IsNull
({tciMemo.MemoID})) then
If {@PostDate}in {@WeekNine} to DateAdd("d",7,
{@WeekNine}) then
If Not IsNull({tciMemo.MemoID}) then
If Trim({tciMemo.MemoID}) = ("BIO") then
"INV";

If (Not IsNull({@EffectiveDate})) and (Not IsNull
({tciMemo.MemoID})) then
If {@EffectiveDate}in {@WeekNine} to DateAdd("d",7,
{@WeekNine}) then
If Not IsNull({tciMemo.MemoID}) then
If Trim({tciMemo.MemoID}) like ("CONTACT*") then
"CON";

I have the above code in the Detail section. I inserted amaximum in the group footer. All that is coming back is CON. BIO=INV, so it should only show the following sort order:

Inv
Con
" "

What should I do? Thanks for your help.

 

[lbass]

Try this:

If Not IsNull({@PostDate}) and
Not IsNull ({tciMemo.MemoID}) then
(
If {@PostDate} in {@WeekNine} to DateAdd("d",7,
{@WeekNine}) and
Trim({tciMemo.MemoID}) = "BIO" then
"INV"
) else

If Not IsNull({@EffectiveDate}) and
Not IsNull ({tciMemo.MemoID}) then
(
If {@EffectiveDate}in {@WeekNine} to DateAdd("d",7,{@WeekNine}) and
Trim({tciMemo.MemoID}) like "CONTACT*" then
"CON"
)

-LB