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]

I' ve also read over the posts in the forum, and while they were helpful, I couldn't figure out my problem. I'm sure it is due to my lack of expertiise with this tool. My biggest issue is not being able to type the SQL Directly. I've been spoiled by doing queries in sql server query analyzer and not having to do any reporting. Nevertheless, thanks in advance.

 

[lbass]

You need to supply the contents of the formula you were hoping to sort by (and also the contents of any formulas it contains).

-LB

 

[missy005]

For every month the previous programmer has a count field (i.e. Contact1Cnt). Here is the total that I'm trying to sort by:

WhilePrintingRecords;
NumberVar ContactTotal;
ContactTotal:={@Contact1Cnt}+{@Contact2Cnt}+{@Contact3Cnt}+{@Contact4Cnt}+{@Contact5Cnt}+{@Contact6Cnt}+{@Contact7Cnt}+{@Contact8Cnt}+{@Contact9Cnt}+{@Contact10Cnt}+{@Contact11Cnt}+{@Contact12Cnt}

However, when I do this I get a message stating error creating summary/running total field.

Here is the formula (they have the same pattern) for @Contact1Cnt:

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

The weird thing (maybe only to me) was that these formulas always appear above the line that they are meant to calculate (again,probably my lack of knowledge).

The only direct query from the dbase file is (pattern continues):

WhilePrintingRecords;
NumberVar Contact1Cnt;
If Year({ContSupp.LASTDATE})=Year({?EndMonth}) 
and
(Month({ContSupp.LASTDATE}) = 1) then 
NumberVar Contact1Cnt:=Contact1Cnt+1;

 

[missy005]

Here is a screenshot of the report. Sorry about the size.

 

[lbass]

In what report section is your last formula placed--the detail section or a group footer? Can't tell from your screenshot what formulas are in the details section versus in the footers.

-LB

 

[missy005]

The last formula is in the details section and completes across the page for each month (it's incremented by one).

So, actually I pasted the code for the formulas in reverse order: the first two are found in the group #3 footer and last one is in the details section.

Thanks so much for your prompt responses.

 

[lbass]

If that's the case, then I think you can insert {ContSupp.LASTDATE} in the detail section and suppress it. Then right click on it and insert summaries (count) at all group levels. Then try to do the topN sort on count of {ContSupp.LASTDate} at whatever group level you want to sort by.

The problem you were having is that the person used variables unnecessarily (I think). The formulas could have been done like:

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

...with summaries (SUMs, not counts) inserted on them at all group levels. There's no need to redo them, however.

-LB

 

[missy005]

Thanks. I'll try that.

 

[missy005]

Hmmm that worked as it was supposed to; however, now I'm th inking that I need to try a different approach. The attached screenshot shows the results. However, the intended result is to have the top-ranked physician (with the most referrals) listed first. For example, the last physician in the screen shot with 3 referrals should have been listed first. Maybe this should be a grand total ranking (because I don't want page-level ranking)?

{img

http://www.stacywig.com/images/screenshot.gif

]

 

[missy005]

Sorry better image. I just whited out some info so as not to disclose info.

 

[lbass]

Is "Rank" the count of {ContSupp.LASTDATE} for the contact group? It looks like you didn't insert the topN yet. Select the summary (Rank). Then go to report->topN/group sort and choose count of {Contsupp.LASTDATE} for whatever group level you want the topN for--the groups are shown as different tabs at the top of the topN screen.

P.S. I could have sworn you had shown the design view in your first screen post. Did you somehow edit it to show preview?

-LB

 

[missy005]

Thanks I'll try that. Yes, it is in Preview mode, but I can post any others in design mode.

 

[missy005]

Yes "Rank" is the count of {ContSupp.LASTDATE}. I tried the topN/Group Sort, but that didn't do anything. I chose the group level of "Contact", because that is the physician name field. However, it is just repeating what I already have on the far right column of the page (total)>

 

[lbass]

I don't think you are implementing this correctly, since this should work. You should be getting the groups ordered like:

Rank
3
2
1
1
1

Please explain step by step what you did. You should have gone to report->topN/group sort->Contacts Tab->All->Select count of {ContSupp.LASTDATE} from the dropdown on the right->Select "descending."

-LB

 

[missy005]

Okay.
I right clicked on ContSupp.LASTDATE in the details section and clicked insert. Then I created a summary (count) of ContSupp.LASTDATE.

I then did the Top/N sort step. See screenshots (I hope I'm not violating any rules with these screenshots).

 

[lbass]

It looks like you are doing everything correctly.

It looks like you have Group#2 headers and footers suppressed. If you unsuppress the header and footer, do the group #3 fields all appear under the same group#2 instance? If they were all from different Group#2 instances then the group sort won't work as you want it to, since the group sort works within the higher level grouping--even if suppressed.

As a test, you might want to save the report under a different name and delete group #2--then I think you'll get the sort you want.

-LB

 

[missy005]

Thanks so much. After deleting the group#2, the sort worked; however it got rid of my grand totals across the bottom. I think that relates to them using so many formulas to get the sums.

 

[missy005]

I may have to go back and implement your suggestion (with sums inserted at the group level) in order to get the grand totals to work.

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

 

[lbass]

You shouldn't have to redo everything. Please copy a grand total formula into the thread here and we can rework it. The formula should still be visible in design mode.

-LB