<div>
I think what you really want are 2 formulas like<br />
RevenueOnly<br />
If {GL_ACCT.ACCT_TYPE}=&quot;reven<wbr />ue&quot; then <br />
&nbsp; &nbsp; {GL_BAL.ENDING_BAL}<br />
else <br />
&nbsp; &nbsp; 0<br />
<br />
ExpenseOnly<br />
If {GL_ACCT.ACCT_TYPE}=&quot;expen<wbr />se&quot; then <br />
&nbsp; &nbsp; {GL_BAL.ENDING_BAL}<br />
else <br />
&nbsp; &nbsp; 0<br />
<br />
<br />
You can then use summaries on them to get the totals.<br />
<br />
To subtract them<br />
<br />
Sum({@RevenueOnly}, {GL_ACCT.ACCT_TYPE}) - Sum({@ExpenseOnly}, {GL_ACCT.ACCT_TYPE}) <br />
<br />
mlmcc<br />

</div>


<div>
I have the two different formulas created. &nbsp;When I try do to the subtraction I get an error message that says &quot;The field cannot be summarized&quot; with @RevenueOnly highlighted
</div>


<div>
Did you use the formulas I provided?<br />
<br />
What formulas are you trying to ue?<br />
<br />
mlmcc
</div>


<div>
 If you use the RevenueOnly formula that mlmcc posted, you should not be getting that error. &nbsp;However, you need to change the subtraction formula to just:<br />
<br />
Sum ({@RevenueOnly}) - Sum ({@ExpenseOnly})<br />
<br />
&nbsp;Put that in the report footer, and you should get the difference between the two account types. &nbsp;The final formula that mlmcc posted was still trying to get a group total for each of those, which won't work because they're in different groups. &nbsp;You'd either get one value or the other or neither, depending on the current value of ACCT_TYPE.<br />
<br />
&nbsp;There is an assumption here that ACCT_TYPE is the only group on the report. &nbsp;If there are other groups, that could change things.<br />
<br />
&nbsp;James<br />

</div>


<div>
You are correct James. &nbsp;I was thinking the group was the Account number and they needed group summaries.<br />
<br />
mlmcc
</div>


<div>
My Groups are<br />
GL_Acct.acct_class_sort<br />
GL_Acct.acct_class<br />
GL_Acct.acct_type<br />
<br />
When I change the revenue formula to the way posted above. &nbsp;It just repeats whatever is the last number in the group.<br />
<br />
The subtraction formula posted by James also says the @RevenueOnly cannot be &nbsp;summarized.
</div>


<div>
Can you post your RevenueOnly and ExpenseOnly formulas?<br />
<br />
Do you need group totals as well as the grand total?<br />
<br />
Where did you put the formulas in the report?<br />
<br />
mlmcc
</div>


<div>
Yes, the revenue and expense formulas are posted in Group Footer 3. &nbsp;<br />
<br />
I don't need a grand total. &nbsp;What they are requesting of me is that one account named Tuition Revenue they want me to subtract the expense side from the revenue side.
</div>


<div>
The formulas if you need to see the values need to be in the details. &nbsp;Yoou can then right click them and put them where you want.<br />
<br />
mlmcc
</div>


<div>
 I think you may have misunderstood something.<br />
<br />
&nbsp;mlmcc asked:<br />
<br />
&nbsp;&gt; Can you post your RevenueOnly and ExpenseOnly formulas?<br />
<br />
&nbsp;You replied:<br />
<br />
&nbsp;&gt; Yes, the revenue and expense formulas are posted in Group Footer 3.<br />
<br />
&nbsp;If that was in response to that question from mlmcc, I believe he was asking if you could post the formulas here, so that we can see them. &nbsp;And if he wasn't asking for that, then I am. &nbsp;:-)<br />
<br />
<br />
&nbsp;When you say &quot;one account named Tuition Revenue&quot;, are you referring to a specific account number? &nbsp;Does the report include multiple account numbers and you need to get the total revenue - total expense for that one account number? &nbsp;Is the report grouped by the account? &nbsp;It doesn't appear to be, unless acct_class_sort or acct_class is something like an account number field.<br />
<br />
&nbsp;James<br />

</div>


