Cannot utilize AR_CustomerSalesHistory. Need $0 Customer sales report for specific time frame. Parameter = i.e. Nov 1 2018 thru Nov 31 2019.
Thanks for your assistance.
This data should be in the AR_InvoiceHistoryHeader table. Group on a formula (division plus customer number, or just group on customer number if you are not using divisions).
Summarize the extended amount field for the customer group. Add a group selection formula (or filter on the group summary) to select groups (customers) with $0 sales. You may want to exclude deleted invoices, especially if your A/R options have ever been set to retain deleted invoices. Add a filter for your dates or use a parameter so the user can decide the date range upon run-time.
AR_Customer as the report's main table. Then use an outer join (or sub-report) to pull in the sales numbers, and suppress when the total is non-zero.
Using transaction data as the report's main table won't show customers with no transactions in the selected date range.
This data should be in the AR_InvoiceHistoryHeader table. Group on a formula (division plus customer number, or just group on customer number if you are not using divisions).
Summarize the extended amount field for the customer group. Add a group selection formula (or filter on the group summary) to select groups (customers) with $0 sales. You may want to exclude deleted invoices, especially if your A/R options have ever been set to retain deleted invoices. Add a filter for your dates or use a parameter so the user can decide the date range upon run-time.
Absolutely correct. An easier approach would be to create a sales amount formula like this:
if invoice date = your date range then extended amount else 0. Summarize the sales amount formula.
Don't filter on the date range. In fact, do not filter the report at all based on invoice date. This way, if a customer has at least one invoice in the history file, regardless of the date range, the customer will still show 0 sales.
That would work (99% of the time), but an unfiltered query of all invoice history can be really slow (depending on the number of rows to process). Crystal Reports can struggle processing really big data sets, which is why I filter record selection criteria as much as possible.
Of course in SQL it is a fairly trivial query...
I have used Crystal Reports for many years, and I have never experienced a problem where Crystal Reports struggled with 1,000,000 or even 2,000,000 records; especially with a report that only contains one table.
I am not on the server, but going through SOTOMAS90 connection so it does take forever in many instances.
Slow speeds in Crystal Reports is often caused by linking tables incorrectly. If the workstation you are using is old or does not have adequate memory, that could slow reports down as well.
Thanks.
*Community Hub is the new name for Sage City