Jeff Moden (11/3/2012)
Thanks guys, but...
Let me try and be more clear.
The data is coming from a table where the monetary value is always "just" a value so the output looks like:
SI- 300 250.00
Where in the Inv/Credit column the id is prefixed with a "SC" I know that's a credit note so in this report - written in BIRT - for the accounts guys, I need to leave the "SI" values "as is" but have a minus symbol where there is a "SC" so it outputs like this:
SI- 300 250.00
You may have to change the column names and, certainly, the table name, but the following will do it quite nicely.
Amount = CASE WHEN LIKE 'SC%' THEN Amount ELSE -Amount END
That's close, but I fear I have misled you...
The SI/SC is not the value field, that's the identifier, and another field is the value.
I tried this, based on your help:
invoices.taxdate,invoiceitems.homenettvalue, CASE WHEN invoices.id LIKE 'SI%' THEN invoiceitems.homenettvalue ELSE -invoiceitems.homenettvalue END,
This adds a column - with no name - repeats the values from the invoiceitems.homenettvalue BUT adds the minus symbol when the invoices.id = 'SC% 'which is what I'm after, but not in a new column. So at the moment it now looks like this:
nominalaccountid name partid id itemnumber homenettvalue (No column name) hometaxvalue
1129-BALSHEET Elektrameric Ltd 294-34200 SI-020217 1 0.00 0.00 0.00
1129-BALSHEET Elektrameric Ltd 294-35610 SI-020217 2 0.00 0.00 0.00
1129-BALSHEET Bryan Donkin RMG Gas Cont 294-23090 SI-020350 7 14.00 14.00 2.45
1160-BALSHEET Shenzhen Oska Measurement Systems E8UPGRADEMISC SC-030585 1 1619.8199 -1619.8199 0.00
1160-BALSHEET Shenzhen Oska Measurement Systems E8UPGRADEMISC SC-030593 1 1619.8199 -1619.8199 0.00
1160-BALSHEET Shenzhen Oska Measurement Systems E8UPGRADEMISC SI-030591 1 1619.8199 1619.8199 0.00
2210-BALSHEET ISIS Precision Engineering Ltd. SR000000 SI-029899 2 0.00 0.00 0.00
2210-BALSHEET ISIS Precision Engineering Ltd. SR000000 SI-029899 3 0.00 0.00 0.00
2210-BALSHEET Spire Machining Ltd. MS0003IN SI-030247 1 295.00 295.00 51.63