Gary,
If you make a calculated field where IIF(SalesRepList Matches
"*os17*","os17",IIF(SalesRepList Matches "*os27*","os27",IIF(SalesRepList
Matches "*os15*","os15",IIF(SalesRepList Matches
"*os25*","os25",SalesRepCode)))). You would have to put in all your
combinations for "os" then use this field to group by and make your
aggregate calculations. It is a lot of work but it should group them by your
outside salesrepcode now.
Jim Frice
If you make a calculated field where IIF(SalesRepList Matches
"*os17*","os17",IIF(SalesRepList Matches "*os27*","os27",IIF(SalesRepList
Matches "*os15*","os15",IIF(SalesRepList Matches
"*os25*","os25",SalesRepCode)))). You would have to put in all your
combinations for "os" then use this field to group by and make your
aggregate calculations. It is a lot of work but it should group them by your
outside salesrepcode now.
Jim Frice
> -----Original Message-----
> From: Gary Grenier [mailto:ggrenier@...]
> Sent: Wednesday, August 16, 2000 2:46 PM
> To: vantage@egroups.com
> Subject: [Vantage] Re: Uh oh! RB Question again..
>
>
> A couple of replies inline...
>
> Carol, I think I get the idea here, but wouldn't this only work for one of
> my OS Reps at a time? I want my report to group each OS rep
> separately, but
> all in one report. They are all four characters long, and they
> always start
> (in the SalesRepList) at position 6, 7, 8, or 9. My IS Reps are all four
> characters long, as well, and they always start at position 1, so
> there's no
> problem there. For THAT report, I shortened the SalesRepList
> field to four
> characters. That way, it ignored the rest of the string.
>
> > From: Carol Engen <cengen@...>
>
> > To get around this, I created several functions. (In our case,
> SalesPeople
> > are two characters)
> > * ChosenRep. '31' {where 31 would...
> > * Rep1 SUBSTRING(SalesRepList,1,2)
> > * Rep2 SUBSTRING(SalesRepList,4,2)
> > * Rep3 SUBSTRING(SalesRepList,7,2)
> > * MatchToChosenRep IIF(Rep2Sub = ChosenRep Or Rep3Sub =
> > ChosenRep Or Rep1Sub = ChosenRep,yes,no)
> > My Filter in this report says and MatchToChosenRep = yes
>
> Jim, I've tried this. Unfortunately, because the OS Rep moves around in
> the string, every different position ends up as a different grouping.
> IS03~OS15~~~~, IS03~~OS15~~~, IS03~~~OS15~~, and IS03~~~~OS15~ end up as
> different groupings with separate totals. I want them all
> (because they all
> contain OS15) to be grouped together, with a single total. OS15 can also
> show up in the SalesRepList paired with other IS## reps. I want those in
> the same group as well. In this same report, I'd like to see a
> separate and
> single total for OS27, OS17, etc...
>
> > From: "Jim Frice" <Jim.Frice@...>
>
> > Can you set up a filter using the invchead.salereplist that matches
> "*os*".
> > That should pick up all invoices that have "os" for your outside reps in
> the
> > invchead.salereplist. If I am understanding your coding for
> "os" I believe
> > this will work for you.
> >
> > Jim Frice