Report Builder Q

Yahoo Archive
1
Worked like a charm! Thanks Ed & Todd.

What a relief! I guess I should have been more specific!

Thanks again!

Juliet

-----Original Message-----
From: Giallombardo, Ed [mailto:egiallombardo@...]
Sent: Wednesday, May 22, 2002 1:08 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Q


This is what I use to pull the salesperson:
Entry(1,SalesRepList,Chr(126))

126 is the ASCII code for the tilde. I have done joins on this to
SalesRep.SalesRepCode. If you're trying to join it to a numeric field
you
would probably have to convert it first using NUMERIC(text).

- Ed

-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Wednesday, May 22, 2002 12:57 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


I said I would report back what I found, so here goes...

These were the two functions that I got to work. You can't do a join on
them (too complex I think) so it was all for nothing.

IIF(LENGTH(SalesRepList) = 6,SUBSTRING(SalesRepList,1,2),
SUBSTRING(SalesRepList,1,3))
For our situation the length will either be 6 or 7 digits long.

IIF(SUBSTRING(SalesRepList,3,1) <> '0', " ","0")
For our situation if their are three digits the last one will always be
0. If the 3rd digit is not a zero but a ~ then it must be two digits
long.

Any function I tried with the ~ it wouldn't work. If I put the quotes
around the ~, I would get an error to terminate the string.

Thanks for all of your help.


Juliet



-----Original Message-----
From: Juliet Martin
Sent: Friday, May 17, 2002 7:16 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.

Juliet

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like this
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

I'd go with either of these:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)

-WC



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=226020.2067224.3532861.1971030/D=egroupweb/S=1705
0071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http:
//ww
w.tv/en/landing.shtml&HHcode=yhc3my2>

<http://us.adserver.yahoo.com/l?M=226020.2067224.3532861.1971030/D=egrou
pmai
l/S=1705007183:HM/A=1073422/rand=268839557>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>

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]



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

Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
2
How do you substring a field up to certain character? I have to pull
out records from a field that are sometimes two digits long and
sometimes 3 digits long but always have a ~ on the end. Can this be
done?

I thought I would ask while I am beating my head against my monitor.

Thanks,

Juliet
3
Will the substring always start at the same position?



[Non-text portions of this message have been removed]
4
You can use the INDEX function to determine positions of the delimter "~".
Either in one big formula or break it into a couple where you set the Start
and End position numbers to use in SUBSTRING functions (for easier
reading/debugging). I do this all the time parsing date ranges out of the
filter string to use on headings. I think there is another function that
directly uses a delimter character to break out chunks of data if all are
seperated by the same character.
-Todd C.

-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Thursday, May 16, 2002 11:29 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Q


How do you substring a field up to certain character? I have to pull
out records from a field that are sometimes two digits long and
sometimes 3 digits long but always have a ~ on the end. Can this be
done?

I thought I would ask while I am beating my head against my monitor.

Thanks,

Juliet


Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=226020.2031982.3521154.1971030/D=egroupweb/S=17050071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http://ww
w.tv/en/landing.shtml&HHcode=yhc3my2>

<http://us.adserver.yahoo.com/l?M=226020.2031982.3521154.1971030/D=egroupmai
l/S=1705007183:HM/A=1073422/rand=942729036>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>

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]
5
Try the ENTRY function. That's the one I was thinking of.
-Todd C.

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Thursday, May 16, 2002 11:43 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Q


You can use the INDEX function to determine positions of the delimter "~".
Either in one big formula or break it into a couple where you set the Start
and End position numbers to use in SUBSTRING functions (for easier
reading/debugging). I do this all the time parsing date ranges out of the
filter string to use on headings. I think there is another function that
directly uses a delimter character to break out chunks of data if all are
seperated by the same character.
-Todd C.

-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Thursday, May 16, 2002 11:29 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Q


How do you substring a field up to certain character? I have to pull
out records from a field that are sometimes two digits long and
sometimes 3 digits long but always have a ~ on the end. Can this be
done?

I thought I would ask while I am beating my head against my monitor.

Thanks,

Juliet


Yahoo! Groups Sponsor

ADVERTISEMENT

