Combining like values from different fields

[Polarican0115]

Crystal 2011

I have a Sales Summary report that displays the following for 195 locations:

Whse Sales Cost Margin$ Margin %
001 55555 33333 22222 40.00%
002 55555 33333 22222 40.00%
003 55555 33333 22222 40.00%
004 88888 66666 22222 25.00%
005 55555 33333 22222 40.00%

The issue I am having is location 004 is a Central Distribution Center that does not report Sales and should not be a part of the report. Any sales for location 004 has a Subset of locations called the "Selling Whse". The revenue for each sale transacted through this CDC gets redistributed back to the Whse getting credit for the Sale.

What would be best way to accomplish combining the total for each whse with any total sale amount via this CDC (whse 004)? I need to display the following:

Whse Sales Cost Margin$ Margin %
001 88888 55555 33333 37.50%
002 66666 44444 22222 33.33%
003 88888 55555 33333 37.50%
005 66666 44444 22222 33.33%

Thank you for any help you could provide.

-PolaricanITSM

 

[pmax9999]

To be able to attribute WHSE "004" results to other WHSEs, you will need to be able to identify that other warehouse.

So, what data is available that indicates the Warehouse the results should be attributed to?

If, for example, there is another field (I have called it ACT_WHSE below), you could create a formula along these lines:

If	{Table.WHSE} = '004'
Then	{Table.ACT_WHSE}
Else	{Table.WHSE}

You would then Group and Sum based on this new formula rather than the WHSE field.

Feel free to provide more information on the structure of your data and I (or someone else) should be able to give you more specific instructions if this doesn't get you in the right direction.

Cheers
Pete

 

[Polarican0115]

Thank you pmax9999.

A Selling Warehouse is only required when an Order is created for Whse 004. The Selling Whse is set up with the same number convention as Whse. I created the formula and grouped and summed on it. the following is what displayed:
-------------------------------------------------------------------------------------------------
Whse Selling Whse Sales Cost Margin$ Margin%
001 Null xxxxx xxxxx xxxxx xx.xx%
002 Null xxxxx xxxxx xxxxx xx.xx%
003 Null xxxxx xxxxx xxxxx xx.xx%
004 001 xxxxx xxxxx xxxxx xx.xx%
002 xxxxx xxxxx xxxxx xx.xx%
003 xxxxx xxxxx xxxxx xx.xx%
005 xxxxx xxxxx xxxxx xx.xx%
006 xxxxx xxxxx xxxxx xx.xx%
005 Null xxxxx xxxxx xxxxx xx.xx%
006 Null xxxxx xxxxx xxxxx xx.xx%
007 Null xxxxx xxxxx xxxxx xx.xx%
-------------------------------------------------------------------------------------------------
The report will now also display the breakdown of sales by Selling Whse. What I need to display are the whse totals for each location (with the exception of 004) that includes the redistributed Sales Totals from the Selling Whse (Whse total + Selling Whse total when Whse = Selling Whse). If there are no Selling Whse Sales for a any one Whse, then simply display the Whse totals.

Any help on how I can get these totals to combine when Whse = Selling whse would be greatly appreciated.

Thank you,

Polarican0115

 

[Polarican0115]

** The formatting of the report example on the previous post was not clear, so I re-posted with the correction.

Thank you pmax9999.

A Selling Warehouse is only required when an Order is created for Whse 004. The Selling Whse is set up with the same number convention as Whse. I created the formula and grouped and summed on it. the following is what displayed:
-------------------------------------------------------------------------------------------------
Whse Selling Whse Sales Cost Margin$ Margin%
001 Null xxxxx xxxxx xxxxx xx.xx%
002 Null xxxxx xxxxx xxxxx xx.xx%
003 Null xxxxx xxxxx xxxxx xx.xx%
004 001 xxxxx xxxxx xxxxx xx.xx%
004 002 xxxxx xxxxx xxxxx xx.xx%
004 003 xxxxx xxxxx xxxxx xx.xx%
004 005 xxxxx xxxxx xxxxx xx.xx%
004 006 xxxxx xxxxx xxxxx xx.xx%
005 Null xxxxx xxxxx xxxxx xx.xx%
006 Null xxxxx xxxxx xxxxx xx.xx%
007 Null xxxxx xxxxx xxxxx xx.xx%
-------------------------------------------------------------------------------------------------
The report will now also display the breakdown of sales by Selling Whse. What I need to display are the whse totals for each location (with the exception of 004) that includes the redistributed Sales Totals from the Selling Whse (Whse total + Selling Whse total when Whse = Selling Whse). If there are no Selling Whse Sales for a any one Whse, then simply display the Whse totals.

