Number of Occurrences for Conditional Formatting 2

[jenfromjax]

I am using Crystal Reports v10 accessing a SQL Server (v8 I think).

SCENARIO:
I have a Salesmen Commission Report. The report is grouped 1st by "Region", then by Salesman ("Rep"). This order of grouping is not negotiable unfortunately.

Occasionally, a Rep will sell something in more than one Region. Usually because the Regions get reassigned throughout the year.

I need all occurrences of the "Rep" ({table.rep}) field to turn red if there is more than one occurrence of a Rep in the report.


EX:
Region 1
Rep Sale Quota
John Doe $5,000 5,000,000
Jane Doe $6,000 5,000,000
Joe Smith $3,000 5,000,000

Region 2
Rep Sale Quota
Mark Jones $4,000 8,000,000
John Doe $2,000 5,000,000
Jen Walker $1,000 8,000,000


QUESTION:
I know how to apply conditional formatting, like mentioned above. However, I do not know how to get the number of occurrences of a Rep and then use it as the conditional formatting criteria.

I would appreciate any advice!! Please let me know if you need more info. I tried to cover everything.

~Jen

 

[lbass]

One approach would be to insert a subreport where you group only on {table.salesrep}. In the subreport, create a formula:

whileprintingrecords;
shared numbervar cntregion := distinctcount({table.region});

Place this formula on the subreport canvas, and then suppress all subreport sections.

Then link the subreport to the main report on {table.salesrep}. Place the subreport in a group header_2a section. Make it as narrow as possible, remove the borders, and resize group header_2a to make it as small as possible. Place the sales rep name in group header_2b. Format Group Header #1 to "Underlay following sections". While in the section expert, highlight group header_2b->go to the color tab->background->x+2 and enter:

whileprintingrecords;
shared numbervar cntregion;

if cntregion > 1 then crYellow else crNoColor

-LB

 

[Madawc]

Another method would be to add {table.rep} again as an alias. Make a left-outer link by salesman, and see if there is more than one link. You'd have to do this by having 'Salesman' as a lower-level group, but it ought to work.

If I were doing it, I'd check for two occurances in the same region (by summary count more than 1), as well as two occurances in different regions (achieved by minumum and maximum comparisons). On general principles, I like to test whether the data really is what it is supposed to be.

The subreport method should work, but subreports at a detail level always slow the run.

Madawc Williams (East Anglia, UK)

 

[jenfromjax]

Both are great ideas, I can't thank you enough. But I've made a wrong turn somewhere. So far, I've only had time to try LBass' suggestion. It's not quite working for me & I'm hoping someone can tell where I went wrong.

RETRACE MY STEPS:
1. Insert | Subreport | Group #1 = {table.salesrep}
2. Created a new formula, called it "lbass", pasted in:
whileprintingrecords;
shared numbervar cntregion := distinctcount ({table.region});
3. I wasn't sure what exactly was meant by "Subreport canvas" so I placed the formula in the details section.
4. Suppressed all subreport sections
5. Again, I wasn't sure about how to "link the subreport to the main report on {table.salesrep}" so I did not complete this step.
6. I placed the subreport in a group header_2a section & applied formatting.
7. Placed the sales rep name in group header_2b.
8. I formatted Group Header #1 to "Underlay following sections". While in the section expert, highlight group header_2b->go to the color tab->background->x+2 and entered:
whileprintingrecords;
shared numbervar cntregion;

if cntregion > 1 then crYellow else crNoColor


RESULTS:
Every row from GH2b comes back yellow.


NOTES:
Asside from the obvious step I missed & things I didn't understand, mentioned above, I noticed something happening on the subreport. When I allow the detail to show on the subreport, every record is 8. I don't know why all of them say 8, but I do know that is the number of Regions we have. It does explain why all of them are yellow though, since 8 is > 1.

That's where I'm stuck. I could use some advice on my steps & where I went wrong. Thanks again.

P.S. Madawc
The reason I need this is for instances where "John Doe" was originally assigned to Region 1. He sold an item while in Region 1, which he & his Regional Manager will get commission for. However, later on in the year we restructured the Region assignments and now John Doe reports to Region 4. He still gets credit for his sale while in Region 1, but so should the Region 1 manager. The sales stays with the Region and the Rep, not just the Rep. Then of course he sells more while in Region 4, so now his name appears in two regions. The great & almighty Oz (aka: Sales Manager) wants those Reps to stand out in Red.

I hope that helps! Please let me know what you think.

~Jen

 

[Madawc]

If a formula or variable isn't working as you expect, use [Insert Section] to add an extra detail section where you can look at it and see what it actually says.

Madawc Williams (East Anglia, UK)

 

[jenfromjax]

OK, I think I figured out what's happening, just not how to fix it. Formula "lbass" (cntregion) is doing a distinct count of all regions in the report (8) instead of a distinct count of regions within each Group (Rep).

How do I tell it to count only the regions in each group instead of the regions in the entire report?

 

[lbass]

You need to link the subreport to the main report. In the main report go to edit->edit subreport links-> choose {table.salesrep} from the main report as the field to link on and then in the bottom right corner of the screen, choose {table.salesrep} from the subreport as the subreport field to link on.

-LB

 

[jenfromjax]

LB,

You rock!!!! It works perfectly!!!

For anyone else who might try LB's solution, I was able to turn the font red instead of turning the whole row yellow using LB's formula:
whileprintingrecords;
shared numbervar cntregion;
if cntregion > 1 then crYellow else crNoColor

Instead of putting it in the Section Expert | Color tab | x+2, I pasted the following in Format Field | Font tab | x+2 for {table.rep}:
whileprintingrecords;
shared numbervar cntregion;
if cntregion > 1 then crRed else crBlack

Thanks again! You saved the day. I hope others are able to benefit from this as well.

~Jen

 

[lbass]

Sorry--I wasn't paying attention when I led you to the section expert instead of field formatting and to the background instead of the font colors...

-LB