Calculation Help

["... you mean these fans?"]

Hi,

 

I have a client that wants to see where their Commodity is gong each month; By Country / By Commodity.  So based on their List I created a Destination Table to hold this list of destination and to add additional destinations if they need to.  Same with there commodity; I created a Commodity Table.  However, I had to hard code the calculation so the summaries would work in their reports.  

 

How can I correct this so I don't have to manually update this calculation each time they add a Destination, or Commodity ( Category ).  And then will the Summaries be able to distinguish the differences ??

 

 

If ( Destination = "Belgium" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Brazil" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "China" and Category = "Copper" ; Price Cost Total + Price Margin Total  ;

If ( Destination = "Germany" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Greece" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Hong Kong" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "India" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Indonesia" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Korea" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Mexico" and Category = "Copper" ; Price Cost Total + Price Margin Total  ;

If ( Destination = "Pakistan" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Spain" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "Taiwan" and Category = "Copper" ; Price Cost Total + Price Margin Total  ;

If ( Destination = "Thailand" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "The Netherlands" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

If ( Destination = "UK" and Category = "Copper" ; Price Cost Total + Price Margin Total;

If ( Destination = "USA" and Category = "Copper" ; Price Cost Total + Price Margin Total ;

 

 "" ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) )

 

Any assistance in this matter I would be grateful.

 

Thank you.

 

Tom :-)

[Wim Decorte]

Store these things in their own table so that you can find the correct record to know what formula to apply.

 

As an aside; for this type of calc you would not use nested IFs but the Case function.

[jbante]

It sounds like what you're looking for is a sub-summary report, not necessarily a calculation. The calculation you do have looks like the result is "Price Cost Total + Price Margin Total" regardless of the combination of country and category; make a calculation field from just that, then make a summary field based on that calculation. For the report, make a list-view layout with a summary layout part that appears when sorted by commodity, and put the summary field in that summary body part (in addition to the country and commodity fields). Make a script that sorts by country, then commodity. When a found set of records is sorted like this, the summary field will summarize the data within each country/commodity group.

["... you mean these fans?"]

Hi jbante

 

I have Sub Summaries.  What happened was that I placed the "Categories" vertical.  This was a requirement from the customer; all their reporting were from Excel Docs.  I tried the table view but I have yet to find a useful display to a client in Table View.

 

Please see .pic

 

To place the value for each vertical category, I had to create the above calculations.  The customer really like these reports I created for them.  They had Excel style worksheets and wanted to keep thing similar and one report they request it to be exactly like the Excel.

 

At to the nested "IF"; I agree.  It is an old habit that I have yet to break.

 

Anyway, any assistance for the above issue I would be grateful.

 

Thank you.

 

Tom

 

 

[jbante]

What you're trying to do is called a "cross-tab" report. If you search the forums for that, you should be able to find a couple different techniques you might be able to use.