dimanche 18 octobre 2020

SSRS Report Numerical Formatting

I have a two column table: TYPE and VALUE, with both columns' values being of the type INT.

I must format Value.value in an SSRS paginated report using "+" and "-" to denote the value's sign, and with parenthesis, i.e. "(value)" or without parenthesis as determined by the column TYPE. If the value of TYPE is > x, then parentheses; else, no parentheses.

I commonly see negative values formatted with parenthesis, i.e. (1,000), but in this case I can't use that standard.

Here's an example of the four solutions I seek, exemplified by the values -1,000 or 1,000, having TYPE > or < x, respectively:

(-1,000)
-1,000
(+1,000)
+1,000

SSRS reports' user interface seems to allow custom expressions for both VALUE and FORMAT. In this instance, I'm creating a custom VALUE expression but not a custom expression for FORMAT (it's possible a solution might combine custom expressions using both).

Here's my attempt, which isn't working (object error).

=iif(TYPE.value > x AND VALUE.value > 0, "(+"&VALUE.value&")"), 
 iif(TYPE.value > x AND VALUE.value < 0, "(-"&*VALUE.value&")"),
 iif(TYPE.value < x AND VALUE.value < 0, "-"&VALUE.value,
 "+"&VALUE.value)

Welcome any guidance. Tks!

Aucun commentaire:

Enregistrer un commentaire