Sage 100

I just designed a pretty and complex ""Monthly Sales by Salesperson"" report for a client, grouping by the AR_Customer.SalespersonNo field and then the client tells me they want to see the sales totals by all of the split salespeople assigned to the account. The standard Salesperson Commission report has built-in logic that groups by all salespeople associated with the invoice but I have to try to figure out how to perform this same grouping on my custom so that I can use the Customer Salesperson assignments. Has anyone ever been successful with writing a custom report that would group by 2 or more of the ""SalespersonNoX"" fields when they exist, or can think of a formula for me to try? Thanks in advance!

I'd either push the complex join logic into SQL Views (if that is an option) or use sub-reports and global variables.

What tables are you using in your custom report?

Ah! The classic multiple LOJ (Left Outer Join) deficiency with Sage's ODBC Driver. Similar to @KevinMoyes's suggestion would be the technique of using pass through queries (MS Access) to the Sage ODBC to create a simple view of AR History with links to multiple copies of the AR_SalespersonCommission table. This can be done without requiring MS Access on the end user's workstation.

Ugh - I was hoping you guys had an easier answer :) I will see if the client wants to approve the time for subreports since I'm not familiar with the SQL/Access routes.

running totals are a pretty powerful way to get summary information. Did you look at that?

Yes, I'm considering that. I just have a lot of formulas and running totals already and was seeing if there was a way to group by one or more of the salesperson number fields rather than having to resort to subreports and possibly manual running totals which are extremely annoying.

Get client to approve subbing it out to @DanBurleson . Great way to learn a new trick.

@JeffSchwenk - instead of saying ""Access"" I should just say ""Window's built-in database functionality"" or Jet or ACE. @AmberPrayfrock - One doesn't have to know (or even have) Access to use the attached pass through query database. It includes simple pass through queries for all MAS90 tables of the basic distribution modules (intentionally excluding work tables). To use it from Crystal's ""Data Expert"" , just select ""Create New Connection"" then use ""Access/Excel (DAO)""/""Make New Connection"" to select the attached file and pick tables just like you would with SOTAMAS90. Select the commission table 5 times with left outer joins.

@DanBurleson this is worth the price of admission. Would you need to refresh links to pick up udf fields, if any?

You would just need to close the connection or the report and reopen to see any changes.

Thank you, Dan! I will look at your samples soon to make sure I understand and hopefully be able to do the same with the AR Customer and Invoice History tables. I appreciate your time!