0

I am trying format 1 column to have the number format based on what the value is:

My current table enter image description here

I am trying to get it to be a percentage except when the description says flat tax, I then want it to be a currency.

Any ideas?

SELECT t1.property, '100' AS tran_code, 'ROOM RATE' AS description, NULL AS tax_amt, @rate AS amount
    FROM z_taxtype_detail t1 INNER JOIN z_trancode t2 ON t1.tran_code = t2.code
        WHERE t1.tax_type = 'ROTX'
        AND t1.property = @property
            GROUP BY t1.property
UNION ALL
SELECT t1.property, t2.code, t2.description, (t1.tax_amt / 100),
      (CASE WHEN t1.tax_base = '1' THEN @rate * (t1.tax_amt / 100)
            WHEN t1.tax_base = '4' THEN t1.tax_amt ELSE 0 END) AS tax_amt
    FROM z_taxtype_detail t1 INNER JOIN z_trancode t2 ON t1.tran_code = t2.code
        WHERE t1.tax_type = 'ROTX'
        AND t1.property = @property
NickyvV
  • 1,720
  • 2
  • 16
  • 18
Femmer
  • 133
  • 12

2 Answers2

0

Add an expression for the Tax Amount column.

=IIF(Fields!Description.Value <> "Flat Tax", FormatPercent(Fields!Tax_Amt.Value,3), FormatNumber(Fields!Tax_Amt,2))

SS_DBA
  • 2,403
  • 1
  • 11
  • 15
  • It is giving me an error message: The Value expression for the text box ‘tax_amt’ refers to the field ‘Description’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. – Femmer Nov 09 '16 at 20:50
  • Does the Description column have another name in the dataset? – SS_DBA Nov 09 '16 at 20:53
  • No, it is just description – Femmer Nov 09 '16 at 20:55
  • I'm deferring to alejandro, we seem to be on the same track. No reason to duplicate. – SS_DBA Nov 09 '16 at 21:04
0

Try this:

=IIF(Fields!Description.Value="FLAT TAX",
Format(CDBL(Fields!Tax_Amount.Value),"C3"),Format(CDBL(Fields!Tax_Amount.Value),"P2")
)

UPDATE: It seems you are passing a NULL value from the query, which could result in error while the expression is evaluated.

Try using this query:

SELECT t1.property, '100' AS tran_code, 'ROOM RATE' AS description, 0 AS tax_amt, @rate AS amount
    FROM z_taxtype_detail t1 INNER JOIN z_trancode t2 ON t1.tran_code = t2.code
        WHERE t1.tax_type = 'ROTX'
        AND t1.property = @property
            GROUP BY t1.property
UNION ALL
SELECT t1.property, t2.code, t2.description, (t1.tax_amt / 100),
      (CASE WHEN t1.tax_base = '1' THEN @rate * (t1.tax_amt / 100)
            WHEN t1.tax_base = '4' THEN t1.tax_amt ELSE 0 END) AS tax_amt
    FROM z_taxtype_detail t1 INNER JOIN z_trancode t2 ON t1.tran_code = t2.code
        WHERE t1.tax_type = 'ROTX'
        AND t1.property = @property

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48