Crystal Report Array

Yahoo Archive
1
I have Sales reps and rate! Thanks to all that helped. What a
process, don't think I could have thought that one up on my own.

Thanks again,
Shari


--- In vantage@yahoogroups.com, "David Gartner" <dgartner@e...>
wrote:
> Thanks Aaron for posting the better and more complete formulas. I
missed
> them the first time around. They solve the problem of not having
the REPID's
> all the same length.
> This group, time and time again, finds the solutions.
>
> David Gartner
> EPG Companies Inc.
>
>
> > -----Original Message-----
> > From: David Gartner [mailto:dgartner@e...]
> > Sent: Friday, August 06, 2004 3:28 PM
> > To: vantage@yahoogroups.com
> > Subject: RE: [Vantage] Crystal Report Array
> >
> >
> > I have done this with significant help from people on this list.
> >
> > It has to be a subreport or a separate report all together.
> >
> > A SQL expression is used:
> > {fn SUBSTRING(RepRate, 1)}
> >
> > 1st rate is extracted by
> > Local numbervar varfirstsemi := InStr (1,
{InvcHead.RepRate},";");
> > If varfirstsemi <> 0 Then tonumber (Mid ({InvcHead.RepRate}, 1,
> > varfirstsemi-1))
> >
> > 2st rate is extracted by
> > Local numbervar varfirstsemi := InStr (1,
{InvcHead.RepRate},";");
> > Local numbervar varsecondsemi := InStr (4,
{InvcHead.RepRate},";");
> > If varsecondsemi <> 0 Then tonumber(Mid({InvcHead.RepRate},
> > (varfirstsemi+1), (varsecondsemi-varfirstsemi-1)))
> >
> > There may be an easier way... but it works.
> > Hope this helps,
> >
> > David Gartner
> > EPG Companies Inc.
> > dgartner@e...
> > 763-424-2613 (v)
> >
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: fselliott1 [mailto:fselliott1@y...]
> > > Sent: Friday, August 06, 2004 2:52 PM
> > > To: vantage@yahoogroups.com
> > > Subject: [Vantage] Crystal Report Array
> > >
> > >
> > > I need HELP! I'm trying to get the RepRate from the OrderDtl.
When
> > > viewed in Report builder it is RepRate[1], RepRate[2], etc...
but in
> > > Crystal it is a 60 character string field in which each rate is
> > > delimited by a ";". I have tried using the formula "Split"
but it
> > > displays the error "the result of a formula can not be an
array".
> > > So I tried to make an array but am having no luck. Anyone out
there
> > > tried this yet?
> > >
> > > Thanks for any and all help.
> > > Shari
> > >
2
I need HELP! I'm trying to get the RepRate from the OrderDtl. When
viewed in Report builder it is RepRate[1], RepRate[2], etc... but in
Crystal it is a 60 character string field in which each rate is
delimited by a ";". I have tried using the formula "Split" but it
displays the error "the result of a formula can not be an array".
So I tried to make an array but am having no luck. Anyone out there
tried this yet?

Thanks for any and all help.
Shari
3
do you have more than 1 sales rep if not try left or trim.



Frank Zeigafuse

Innovative Office Products

General Manager

Direct Phone: 610-559-6369

Email: fzeigafuse@...



-----Original Message-----
From: fselliott1 [mailto:fselliott1@...]
Sent: Friday, August 06, 2004 3:52 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal Report Array


I need HELP! I'm trying to get the RepRate from the OrderDtl. When
viewed in Report builder it is RepRate[1], RepRate[2], etc... but in
Crystal it is a 60 character string field in which each rate is
delimited by a ";". I have tried using the formula "Split" but it
displays the error "the result of a formula can not be an array".
So I tried to make an array but am having no luck. Anyone out there
tried this yet?

