On Fri, Aug 7, 2015 at 2:17 PM, 'Cathy' cathy@... [vantage] <vantage@yahoogroups.com> wrote:Â<div> <p></p><div><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:11.0pt;color:#1f497d;">IÂ am not that technical and I know this is an Example and I might not be understanding your problem.<u></u><u></u></span></p><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:11.0pt;color:#1f497d;"><u></u>Â <u></u></span></p><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:11.0pt;color:#1f497d;">In the AR Aging RDD, Customer is a linked table under Report Table > Linked Table > Pick links.<u></u><u></u></span></p><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:11.0pt;color:#1f497d;"><u></u>Â <u></u></span></p><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:11.0pt;color:#1f497d;">It is getting BTName, CustID and name from the Customer table already.<u></u><u></u></span></p><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:11.0pt;color:#1f497d;"><u></u>Â <u></u></span></p><div><div style="border-top:solid #b5c4df 1.0pt;padding:3.0pt 0in 0in 0in;"><p class="ygrps-yiv-1264612066MsoNormal"><b><span style="font-size:10.0pt;">From:</span></b><span style="font-size:10.0pt;"> <a rel="nofollow" target="_blank" href="mailto:vantage@yahoogroups.com">vantage@yahoogroups.com</a> [mailto:<a rel="nofollow" target="_blank" href="mailto:vantage@yahoogroups.com">vantage@yahoogroups.com</a>] <br><b>Sent:</b> Thursday, August 06, 2015 8:31 AM<span><br><b>To:</b> Vantage<br><b>Subject:</b> [Vantage] E10 RDD CROSS JOIN ISSUE<u></u><u></u></span></span></p></div></div><p class="ygrps-yiv-1264612066MsoNormal"><u></u>Â <u></u></p><p class="ygrps-yiv-1264612066MsoNormal">Â <u></u><u></u></p><div><div><div><div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>Some of you may have already run across this but I wanted to alert you to a "BUG" (Epicor might call it a feature) that we found in E10 10.0.700.4<u></u><u></u></span></p></div><div><div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>When you customize an RDD and you add your own table. For example adding Customer to your ARAging report. You add the table and do your relationship like normal. When you run the report Epicor will generate your Standard ARAging_GUID tables as well as a new table Customer_GUID.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>There issue here becomes that you will get one record in the Customer_GUID table per Invoice, regardless of whether that Customer record already exists in the Customer_GUID table.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>What happens very quickly is that you end up with the equivalent of a cross join. If you are to run this report for a customer that has 1000 Invoices, you'll be putting 1000 copies of that exact same customer record in the Customer_GUID table.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>Then when you do your join in SSRS you'll end up with a CROSS join equivalent of 1000 x 1000 records since Company, CustNum will match 1000 records per InvcHead record. This can quickly become an issue if you go and add say Terms, Parts and other tables to your report. For each table you add you increase the number of records in your final query exponentially.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>This morning I ran an ARAging report with 71 rows initially that ended up with 630K rows in the end. No amount of Group By was able to fix the issue and the report took for ever to run.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>I was able to get around it by selecting distinct from each table before I did the join as shown below.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>SELECT T1.Company, T1.InvoiceNum, T3.CustomerName<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>FROM ARPrnt_0a5faf63-d424-4216-b269-60e8a30c5c3c T1<br>INNER JOIN (SELECT DISTINCT * FROM Customer_0a5faf63-d424-4216-b269-60e8a30c5c3c) T2 ON T1.Company = T2.Company AND T1.CustNum = T2.CustNum<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>Just thought it may be useful info.<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span>And I am sure it is fixed in 10.1 *GRIN*<u></u><u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><u></u>Â <u></u></span></p></div><div><div><div><div><p class="ygrps-yiv-1264612066MsoNormal"><span><br><b><span style="color:#333333;">Jose C Gomez</span></b></span><u></u><u></u></p></div><div><p class="ygrps-yiv-1264612066MsoNormal" style="margin-bottom:12.0pt;"><b><span style="font-size:7.5pt;color:#666666;">Software Engineer</span></b><u></u><u></u></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><b><span style="color:#666666;"><br></span></b><span style="font-size:7.5pt;color:#ff6600;">T: </span><span style="font-size:7.5pt;"><a rel="nofollow">904.469.1524</a> mobile</span><u></u><u></u></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span style="font-size:7.5pt;color:#ff6600;">E:</span><span style="font-size:7.5pt;"> <a rel="nofollow" target="_blank" href="mailto:jose@...">jose@...</a><br><a rel="nofollow" target="_blank" href="http://www.josecgomez.com"><span style="color:#ff6600;">http://www.josecgomez.com</span></a><br><a rel="nofollow" target="_blank" href="http://www.linkedin.com/in/josecgomez"><span style="font-size:12.0pt;text-decoration:none;"><img border="0" src="http://www.josecgomez.com/images/linkedin.png"></span></a>Â <a rel="nofollow" target="_blank" href="http://www.facebook.com/josegomez"><span style="font-size:12.0pt;text-decoration:none;"><img border="0" src="http://www.josecgomez.com/images/facebook.png"></span></a>Â </span><a rel="nofollow" target="_blank" href="http://www.google.com/profiles/jose.gomez"><span style="text-decoration:none;"><img border="0" src="http://www.josecgomez.com/images/google.png"></span></a>Â <a rel="nofollow" target="_blank" href="http://www.twitter.com/joc85"><span style="text-decoration:none;"><img border="0" src="http://www.josecgomez.com/images/twitter.png"></span></a>Â <a rel="nofollow" target="_blank" href="http://www.josecgomez.com/professional-resume/"><span style="text-decoration:none;"><img border="0" src="http://www.josecgomez.com/images/wp.png"></span></a>Â <span><a rel="nofollow" target="_blank" href="http://www.josecgomez.com/feed/"><span style="text-decoration:none;"><img border="0" src="http://www.josecgomez.com/images/rss.png"></span></a>Â </span><u></u><u></u></p></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span lang="LA" style="color:black;"><br></span><b><i><span lang="LA" style="font-size:7.5pt;color:black;">Quis custodiet ipsos custodes?</span></i></b><u></u><u></u></p></div></div></div></div></div></div></div></div><div><p class="ygrps-yiv-1264612066MsoNormal"><span style="color:white;"><u></u><u></u></span></p></div></div></div></div><p></p> </div> <div style="color:#fff;min-height:0;"></div>
INNER JOIN (SELECT DISTINCT * FROM Customer_0a5faf63-d424-4216-b269-60e8a30c5c3c) T2 ON T1.Company = T2.Company AND T1.CustNum = T2.CustNum
Jose C Gomez
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?
IÂ am not that technical and I know this is an Example and I might not be understanding your problem.
In the AR Aging RDD, Customer is a linked table under Report Table > Linked Table > Pick links.
It is getting BTName, CustID and name from the Customer table already.
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, August 06, 2015 8:31 AM
To: Vantage
Subject: [Vantage] E10 RDD CROSS JOIN ISSUE
Some of you may have already run across this but I wanted to alert you to a "BUG" (Epicor might call it a feature) that we found in E10 10.0.700.4
When you customize an RDD and you add your own table. For example adding Customer to your ARAging report. You add the table and do your relationship like normal. When you run the report Epicor will generate your Standard ARAging_GUID tables as well as a new table Customer_GUID.
There issue here becomes that you will get one record in the Customer_GUID table per Invoice, regardless of whether that Customer record already exists in the Customer_GUID table.
What happens very quickly is that you end up with the equivalent of a cross join. If you are to run this report for a customer that has 1000 Invoices, you'll be putting 1000 copies of that exact same customer record in the Customer_GUID table.
Then when you do your join in SSRS you'll end up with a CROSS join equivalent of 1000 x 1000 records since Company, CustNum will match 1000 records per InvcHead record. This can quickly become an issue if you go and add say Terms, Parts and other tables to your report. For each table you add you increase the number of records in your final query exponentially.
This morning I ran an ARAging report with 71 rows initially that ended up with 630K rows in the end. No amount of Group By was able to fix the issue and the report took for ever to run.
I was able to get around it by selecting distinct from each table before I did the join as shown below.
SELECT T1.Company, T1.InvoiceNum, T3.CustomerName
FROM ARPrnt_0a5faf63-d424-4216-b269-60e8a30c5c3c T1
INNER JOIN (SELECT DISTINCT * FROM Customer_0a5faf63-d424-4216-b269-60e8a30c5c3c) T2 ON T1.Company = T2.Company AND T1.CustNum = T2.CustNum
Just thought it may be useful info.
And I am sure it is fixed in 10.1 *GRIN*
Jose C Gomez
Software Engineer
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?