<
http://rd.yahoo.com/M=226020.2031982.3521154.1971030/D=egroupweb/S=17050071
<http://rd.yahoo.com/M=226020.2031982.3521154.1971030/D=egroupweb/S=17050071
>
83:HM/A=1073422/R=0/*
http://www.qksrv.net/click-1084497-610300?url=http://ww
<http://www.qksrv.net/click-1084497-610300?url=http://ww>
w.tv/en/landing.shtml&HHcode=yhc3my2>

<
http://us.adserver.yahoo.com/l?M=226020.2031982.3521154.1971030/D=egroupmai
<http://us.adserver.yahoo.com/l?M=226020.2031982.3521154.1971030/D=egroupmai
>
l/S=1705007183:HM/A=1073422/rand=942729036>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
< http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.> >
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
< http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>
< http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links> >

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




[Non-text portions of this message have been removed]



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=226020.2031982.3521154.1971030/D=egroupweb/S=17050071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http://ww
w.tv/en/landing.shtml&HHcode=yhc3my2>

<http://us.adserver.yahoo.com/l?M=226020.2031982.3521154.1971030/D=egroupmai
l/S=1705007183:HM/A=1073422/rand=922460837>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>

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]
6
Create a calculated field called TILDE = CHR(126)
Create a second field FOUNDYA = ENTRY(1,fieldNAME,TILDE)

Patrick

-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Thursday, May 16, 2002 11:29 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Q


How do you substring a field up to certain character? I have to pull
out records from a field that are sometimes two digits long and
sometimes 3 digits long but always have a ~ on the end. Can this be
done?

I thought I would ask while I am beating my head against my monitor.

Thanks,

Juliet


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

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
7
The field data would look like this

20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
10~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

Thank you all for your suggestions. I will start trying them and report
back.

Juliet


-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Thursday, May 16, 2002 11:40 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


Will the substring always start at the same position?



[Non-text portions of this message have been removed]



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

Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
8
At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like this
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

I'd go with either of these:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)

-WC



[Non-text portions of this message have been removed]
9
Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.

Juliet

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like this
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

I'd go with either of these:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)

-WC



[Non-text portions of this message have been removed]



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

Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
10
I said I would report back what I found, so here goes...

These were the two functions that I got to work. You can't do a join on
them (too complex I think) so it was all for nothing.

IIF(LENGTH(SalesRepList) = 6,SUBSTRING(SalesRepList,1,2),
SUBSTRING(SalesRepList,1,3))
For our situation the length will either be 6 or 7 digits long.

IIF(SUBSTRING(SalesRepList,3,1) <> '0', " ","0")
For our situation if their are three digits the last one will always be
0. If the 3rd digit is not a zero but a ~ then it must be two digits
long.

Any function I tried with the ~ it wouldn't work. If I put the quotes
around the ~, I would get an error to terminate the string.

Thanks for all of your help.


Juliet



-----Original Message-----
From: Juliet Martin
Sent: Friday, May 17, 2002 7:16 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.

Juliet

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like this
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

I'd go with either of these:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)

-WC
11
Create a function for an item named Tilde = Chr(126) and use this instead of
trying to put it in quotes.
-Todd C.

-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Wednesday, May 22, 2002 12:57 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


I said I would report back what I found, so here goes...

These were the two functions that I got to work. You can't do a join on
them (too complex I think) so it was all for nothing.

IIF(LENGTH(SalesRepList) = 6,SUBSTRING(SalesRepList,1,2),
SUBSTRING(SalesRepList,1,3))
For our situation the length will either be 6 or 7 digits long.

IIF(SUBSTRING(SalesRepList,3,1) <> '0', " ","0")
For our situation if their are three digits the last one will always be
0. If the 3rd digit is not a zero but a ~ then it must be two digits
long.

Any function I tried with the ~ it wouldn't work. If I put the quotes
around the ~, I would get an error to terminate the string.

Thanks for all of your help.


Juliet



-----Original Message-----
From: Juliet Martin
Sent: Friday, May 17, 2002 7:16 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.

Juliet

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like this
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

I'd go with either of these:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)

-WC



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=226020.2067224.3532861.1971030/D=egroupweb/S=17050071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http://ww
w.tv/en/landing.shtml&HHcode=yhc3my2>

<http://us.adserver.yahoo.com/l?M=226020.2067224.3532861.1971030/D=egroupmai
l/S=1705007183:HM/A=1073422/rand=268839557>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>

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]
12
This is what I use to pull the salesperson:
Entry(1,SalesRepList,Chr(126))

126 is the ASCII code for the tilde. I have done joins on this to
SalesRep.SalesRepCode. If you're trying to join it to a numeric field you
would probably have to convert it first using NUMERIC(text).

- Ed

-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Wednesday, May 22, 2002 12:57 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


I said I would report back what I found, so here goes...

These were the two functions that I got to work. You can't do a join on
them (too complex I think) so it was all for nothing.

IIF(LENGTH(SalesRepList) = 6,SUBSTRING(SalesRepList,1,2),
SUBSTRING(SalesRepList,1,3))
For our situation the length will either be 6 or 7 digits long.

IIF(SUBSTRING(SalesRepList,3,1) <> '0', " ","0")
For our situation if their are three digits the last one will always be
0. If the 3rd digit is not a zero but a ~ then it must be two digits
long.

Any function I tried with the ~ it wouldn't work. If I put the quotes
around the ~, I would get an error to terminate the string.

Thanks for all of your help.


Juliet



-----Original Message-----
From: Juliet Martin
Sent: Friday, May 17, 2002 7:16 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.

Juliet

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q


At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like this
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc

I'd go with either of these:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)

-WC



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=226020.2067224.3532861.1971030/D=egroupweb/S=17050071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http://ww
w.tv/en/landing.shtml&HHcode=yhc3my2>

<http://us.adserver.yahoo.com/l?M=226020.2067224.3532861.1971030/D=egroupmai
l/S=1705007183:HM/A=1073422/rand=268839557>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>

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]