<div>
Sum Revenue Only formula is:<br />
If {GL_ACCT.ACCT_TYPE}=&quot;reven<wbr />ue&quot; then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0<br />
<br />
Sum Expense formula is:<br />
If {GL_ACCT.ACCT_TYPE}=&quot;expen<wbr />se&quot; then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0<br />
<br />
I have attached a screen shot so you can see under Tuition Revenue I have two groups. &nbsp;The first one is the revenue accounts the second is the expense accounts. &nbsp;I'm trying to write a formula that subtracts the tuition expense from the tuition revenue. &nbsp;<br />
<br />
The formula that i've been trying is:<br />
If {GL_ACCT.ACCT_CLASS}=&quot;tuit<wbr />ion revenue&quot; then({@Sum Revenue Only}) - ({@Sum Expense})else 0<br />
that is the set of numbers that is showing between the two accounts. <a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/460004/Crystal-Report.docx" target="_blank" class="file-inline" title="formula issue (105 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Crystal-Report.docx</a>
</div>


Crystal-Report.docx

<div>
Revenue Only formula should be<br />
If {GL_ACCT.ACCT_TYPE}=&quot;reven<wbr />ue&quot; then {GL_BAL.ENDING_BAL} else 0<br />
<br />
Expense formula should be<br />
If {GL_ACCT.ACCT_TYPE}=&quot;expen<wbr />se&quot; then {GL_BAL.ENDING_BAL} else 0<br />
<br />
You can then use this to get the difference<br />
<br />
Sum({@Revenue Only}, &nbsp;{GL_ACCT.ACCT_TYPE}) &nbsp;- Sum({@Expense Only}, &nbsp;{GL_ACCT.ACCT_TYPE}) &nbsp;<br />
<br />
mlmcc<br />
<br />

</div>


<div>
When I do the formulas the way you suggest all I get is the last number in the list copied down. &nbsp;Please see attached. <a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/460020/crystal-reports-2.docx" target="_blank" class="file-inline" title="formula changed  (106 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>crystal-reports-2.docx</a>
</div>


crystal-reports-2.docx

<div>
The number in group footer should be a summary value not the revenue only formula.<br />
<br />
If you right click the number in the detail section you can INSERT --&gt; SUMMARY and put it in GF3<br />
<br />
mlmcc
</div>


<div>
OK, so I inserted the summary into GF3. &nbsp;Now how do you get the expense portion to subtract from the revenue portion
</div>


<div>
You can then use this to get the difference<br />
<br />
Sum({@Revenue Only}, &nbsp;{GL_ACCT.ACCT_TYPE}) &nbsp;- Sum({@Expense Only}, &nbsp;{GL_ACCT.ACCT_TYPE}) &nbsp;<br />
<br />
mlmcc
</div>


<div>
 No, mlmcc. &nbsp;You're forgetting that the two formulas are producing values for different ACCT_TYPE's, so you're trying to subtract the total from one ACCT_TYPE group from the total for a different ACCT_TYPE group, and ACCT_TYPE can only have one value at any given time (so, at most, you'll only have one of those totals available when that formula is evaluated).<br />
<br />
&nbsp;I'm thinking that he'll need to use a formula in the ACCT_TYPE group footer to save the revenue and expense group totals in variables and then use the variables to get the difference at the end.<br />
<br />
&nbsp;James<br />

</div>


<div>
You are right. &nbsp;<br />
<br />
mlmcc
</div>


<div>
Good point.<br />
<br />
mlmcc
</div>


<div>
Ok so I built the built the formula James0628 suggested. &nbsp;<br />
went to GF3 and inserted a summary chose the formula as the field I wanted to summarize. &nbsp;Left Calculate this summary as sum. &nbsp;But it is not subtracting the tuition expense from the tuition revenue. &nbsp;What did I do wrong?
</div>


<div>
Is GF3 the correct group?<br />
For this to work G3 has to be above the revenue and Expense groups.<br />
ie tutition revenue and tuition expense must be group 4<br />
<br />
mlmcc
</div>


<div>
I moved it to GF2 so it is above the revenue and expense groups but it adds the two totals together. &nbsp;It is not subtracting them
</div>


<div>
Just summarize the new formula. &nbsp;or are the values for expense already negative?<br />
<br />
mlmcc
</div>


<div>
 Did you include the &quot;-&quot; on the last line? &nbsp;Just checking.<br />
<br />
&nbsp;I think mlmcc may be right about the expense amounts already being negative. &nbsp;His idea was that if it's a revenue account, the formula outputs ENDING_BAL, and if it's an expense account, the formula outputs negative ENDING_BAL. &nbsp;For any other ACCT_TYPE, the formula just produces 0. &nbsp;So, for example, if you had 10 revenue and 3 expense (and some other account types), you'd get 10 and -3, and when you summed those, you'd get 7. &nbsp;But if the expense amounts are already negative, then you don't want to reverse them. &nbsp;If that was the case, you could change the formula to:<br />
<br />
If {GL_ACCT.ACCT_TYPE}=&quot;reven<wbr />ue&quot; or {GL_ACCT.ACCT_TYPE}=&quot;expen<wbr />se&quot; then<br />
&nbsp; &nbsp; {GL_BAL.ENDING_BAL}<br />
<br />
<br />
&nbsp;James<br />

</div>


<div>
Thank you gentleman for all your help. &nbsp;The calculation is working.
</div>


<div>
 You're welcome. &nbsp;I must say, I really like that last formula that mlmcc came up with. &nbsp;I was thinking that you were going to have to use a variable to accumulate a total from the different ACCT_TYPE groups, but his formula avoids that and lets you just do a simple summary.<br />

</div>


<div>
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
</div>


<div>
<div class="content wysiwyg-content">
In Crystal Reports I have two formulas, one that adds revenue accounts (If {GL_ACCT.ACCT_TYPE}=&quot;reven<wbr />ue&quot; then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0) and one that adds expense accounts (If {GL_ACCT.ACCT_TYPE}=&quot;expen<wbr />se&quot; then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0). &nbsp;I am trying to write a formula that will subtract the expense account totals from the revenue account totals. 
</div>
</div>


In Crystal Reports I have two formulas, one that adds revenue accounts (If {GL_ACCT.ACCT_TYPE}="revenue" then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0) and one that adds expense accounts (If {GL_ACCT.ACCT_TYPE}="expense" then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0).  I am trying to write a formula that will subtract the expense account totals from the revenue account totals. 

Subtracting One Formula Field from another

Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.