Conditional Negative Number

[Guest]

I have a report that lists loan officer names and their loan portfolio $ for
last month. The report is grouped by supervisor. The report is pulling data
from a table where the supervisor's loan portfolio is a total of their loan
portfolio, plus all the loan officers they supervise. So, for a loan officer
named John who supervises three other loan officers, his personal loan
portfolio is only $1 million, but the number that displays on the report for
him is $5 million, because the table contains the sum of John's portfolio and
the three loan officers he supervises.

Now getting back to my report, is there a way to use conditional formatting
to change a positive number in a report field to a negative number? I want to
do something like this:

If [supervisor] = John, then leave the number positive
Else change the number to a negative

I want to do this so that when I add a subtotal to John's group, the report
takes John's $5 million loan portfolio, subtracts the loan portfolios of the
three officers he supervises (because they are negative numbers now), and in
the group total gives me John's actual portfolio of $1 million. So my report
output would look like this:

Supervisor: John $5,000,000
Ted -1,000,000
Jack -1,000,000
Ann -2,000,000
Group Total $1,000,000

I hope I've explained this clearly enough. Thanks in advance for your help.

GwenH

 

[Steve Schapel]

Gwen,

Well, it's not formatting, it's a value manipulation. So yes, you could
do pretty much as you suggested, either ina calculated field in the
query that the report is based on, or in a calculated control on the report.
=IIf([Supervisor]="John",1,-1)*[Amount]

Without knowing much about your database, it seems likely that "the
supervisor's loan portfolio is a total of their loan portfolio, plus all
the loan officers they supervise" should probably be regarded as a
databse design flaw. It seems likely to me that the table should store
the supervisor's own loan portfolio amount, and the amount that includes
his supervisees' portfolios should be calculated as required.