Any help on how I can get these totals to combine when Whse = Selling whse would be greatly appreciated.

Thank you,

Polarican0115

 

[pmax9999]

Did you try my previous suggestion, which was to create a new formula that uses the WHSE field for all WHSEs other than "004" and the "Selling WHSE" if the WHSE is "004". Group and Sum on the new formula rather than the WHSE field.

If it doesn't give you the results you are looking for, please post back with the exact code for the formula you created (ie, copy and paste into the thread) details of what is wrong with the results (including sample data showing what you get and what you should get).

Cheers
Pete

 

[Polarican0115]

Yes, thank you. I tried what you suggested and got the result I mentioned in the previous post.

The formula I used (and did a group and sum on) was:

if {@Whse} = '037' then {@Selling Whse} else
{@Whse}

What I can't figure out is how to get totals to combine when (whse = Selling Whse).

Creating a Group and Sum on this formula resulted in the following:

(Whse) (Selling Whse) (Sales)
001 Null xxxxx
002 Null xxxxx
003 Null xxxxx
004 001 xxxxx
004 002 xxxxx
004 003 xxxxx
004 005 xxxxx
004 006 xxxxx
005 Null xxxxx
006 Null xxxxx
007 Null xxxxx

 

[pmax9999]

Please post the code for {@Whse} and {@Selling Whse}.

Pete

 

[Polarican0115]

Thank you pmax9999

