Dynamics GP Blog

More than a few of our clients have asked for the ability to change the way a particular row- in a calculated column- totals. Since it’s confusing to explain… Here is a common example:
We are calculating gross profit % in a 12 month rolling income statement
Jan Feb March April YTD Total
Revenue $500 $500 $450 $650 $2,100

Labor and Materials 300 350 400 450 1500
Gross Profit 200 150 50 200 600

Gross Profit % 40.00% 30.00% 11.11% 30.77% 111.88%
You can see how the Total percentage in red is based on a total of the first 4 months, and not a calculation of (Total Rev-Total Cost) / Total Rev.
To get the correct percentage to show, here is what you need to do.

1) In the Column Layout

a. Instead of using a range for your Calc Formula use B+C+D+E..etc..+Y

2) In the Row Format

b. For your related rates column, for row 850 ‘Gross Profit %’, use a smilier forcing statement as below:
B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y=@850/@340, Z=Z850/Z340, AA=AA850/AA340 “###,##0.0%”

The above forcing statement is telling FRx to do the following:

i. Columns B,C,D,E….Y to use the formula @850/@340 for row 880.

ii. Column Z will take what is in column Z for row 850 and divide it by what is in column Z for row 340, and place it in column Z for row 880.

iii. The same will hold true for column AA as for column Z. It will take what is in column AA for row 850 and divide it by what is in column AA for 340 and place it in column ZZ for row 880.

3) In the Catalog

a. Since we are doing a calculation in the row that is based on a column we will have to change a report option to calculate the column first.

i. Go to Report Options | Advanced Tab

ii. Select the Calculation priority ‘Calculate columns first’

4) Generate the report to see if you are able to get the percent that you want in the ‘Gross Profit %’ row.

This entry was posted on Monday, August 24th, 2009 at 4:38 pm and is filed under FRx. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.