Sage 100

Hi - having an issue with crystal reports for Sage 100 2015 standard. I have been asked to write a report that has the salesperson from AR invoice history, and the salesperson from Customer maintenance - so far so good. They want the salesperson's names also. So I have an equal join on the AR invoice history header, and can put the name column from ar_salesperson. Crystal has a feature that allows you to add the AR_salesperson table again (creating an alias, such as ar_salesperson_1) for this very purpose, but when I create the join to the alias from the customer fields, the query does not return any value. I think this may be a limitation of the pvx ODBC driver.... Has anyone run into this and know ? I was also thinking about an access pass-thru (the way that knowledgesync suggests) but would have to try it. This would be the same issue if you wanted to print the G/L account names for Sales and Cogs from SO_Order detail - two different joins into the same table via an alias. Thanks in advance...... Jeff

I've had a similar issue trying to use the same file twice, I believe I solved it by printing the Salesperson Name via a Sub-Report.

Thanks - I need to avoid the sub-report due to the strange behavior of crystal when exporting sub-reports to excel data only (blank lines etc.) - unless there is a solve for that one as well.

try exporting as a text file using a tab delimiter - tab separated text. I have had success with reports with subreports using that format. you can still bring into excel as tab delimited. also make sure the same fonts and size are in both main and sub reports.

Sub-report fix when exporting to Excel: pass the value out to the main report using a shared / global variable. Put the sub-report a section above where you call it. Hide the sub report's section with your source formula field (everything else should be suppressed), suppress blank sub report and suppress blank section. Remember to reset your shared variable after use so you don't get recycled values for the next call, if your sub-report has NULL results.

I don't have any issue connecting the AR_Salesperson table twice and populating the report with fields from the invoice linked table at the same time as the customer linked table. I suggest that you post your report.

Depending on many salespeople and how often they add new ones, I would create an 'if then' formula for the name and not link to the salesperson table at all.

FWIW - I used @KevinMoyes suggestion today with a report/sub report combo and it worked fine as long as I exported using the ""Excel (97 - 2003)"" format option. Any other format created blank rows. Neat trick, thanks @KevinMoyes.

Thanks - I agree, but the client really wants to know why they can't use xlsx data only - I guess this is a crystal ""feature/bug

Crystal is a free form report designer, and Excel has everything in cells. Structurally very different. The export to Excel is a best guess (fitting data into cells), and sub-reports are a known complication to how Crystal assigns output to cells. Feature / bug... it is what it is.

If the client wants the final report in Excel I will point them to Sage Intelligence. You can pretty much do the same thing as with Crystal but it's designed for Excel.

Jeff for J/C we were told to specifically create the standard reports with Excel (97 - 2003) as the basis for exporting to Excel, and to stay away from the Data Only format because that export driver has ""limited support"", IOW this subreport issue and other things that don't work with it.