//@whse
if {orderlines.whse} in [ '001', '001p', '002', '011', '019'] then "001 Linden" else
if {orderlines.whse} in [ '003', '004', '009', '017', '025', '031', '090', '092', '045' ] then "003 Houston" else
if {orderlines.whse} in [ '005' ] then "005 Turtle Energy" else
if {orderlines.whse} in [ '007', '007p', '035' ] then "007 Bridgewater" else
if {orderlines.whse} in [ '013', '013a', '013b', '013c', '013d', '013e', '013f', '013g' ] then "013 THIS Hamden" else
if {orderlines.whse} in [ '015' ] then "015 THIS Puerto Rico" else
if {orderlines.whse} in [ '021', '069' ] then "021 New Haven" else
if {orderlines.whse} in [ '023', '023c' ] then "023 Plainfield" else
if {orderlines.whse} in [ '027' ] then "027 Pleasantville" else
if {orderlines.whse} in [ '029' ] then "029 LIC" else
if {orderlines.whse} in [ '033', '033l' ] then "033 Whippany" else
if {orderlines.whse} in [ '037' ] then "037 CDC" else
if {orderlines.whse} in [ '039' ] then "039 THIS BWAY Cincinnati" else
if {orderlines.whse} in [ '041' ] then "041 THIS BWAY Chicago" else
if {orderlines.whse} in [ '042', '044', '046' ] then "042 MIE-TH HOLDING" else
if {orderlines.whse} in [ '043' ] then "043 THIS BWAY Homerville" else
if {orderlines.whse} in [ '047', '047a', '047b' ] then "047 THIS Manati" else
if {orderlines.whse} in [ '009', '051', '053', '055', '057', '065' ] then "051 TNH Industrial" else
if {orderlines.whse} in [ '059' ] then "059 T&H Green Energy" else
if {orderlines.whse} in [ '061' ] then "061 Richards" else
if {orderlines.whse} in [ '063', '063a', '063b', '063c', '063d', '063e', '063f', '063g', '063h', '063i', '063j', '063k', '063l', '063m' ] then "063 THIS Sharebuyer" else
if {orderlines.whse} in [ '067' ] then "067 THIS Stryker Mahwah" else
if {orderlines.whse} in [ '071' ] then "071 THIS Honeywell CPG" else
if {orderlines.whse} in [ '073' ] then "073 THIS Honeywell Greenville" else
if {orderlines.whse} in [ '075' ] then "075 THIS Fostoria" else
if {orderlines.whse} in [ '077' ] then "077 THIS HW Elberton" else
if {orderlines.whse} in [ '079' ] then "079 THIS HW Sharebuyer" else
if {orderlines.whse} in [ '081' ] then "081 THIS J&J Lilitz" else
if {orderlines.whse} in [ '083' ] then "083 THIS HW Duncan" else
if {orderlines.whse} in [ '085' ] then "085 THIS Tect Newington" else
if {orderlines.whse} in [ '087' ] then "087 THIS Tect Thomasville" else
if {orderlines.whse} in [ '089' ] then "089 THIS Tect Whitesboro" else
if {orderlines.whse} in [ '091' ] then "091 THIS Tect Cleveland" else
if {orderlines.whse} in [ '093' ] then "093 THIS Unilever Clinton" else
if {orderlines.whse} in [ '095' ] then "095 THIS Unilever Hammond" else
if {orderlines.whse} in [ '097' ] then "097 THIS DSM" else
if {orderlines.whse} in [ '099' ] then "099 THIS EGS Appleton" else
if {orderlines.whse} in [ '101' ] then "101 THIS EGS Shoemakersville" else
if {orderlines.whse} in [ '103' ] then "103 THIS EGS Mexico" else
if {orderlines.whse} in [ '105', '105a', '105b' ] then "105 THIS HW Golden Valley" else
if {orderlines.whse} in [ '107' ] then "107 THIS HW Freeport" else
if {orderlines.whse} in [ '109' ] then "109 THIS HW BDG" else
if {orderlines.whse} in [ '110' ] then "110 THIS ATS" else
if {orderlines.whse} in [ '111' ] then "111 THIS J&J North Wales" else
if {orderlines.whse} in [ '113' ] then "113 THIS Alza Corp" else
if {orderlines.whse} in [ '115' ] then "115 THIS Akzo La Porte" else
if {orderlines.whse} in [ '117' ] then "117 THIS Akzo Pasadena" else
if {orderlines.whse} in [ '119' ] then "119 WGI" else
if {orderlines.whse} in [ '121' ] then "121 THIS Vistakon" else
if {orderlines.whse} in [ '122' ] then "122 THIS J&J Depuy Raynham" else
if {orderlines.whse} in [ '123' ] then "123 THIS Mcneil" else
if {orderlines.whse} in [ '124' ] then "124 THIS J&J Depuy Warsaw" else
if {orderlines.whse} in [ '125' ] then "125 THIS Ethicon Cornelia" else
if {orderlines.whse} in [ '127' ] then "127 THIS Ethicon San Angel" else
if {orderlines.whse} in [ '129' ] then "129 THIS Neutrogena" else
if {orderlines.whse} in [ '131' ] then "131 THIS J&J Bridgewater" else
if {orderlines.whse} in [ '133' ] then "133 THIS Merck Rahway" else
if {orderlines.whse} in [ '135' ] then "135 THIS Merck West Point" else
if {orderlines.whse} in [ '137' ] then "137 THIS Merck Barceloneta" else
if {orderlines.whse} in [ '139' ] then "139 THIS Merck" else
if {orderlines.whse} in [ '141' ] then "141 THIS Merck Elton" else
if {orderlines.whse} in [ '143' ] then "143 THIS Merck Durham" else
if {orderlines.whse} in [ '145' ] then "145 THIS Merck Wilson" else
if {orderlines.whse} in [ '147' ] then "147 THIS Janssen" else
if {orderlines.whse} in [ '149' ] then "149 THIS Janicki Hamilton" else
if {orderlines.whse} in [ '151' ] then "151 THIS Janicki Layton" else
if {orderlines.whse} in [ '153' ] then "153 THIS KIK Elkhart" else
if {orderlines.whse} in [ '155' ] then "155 THIS KIK Gainesville" else
if {orderlines.whse} in [ '157' ] then "157 THIS KIK Etobicoke" else
if {orderlines.whse} in [ '159' ] then "159 THIS KIK Danville" else
if {orderlines.whse} in [ '161' ] then "161 THIS KIK Memphis" else
if {orderlines.whse} in [ '163' ] then "163 THIS J&J Raritan" else
if {orderlines.whse} in [ '165' ] then "165 THIS J&J Rochester" else
if {orderlines.whse} in [ '167' ] then "167 THIS J&J Ariba" else
if {orderlines.whse} in [ '169' ] then "169 THIS Ampac" else
if {orderlines.whse} in [ '171' ] then "171 THIS Hitchner" else
if {orderlines.whse} in [ '173' ] then "173 THIS J&J Montreal" else
if {orderlines.whse} in [ '191' ] then "191 TSI" else
if {orderlines.whse} in [ '193' ] then "193 Rhino" else
if {orderlines.whse} in [ '194' ] then "194 T&H Ontario" else
if {orderlines.whse} in [ '195' ] then "195 Mag-Trol" else
{orderlines.whse}

