Sorting on a formula

[msanders67]

I have a report wherein I need to sort on something other than a group summary field, group field, or database field.

My report retrieves MTD and YTD sales for customers. I have a group that is based on customer_display_name, which is a field in the same db file with the MTD and YTD figures. I must have the group because I have some customer accounts that I need to combine for a YTD sales total.

example: Smith International - California
MTD: 2,500.00 YTD: 10,000.00
Smith International - New York
MTD: 8,000.00 YTD: 30,000.00
will be combined and be displayed as:
Smith International
MTD: 10,500.00 YTD: 40,000.00

This all works fine, except that I also have some sales figures from an old accounting system that I need to add to the YTD sales for some customer_display_names in the group (not all). I then need to sort based on that calculated total in descending order and do a topN sort.

Any ideas?

TIA,
Margo

 

[IdoMillet]

Option 1 (requires that both data sources are in one DBMS).
-----------------------------------------------
Use a UNION statement in Crystal SQL Designer
as the data source or create the full UNION query as a VIEW in the database itself ("Query" in MS Access), which is treated as a Table by Crystral.

The UNION statement can combine the rows from one SELECT with the rows from another SELECT.

Option 2 (the two data sources DON'T need to be in one DBMS)
-----------------------------------------------------------
Use a linked subreport in the group header of each combined account to get at the related data in the old source system. Pass the figures to the main report via Shared Variables and use them in the Group Footer to compute a proper total for each group.

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[msanders67]

Thank your for your suggestions Ido. I'll give the second one a try first as my db and CR are on a hosted apps site w/o Access access....

Thanks for your help,
Margo