Ranking Report 1

[missy005]

I inherited a CR 8.5 Report, and I am definitely a newbie. The report has three groups: 1 by contact, 1 by zip code, and 1 by account mgr. Each acct mgr has certain zip codes within which the contacts fall.

Currently, the report counts how many times a referral was made by each contact each month. All of this is done in formulas (which I am unable to sort or group by).The code for the select expert is below. I've tried to do sort by group, but it greyed out. I know I need a summary on a group field, but I've tried creating one in several places and it still is not showing up (Top Sort is greyed out still).

{ContSupp.RECTYPE} = "R" and
{ContSupp.CONTSUPREF} = "Physician - Prescribing" and
{@PrescriptionYear} = {@CurrentYear} and
not ({Contact1.KEY2} startswith "EU")

Other data fields: LASTDATE (Used in year formulas), CONTACT
I've also created a running total field (on CONTSUPREF) changing when the CONTACT changes.



Crystal Reports 8.5 primarily using xBase files.

 

[missy005]

Okay. Thanks again.

You might recall me saying that the calculations were done on a row by row basis. The grand total formula is a successive process. The following formulas are what build the grand total.

CONTACT1CNT

WhilePrintingRecords;
NumberVar Contact1Cnt;
NumberVar Zip1Cnt:=Zip1Cnt+(Contact1Cnt-1);
Contact1Cnt:=Contact1Cnt-1

ZIP1CNT

WhilePrintingRecords;
NumberVar Zip1Cnt;
NumberVar Account1Cnt:=Account1Cnt+(Zip1Cnt-1);
Zip1Cnt:=Zip1Cnt-1

ACCOUNT1CNT

WhilePrintingRecords;
NumberVar Account1Cnt;
NumberVar Grand1Cnt:=Grand1Cnt+(Account1Cnt-1);
Account1Cnt:=Account1Cnt-1

GRAND1CNT - monthly total

WhilePrintingRecords;
NumberVar Grand1Cnt;
Grand1Cnt :=Grand1Cnt

GRANDTOTAL - yearly total
WhilePrintingRecords;
NumberVar GrandTotal;
GrandTotal:={@Grand1Cnt}+{@Grand2Cnt}+{@Grand3Cnt}+{@Grand4Cnt}+{@Grand5Cnt}+{@Grand6Cnt}+{@Grand7Cnt}+{@Grand8Cnt}+{@Grand9Cnt}+{@Grand10Cnt}+{@Grand11Cnt}+{@Grand12Cnt}

 

[lbass]

Well, you might be better off changing the initial formulas. It almost looks like these formulas are correcting for not having a display formula at the group level.

To continue using the variables, you would have a detail formula like:

whileprintingrecords;
numbervar contact1cnt;
numbervar account1cnt;
numbervar grand1cnt;

if year({ContSupp.LASTDATE})=Year({?EndMonth}) and
Month({ContSupp.LASTDATE}) = 1 then
(
numbervar contact1cnt := contact1cnt + 1;
numbervar account1cnt := account1cnt + 1;
numbervar grand1cnt := grand1cnt + 1;
)

You would also have the following reset formulas:

//{@contact1reset} to be placed in the contact group header:
whileprintingrecords;
numbervar contact1cnt := 0;

//{@accountreset} to be placed in the account group header:
whileprintingrecords;
numbervar account1cnt := 0;

Then you would have three display formulas:

//{@contact1cntdisplay} to be placed in the contact group footer:
whileprintingrecords;
numbervar contact1cnt;

//{@account1cntdisplay} to be placed in the account group footer:

//{@grandtotaldisplay} to be placed in the report footer:
whileprintingrecords;
numbervar grand1cnt;

You would have to create formulas like this for each month. All in all, it would probably be a lot easier to redo the detail level formulas so you can insert summaries at the group and grand total levels. You might create the formula I suggested without variables for month 1 and insert summaries (SUMs) and a grand total on it and verify that the numbers are correct before redoing everything.

-LB

 

[missy005]

LB, Thanks so much. Just to clarify, I should do a test formula (in the details) of:

if year({ContSupp.LASTDATE})=Year({?EndMonth}) and
Month({ContSupp.LASTDATE}) = 1 then 1

...and insert a summary and grand total on it?

 

[lbass]

Yes, right click on it and insert a SUM and choose for all groups and insert a grand total.

-LB

 

[missy005]

Okay, thanks so much. That worked for my monthly grand totals, now is the process the same for the far right totals (by physician)? The number should be the same as the
ContSupp.LASTDATE (ranking field), but it won't let me reinsert that same count field.

 

[lbass]

Just drag that field over to where you want it in the grid. If you need to display the count twice (once as rank, once as the total) then don't try to insert it again, just copy the first one and paste it where you want it.

-LB

 

[missy005]

Thank you so much for your help. Everything is working as intended (atleast no one is yelling yet ;-). I would have never figured this out without you! I really appreciate it. Have a great day.