Thanks for any and all help.
Shari



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=12977rac1/M=295196.4901138.6071305.3001176/D=gr
oups/S=1705007183:HM/EXP=1091908336/A=2128215/R=0/SIG=10se96mf6/*http://comp
anion.yahoo.com> click here

<http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S=
:HM/A=2128215/rand=252819103>


_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/


* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .



<www.LCDarms.com> -- Ergonomic mounting solutions for monitors and keyboards

This e-mail is from Innovative Office Products, Inc. but expresses the views of the sender and not necessarily the views of the company. The email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed. It may not be disclosed or used by any other than that addressee, nor may it be copied in any way. If received in error, please delete and notify the sender via e-mail or sales@... quoting the name of the sender. Messages sent via this medium may be subject to delays, non-delivery and unauthorized alteration. This e-mail has been prepared using information believed by the author to be reliable and accurate. Unless expressly stated otherwise, this e-mail does not form part of a legally binding contract or agreement between recipient and Innovative Office Products, Inc.

Warning: Although Innovative Office Products, Inc. has taken reasonable precautions to ensure no viruses are present in this email, we cannot accept responsibility for any loss or damage arising from the use of this email or attachments. It is, therefore, your responsibility to scan attachments (if any).



[Non-text portions of this message have been removed]
4
I have done this with significant help from people on this list.

It has to be a subreport or a separate report all together.

A SQL expression is used:
{fn SUBSTRING(RepRate, 1)}

1st rate is extracted by
Local numbervar varfirstsemi := InStr (1, {InvcHead.RepRate},";");
If varfirstsemi <> 0 Then tonumber (Mid ({InvcHead.RepRate}, 1,
varfirstsemi-1))

2st rate is extracted by
Local numbervar varfirstsemi := InStr (1, {InvcHead.RepRate},";");
Local numbervar varsecondsemi := InStr (4, {InvcHead.RepRate},";");
If varsecondsemi <> 0 Then tonumber(Mid({InvcHead.RepRate},
(varfirstsemi+1), (varsecondsemi-varfirstsemi-1)))

There may be an easier way... but it works.
Hope this helps,

David Gartner
EPG Companies Inc.
dgartner@...
763-424-2613 (v)





> -----Original Message-----
> From: fselliott1 [mailto:fselliott1@...]
> Sent: Friday, August 06, 2004 2:52 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Crystal Report Array
>
>
> I need HELP! I'm trying to get the RepRate from the OrderDtl. When
> viewed in Report builder it is RepRate[1], RepRate[2], etc... but in
> Crystal it is a 60 character string field in which each rate is
> delimited by a ";". I have tried using the formula "Split" but it
> displays the error "the result of a formula can not be an array".
> So I tried to make an array but am having no luck. Anyone out there
> tried this yet?
>
> Thanks for any and all help.
> Shari
>
>
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You
> must have already linked your email address to a yahoo id to
> enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report
> Builder and Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
> Yahoo! Groups Links
>
>
>
>
>
>
5
For anything commission related I've always used Microsoft Access to
deal with the information. I parse out the arrays into their own
fields and create Access tables and report of off them. If you are
at all familiar with Access I'd suggest doing that, a lot simpler
than trying to report off of Arrays, IMO. If you don't know Access,
I'd suggest trying to learn it. It is a very valuable tool.

Brian Stenglein
Clow Stamping Co.


--- In vantage@yahoogroups.com, "fselliott1" <fselliott1@y...> wrote:
> I need HELP! I'm trying to get the RepRate from the OrderDtl. When
> viewed in Report builder it is RepRate[1], RepRate[2], etc... but
in
> Crystal it is a 60 character string field in which each rate is
> delimited by a ";". I have tried using the formula "Split" but it
> displays the error "the result of a formula can not be an array".
> So I tried to make an array but am having no luck. Anyone out
there
> tried this yet?
>
> Thanks for any and all help.
> Shari
6
I will give that a try. What a mess to try and get that out of
there! Thanks David.

I could have one sales rep, I could have 5 sales reps. All depends
on where the order is going too. Wish they would have limited that
but they did not.

As for Access, don't know it but want to learn it. Just have no
time right now. Thanks to all. I'll let you know if I finally get
the data out of there!

Shari
7
Shari,
I had to split this data out once before in crystal.
There are a couple methods to try. For the Rep code, create 5 calculated
fields (rep1,rep2...). Each filed will use the "instr" function to find
the place of the ~ "Chr(126)" character. From there, you will use the
location of these to extract the actual code using the "mid" function.
As for the others, again 5 calculated fields. Give this a try:
reprate1: Left ({OrderDtl.RepRate}, (InStr ({OrderDtl.RepRate},
";")-1)) & "%"
reprate2: Mid ({OrderDtl.RepRate},(InStr ({OrderDtl.RepRate}, ";")+1),
(InStr ((InStr ({OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate},
";")-(InStr ({OrderDtl.RepRate}, ";")))-1) & "%"
reprate3: Mid ({OrderDtl.RepRate},(InStr ((InStr ({OrderDtl.RepRate},
";")+1), {OrderDtl.RepRate}, ";")+1), (InStr ((InStr ((InStr
({OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")+1),
{OrderDtl.RepRate}, ";")-(InStr ((InStr ({OrderDtl.RepRate}, ";")+1),
{OrderDtl.RepRate}, ";")))-1) & "%"
reprate4: Mid ({OrderDtl.RepRate},(InStr ((InStr ((InStr
({OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")+1),
{OrderDtl.RepRate}, ";")+1), (InStr ((InStr ((InStr ((InStr
({OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")+1),
{OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")-(InStr ((InStr
((InStr ({OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")+1),
{OrderDtl.RepRate}, ";")))-1) & "%"
reprate5: Mid ({OrderDtl.RepRate},(InStr ((InStr ((InStr ((InStr
({OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")+1),
{OrderDtl.RepRate}, ";")+1), {OrderDtl.RepRate}, ";")+1)) & "%"

repsplit1: Left ({OrderDtl.RepSplit}, (InStr ({OrderDtl.RepSplit},
";")-1))

You can then follow the above examples to find the rest of the splits.
I have done all my rep info in a sub report. This should help point you
in a direction that should help.
To give credit where due, the formulas above were created with
significant input from the group about 6 months ago.
Good luck,
Aaron Hoyt
Hittite Microwave

-----Original Message-----
From: fselliott1 [mailto:fselliott1@...]
Sent: Monday, August 09, 2004 2:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Crystal Report Array


I will give that a try. What a mess to try and get that out of
there! Thanks David.

I could have one sales rep, I could have 5 sales reps. All
depends
on where the order is going too. Wish they would have limited
that
but they did not.

As for Access, don't know it but want to learn it. Just have no

time right now. Thanks to all. I'll let you know if I finally
get
the data out of there!

Shari



Useful links for the Yahoo!Groups Vantage Board are: ( Note:
You must have already linked your email address to a yahoo id to enable
access. )
(1) To access the Files Section of our Yahoo!Group for Report
Builder and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links



Yahoo! Groups Sponsor
ADVERTISEMENT
click here
<http://us.ard.yahoo.com/SIG=129qmn9do/M=298184.5285298.6392945.3001176/
D=groups/S=1705007183:HM/EXP=1092161897/A=2164330/R=0/SIG=11eamf8g4/*htt
p://www.netflix.com/Default?mqso=60183350>

<http://us.adserver.yahoo.com/l?M=298184.5285298.6392945.3001176/D=group
s/S=:HM/A=2164330/rand=312606258>


_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/

* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms
of Service <http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
8
Thanks Aaron for posting the better and more complete formulas. I missed
them the first time around. They solve the problem of not having the REPID's
all the same length.
This group, time and time again, finds the solutions.

David Gartner
EPG Companies Inc.


> -----Original Message-----
> From: David Gartner [mailto:dgartner@...]
> Sent: Friday, August 06, 2004 3:28 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] Crystal Report Array
>
>
> I have done this with significant help from people on this list.
>
> It has to be a subreport or a separate report all together.
>
> A SQL expression is used:
> {fn SUBSTRING(RepRate, 1)}
>
> 1st rate is extracted by
> Local numbervar varfirstsemi := InStr (1, {InvcHead.RepRate},";");
> If varfirstsemi <> 0 Then tonumber (Mid ({InvcHead.RepRate}, 1,
> varfirstsemi-1))
>
> 2st rate is extracted by
> Local numbervar varfirstsemi := InStr (1, {InvcHead.RepRate},";");
> Local numbervar varsecondsemi := InStr (4, {InvcHead.RepRate},";");
> If varsecondsemi <> 0 Then tonumber(Mid({InvcHead.RepRate},
> (varfirstsemi+1), (varsecondsemi-varfirstsemi-1)))
>
> There may be an easier way... but it works.
> Hope this helps,
>
> David Gartner
> EPG Companies Inc.
> dgartner@...
> 763-424-2613 (v)
>
>
>
>
>
> > -----Original Message-----
> > From: fselliott1 [mailto:fselliott1@...]
> > Sent: Friday, August 06, 2004 2:52 PM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Crystal Report Array
> >
> >
> > I need HELP! I'm trying to get the RepRate from the OrderDtl. When
> > viewed in Report builder it is RepRate[1], RepRate[2], etc... but in
> > Crystal it is a 60 character string field in which each rate is
> > delimited by a ";". I have tried using the formula "Split" but it
> > displays the error "the result of a formula can not be an array".
> > So I tried to make an array but am having no luck. Anyone out there
> > tried this yet?
> >
> > Thanks for any and all help.
> > Shari
> >