The first filter should be Company = 'XXXX' (whatever company code you use) for the "Master Table" for the report. The second table in a report is always linked to that master table (and then others in various ways after that).
Then all other table linking should ALWAYS include the Company as the first field linked - plus use as many fields as possible in the linking as are part of one of the table indexes. If possible find an index where you can use EVERY field in it. If there is a choice of indexes then use the one that is closest to the sort order you need.
So the answer to your first question is YES to both.
Another filter order tip I forgot was to place any filters on table index fields next after company so as to reduce the record complex size as quickly as possible right at the initial read level. Reduces a lot of work in the sort and aggregating phases.
I always thought that every table in Vantage started with Company as part of the primary index. A few have it as not the first field in the table but rather a little further down but it is definitely in the indexes. If you have a specific table with no company field let me know which it is.
-Todd C.
-----Original Message-----
From: Paul Whittle [mailto:pwhittle@...]
Sent: Monday, January 10, 2005 7:37 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
Todd.
Are you suggesting making the first filter company='Company ID', or Company
(in table 1) = Company on table 2?
I tried the first version on a BOM exception report and it still took 30
seconds to run!
Why don't some tables appear to have a valid company - at least not in the
report I tried (which used part rev, partmtl, plus part as two aliases)?
Best regards
Paul Whittle
Business Process (Quality) Manager
Jiskoot Autocontrol Ltd
Tunbridge Wells
Kent TN1 2DJ UK
Direct Line +44 (0)1892 779975
Switchboard +44 (0)1892 518000
Fax +44 (0)1892 518100
www.jiskoot.com < http://www.jiskoot.com/>
-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: 07 January 2005 16:39
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
If the filters upon which there are keys in the table index, in particular
the master table, are done first then it is a LOT faster because of where
and how the records are selected (in the index structure versus in the
data). Because every table has the "company" fieed and it is in virtually
every index if you do not include it as a filter item then selection is not
done via the indexes but rather as a slow record complex by record complex
comparison process. If possible you should always place any filters that
are related to one of the table indexes in the same order as the index - as
much as possible anyway - this way it can directly and random access the
tables. If company is not the first filter then this setup is blown right
away.
I have had reports that took 30 minutes to run improve to 30 seconds just by
re-ordering the filters. I suspect the same info works for any Progress
table access so probably for queries and SQL type selections - not just RB.
Mark Pladson at Epicor did a great session at the user conference in 1999 on
RB Performance Optimization (pre-Perspectives days). #1,2,3 tips: Company
Filter First, Join in same order as indexes, Use all the common fields in
indexes. He published one test where adding the company filter first
reduced the time to access records from 20 minutes to 6 seconds for the
first report page.
-Todd C.
-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Friday, January 07, 2005 7:32 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
I guess I never really thought of this before but does do order make any
difference?
Jasper
-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Thursday, January 06, 2005 10:19 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
I've never found a way to re-order existing filters. In the early days
before I knew better I often had the Company filter not as the first filter
(a big performance no-no). When I run across them now it is always Delete
the one down in the middle of the filters & Insert a new one at the top. If
you are just adding a new one you can Append to place it at the bottom
instead of inserting above the current last one (or whichever is currently
selected).
-Todd C.
-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Wednesday, January 05, 2005 3:37 PM
To: Vantage Groups (E-mail)
Subject: [Vantage] Report Builder Filter
Is it possible to manually adust the order of a report filter? I know you
can click on a filter and add one above it but what if you want the new
filter criteria in a different location? Do you need to just delete and add
or can you alter the order?
Thanks,
Jasper
[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/.
< 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>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
< 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/ < 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/.
< 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>
Yahoo! Groups Links
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>
Yahoo! Groups Sponsor
ADVERTISEMENT
<
http://us.ard.yahoo.com/SIG=129hj3ndk/M=298184.5639630.6699735.3001176/D=gro
ups/S=1705007183:HM/EXP=1105191134/A=2495202/R=0/SIG=11evjk50a/* http://www.n
etflix.com/Default?mqso=60188913
< http://us.ard.yahoo.com/SIG=129hj3ndk/M=298184.5639630.6699735.3001176/D=gr
oups/S=1705007183:HM/EXP=1105191134/A=2495202/R=0/SIG=11evjk50a/* http://www.
netflix.com/Default?mqso=60188913> > click here
<
http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=groups/S=:
HM/A=2495202/rand=794227828
< http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=groups/S=
:HM/A=2495202/rand=794227828> >
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
< 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/ < 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/.
< 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>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
< 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/> .
----------
On-Line API/ISO custody transfer sampling standards tutorial
Click the following link - http://www.jiskoot.com/NetsiteCMS.php?pageid=18
This email transmission is confidential and intended solely for the person
or organisation to whom it is addressed. If you are not the intended
recipient, you must not copy, distribute or disseminate the information, or
take any action in reliance of it.Any views expressed in this message are
those of the individual sender, except where the sender specifically states
them to be the views of any organisation or employer. If you have received
this message in error, do not open any attachment but please notify the
sender (above) deleting this message from your system. Please rely on your
own virus check no responsibility is taken by the sender for any damage
rising out of any bug or virus infection.
This message has been scanned by F-Secure AntiVirus for mail.
[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
_____
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]
Then all other table linking should ALWAYS include the Company as the first field linked - plus use as many fields as possible in the linking as are part of one of the table indexes. If possible find an index where you can use EVERY field in it. If there is a choice of indexes then use the one that is closest to the sort order you need.
So the answer to your first question is YES to both.
Another filter order tip I forgot was to place any filters on table index fields next after company so as to reduce the record complex size as quickly as possible right at the initial read level. Reduces a lot of work in the sort and aggregating phases.
I always thought that every table in Vantage started with Company as part of the primary index. A few have it as not the first field in the table but rather a little further down but it is definitely in the indexes. If you have a specific table with no company field let me know which it is.
-Todd C.
-----Original Message-----
From: Paul Whittle [mailto:pwhittle@...]
Sent: Monday, January 10, 2005 7:37 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
Todd.
Are you suggesting making the first filter company='Company ID', or Company
(in table 1) = Company on table 2?
I tried the first version on a BOM exception report and it still took 30
seconds to run!
Why don't some tables appear to have a valid company - at least not in the
report I tried (which used part rev, partmtl, plus part as two aliases)?
Best regards
Paul Whittle
Business Process (Quality) Manager
Jiskoot Autocontrol Ltd
Tunbridge Wells
Kent TN1 2DJ UK
Direct Line +44 (0)1892 779975
Switchboard +44 (0)1892 518000
Fax +44 (0)1892 518100
www.jiskoot.com < http://www.jiskoot.com/>
-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: 07 January 2005 16:39
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
If the filters upon which there are keys in the table index, in particular
the master table, are done first then it is a LOT faster because of where
and how the records are selected (in the index structure versus in the
data). Because every table has the "company" fieed and it is in virtually
every index if you do not include it as a filter item then selection is not
done via the indexes but rather as a slow record complex by record complex
comparison process. If possible you should always place any filters that
are related to one of the table indexes in the same order as the index - as
much as possible anyway - this way it can directly and random access the
tables. If company is not the first filter then this setup is blown right
away.
I have had reports that took 30 minutes to run improve to 30 seconds just by
re-ordering the filters. I suspect the same info works for any Progress
table access so probably for queries and SQL type selections - not just RB.
Mark Pladson at Epicor did a great session at the user conference in 1999 on
RB Performance Optimization (pre-Perspectives days). #1,2,3 tips: Company
Filter First, Join in same order as indexes, Use all the common fields in
indexes. He published one test where adding the company filter first
reduced the time to access records from 20 minutes to 6 seconds for the
first report page.
-Todd C.
-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Friday, January 07, 2005 7:32 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
I guess I never really thought of this before but does do order make any
difference?
Jasper
-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Thursday, January 06, 2005 10:19 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filter
I've never found a way to re-order existing filters. In the early days
before I knew better I often had the Company filter not as the first filter
(a big performance no-no). When I run across them now it is always Delete
the one down in the middle of the filters & Insert a new one at the top. If
you are just adding a new one you can Append to place it at the bottom
instead of inserting above the current last one (or whichever is currently
selected).
-Todd C.
-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Wednesday, January 05, 2005 3:37 PM
To: Vantage Groups (E-mail)
Subject: [Vantage] Report Builder Filter
Is it possible to manually adust the order of a report filter? I know you
can click on a filter and add one above it but what if you want the new
filter criteria in a different location? Do you need to just delete and add
or can you alter the order?
Thanks,
Jasper
[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/.
< 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>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
< 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/ < 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/.
< 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>
Yahoo! Groups Links
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>
Yahoo! Groups Sponsor
ADVERTISEMENT
<
http://us.ard.yahoo.com/SIG=129hj3ndk/M=298184.5639630.6699735.3001176/D=gro
ups/S=1705007183:HM/EXP=1105191134/A=2495202/R=0/SIG=11evjk50a/* http://www.n
etflix.com/Default?mqso=60188913
< http://us.ard.yahoo.com/SIG=129hj3ndk/M=298184.5639630.6699735.3001176/D=gr
oups/S=1705007183:HM/EXP=1105191134/A=2495202/R=0/SIG=11evjk50a/* http://www.
netflix.com/Default?mqso=60188913> > click here
<
http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=groups/S=:
HM/A=2495202/rand=794227828
< http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=groups/S=
:HM/A=2495202/rand=794227828> >
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
< 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/ < 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/.
< 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>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
< 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/> .
----------
On-Line API/ISO custody transfer sampling standards tutorial
Click the following link - http://www.jiskoot.com/NetsiteCMS.php?pageid=18
This email transmission is confidential and intended solely for the person
or organisation to whom it is addressed. If you are not the intended
recipient, you must not copy, distribute or disseminate the information, or
take any action in reliance of it.Any views expressed in this message are
those of the individual sender, except where the sender specifically states
them to be the views of any organisation or employer. If you have received
this message in error, do not open any attachment but please notify the
sender (above) deleting this message from your system. Please rely on your
own virus check no responsibility is taken by the sender for any damage
rising out of any bug or virus infection.
This message has been scanned by F-Secure AntiVirus for mail.
[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
_____
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]