//@Selling Whse
//if {orderlines.whse} <> '037' then {@Whse} else
//if {orders_ext.User1} = " " then "No Selling Whse" else
if {orders_ext.User1} in [ '001', '001p', '002', '011', '019'] then "001 Linden" else
if {orders_ext.User1} in [ '003', '004', '009', '017', '025', '031', '090', '092', '045' ] then "003 Houston" else
if {orders_ext.User1} in [ '005' ] then "005 Turtle Energy" else
if {orders_ext.User1} in [ '007', '007p', '035' ] then "007 Bridgewater" else
if {orders_ext.User1} in [ '013', '013a', '013b', '013c', '013d', '013e', '013f', '013g' ] then "013 THIS Hamden" else
if {orders_ext.User1} in [ '015' ] then "015 THIS Puerto Rico" else
if {orders_ext.User1} in [ '021', '069' ] then "021 New Haven" else
if {orders_ext.User1} in [ '023', '023c' ] then "023 Plainfield" else
if {orders_ext.User1} in [ '027' ] then "027 Pleasantville" else
if {orders_ext.User1} in [ '029' ] then "029 LIC" else
if {orders_ext.User1} in [ '033', '033l' ] then "033 Whippany" else
if {orders_ext.User1} in [ '037' ] then "037 CDC" else
if {orders_ext.User1} in [ '039' ] then "039 THIS BWAY Cincinnati" else
if {orders_ext.User1} in [ '041' ] then "041 THIS BWAY Chicago" else
if {orders_ext.User1} in [ '042', '044', '046' ] then "042 MIE-TH HOLDING" else
if {orders_ext.User1} in [ '043' ] then "043 THIS BWAY Homerville" else
if {orders_ext.User1} in [ '047', '047a', '047b' ] then "047 THIS Manati" else
if {orders_ext.User1} in [ '009', '051', '053', '055', '057', '065' ] then "051 TNH Industrial" else
if {orders_ext.User1} in [ '059' ] then "059 T&H Green Energy" else
if {orders_ext.User1} in [ '061' ] then "061 Richards" else
if {orders_ext.User1} in [ '063', '063a', '063b', '063c', '063d', '063e', '063f', '063g', '063h', '063i', '063j', '063k', '063l', '063m' ] then "063 THIS Sharebuyer" else
if {orders_ext.User1} in [ '067' ] then "067 THIS Stryker Mahwah" else
if {orders_ext.User1} in [ '071' ] then "071 THIS Honeywell CPG" else
if {orders_ext.User1} in [ '073' ] then "073 THIS Honeywell Greenville" else
if {orders_ext.User1} in [ '075' ] then "075 THIS Fostoria" else
if {orders_ext.User1} in [ '077' ] then "077 THIS HW Elberton" else
if {orders_ext.User1} in [ '079' ] then "079 THIS HW Sharebuyer" else
if {orders_ext.User1} in [ '081' ] then "081 THIS J&J Lilitz" else
if {orders_ext.User1} in [ '083' ] then "083 THIS HW Duncan" else
if {orders_ext.User1} in [ '085' ] then "085 THIS Tect Newington" else
if {orders_ext.User1} in [ '087' ] then "087 THIS Tect Thomasville" else
if {orders_ext.User1} in [ '089' ] then "089 THIS Tect Whitesboro" else
if {orders_ext.User1} in [ '091' ] then "091 THIS Tect Cleveland" else
if {orders_ext.User1} in [ '093' ] then "093 THIS Unilever Clinton" else
if {orders_ext.User1} in [ '095' ] then "095 THIS Unilever Hammond" else
if {orders_ext.User1} in [ '097' ] then "097 THIS DSM" else
if {orders_ext.User1} in [ '099' ] then "099 THIS EGS Appleton" else
if {orders_ext.User1} in [ '101' ] then "101 THIS EGS Shoemakersville" else
if {orders_ext.User1} in [ '103' ] then "103 THIS EGS Mexico" else
if {orders_ext.User1} in [ '105', '105a', '105b' ] then "105 THIS HW Golden Valley" else
if {orders_ext.User1} in [ '107' ] then "107 THIS HW Freeport" else
if {orders_ext.User1} in [ '109' ] then "109 THIS HW BDG" else
if {orders_ext.User1} in [ '110' ] then "110 THIS ATS" else
if {orders_ext.User1} in [ '111' ] then "111 THIS J&J North Wales" else
if {orders_ext.User1} in [ '113' ] then "113 THIS Alza Corp" else
if {orders_ext.User1} in [ '115' ] then "115 THIS Akzo La Porte" else
if {orders_ext.User1} in [ '117' ] then "117 THIS Akzo Pasadena" else
if {orders_ext.User1} in [ '119' ] then "119 WGI" else
if {orders_ext.User1} in [ '121' ] then "121 THIS Vistakon" else
if {orders_ext.User1} in [ '122' ] then "122 THIS J&J Depuy Raynham" else
if {orders_ext.User1} in [ '123' ] then "123 THIS Mcneil" else
if {orders_ext.User1} in [ '124' ] then "124 THIS J&J Depuy Warsaw" else
if {orders_ext.User1} in [ '125' ] then "125 THIS Ethicon Cornelia" else
if {orders_ext.User1} in [ '127' ] then "127 THIS Ethicon San Angel" else
if {orders_ext.User1} in [ '129' ] then "129 THIS Neutrogena" else
if {orders_ext.User1} in [ '131' ] then "131 THIS J&J Bridgewater" else
if {orders_ext.User1} in [ '133' ] then "133 THIS Merck Rahway" else
if {orders_ext.User1} in [ '135' ] then "135 THIS Merck West Point" else
if {orders_ext.User1} in [ '137' ] then "137 THIS Merck Barceloneta" else
if {orders_ext.User1} in [ '139' ] then "139 THIS Merck" else
if {orders_ext.User1} in [ '141' ] then "141 THIS Merck Elton" else
if {orders_ext.User1} in [ '143' ] then "143 THIS Merck Durham" else
if {orders_ext.User1} in [ '145' ] then "145 THIS Merck Wilson" else
if {orders_ext.User1} in [ '147' ] then "147 THIS Janssen" else
if {orders_ext.User1} in [ '149' ] then "149 THIS Janicki Hamilton" else
if {orders_ext.User1} in [ '151' ] then "151 THIS Janicki Layton" else
if {orders_ext.User1} in [ '153' ] then "153 THIS KIK Elkhart" else
if {orders_ext.User1} in [ '155' ] then "155 THIS KIK Gainesville" else
if {orders_ext.User1} in [ '157' ] then "157 THIS KIK Etobicoke" else
if {orders_ext.User1} in [ '159' ] then "159 THIS KIK Danville" else
if {orders_ext.User1} in [ '161' ] then "161 THIS KIK Memphis" else
if {orders_ext.User1} in [ '163' ] then "163 THIS J&J Raritan" else
if {orders_ext.User1} in [ '165' ] then "165 THIS J&J Rochester" else
if {orders_ext.User1} in [ '167' ] then "167 THIS J&J Ariba" else
if {orders_ext.User1} in [ '169' ] then "169 THIS Ampac" else
if {orders_ext.User1} in [ '171' ] then "171 THIS Hitchner" else
if {orders_ext.User1} in [ '173' ] then "173 THIS J&J Montreal" else
if {orders_ext.User1} in [ '191' ] then "191 TSI" else
if {orders_ext.User1} in [ '193' ] then "193 Rhino" else
if {orders_ext.User1} in [ '194' ] then "194 T&H Ontario" else
if {orders_ext.User1} in [ '195' ] then "195 Mag-Trol" else
{orders_ext.User1}

The CDC (location 037) needs to have its Sales Totals for each Selling Whse redistributed back to the associated Whse.

Thank you for your assistance.

Polarican0115

 

[pmax9999]

Based on these formulas, my earlier suggested approach should work.

One small change is to deal with nulls in the {@Selling Whse} formula. Add the following line to the beginning of your formula:

If Isnull({orders_ext.User1}) Then "NA" Else
If Trim({orders_ext.User1}) = "" Then "NA" Else
...

Then, assuming the report is Grouped and Summed on the new formula it should work.

In the sample data you provided, you do not show the new formula. Are you sure you have grouped on the new formula? Please explain the report structure in detail, ie groups, and the sections that data is being displayed in.

Cheers
Pete

 

[Polarican0115]

Thank you Pete, that was extremely helpful! I really appreciate it.