Change number formatting based on values of another column in SSRS

Question

I want to change the format of the values based on another column. Basically, based on Type column , I want to change the number format. Any help will be appreciated.

Below is the current tablix report. It has 3 columns (Type, US and UK). it always has 4 rows (Sales, Quantity, Percentage and Notes). I tried using Format function but it is throwing me error for other rows. Switch(Fields!Quantity.Value = "Quantity",FormatNumber(Fields!US.Value,0))

Type US UK

Sales 123.36 2122.2

Quantity 2345 3451

Percentage 0.81 0.91

Notes NetSales GrossSales

Date May 20 2020 11:00AM May 20 2020 12:00PM

Now, I want to display them as below

Type US UK

Sales $123.36 £2,122.2

Quantity 2,345 3,451

Percentage 81% 91%

Notes NetSales GrossSales

Date 11AM 12PM

Answer 1

Assuming that your dataset query actually returns those 3 columns as they appear in your sample then you need to be testing the Type column....

I recreated the recordset using the following code in the dataset query

DECLARE @t TABLE (Type varchar(10), US varchar(50), UK varchar(50))
INSERT INTO @t VALUES
    ('Sales', '123.36','2122.2'),
    ('Quantity', '2345','3451'),
    ('Percentage', '0.81',' 0.91'),
    ('Notes', 'NetSales','GrossSales')
SELECT * FROM @t

I then aded two identical tables, one to show the unformatted values and the other to show them after the format expressions have been applied.

For the US Column expression is used the following expression which basically converts each text entry to a value then applies from formatting.

=SWITCH(
    Fields!Type.Value = "Sales", FORMAT(VAL(Fields!US.Value),"$0.00"),
    Fields!Type.Value = "Quantity", FORMAT(VAL(Fields!US.Value),"n0"),
    Fields!Type.Value = "Percentage", FORMAT(VAL(Fields!US.Value),"p0"),
    Fields!Type.Value = "Date", FORMAT(cdate(Fields!US.Value), "HH:mm tt")
    True, Fields!US.Value
    )

The UK expression is almost identical.

=SWITCH(
    Fields!Type.Value = "Sales", FORMAT(VAL(Fields!UK.Value),"£0.00"),
    Fields!Type.Value = "Quantity", FORMAT(VAL(Fields!UK.Value),"n0"),
    Fields!Type.Value = "Percentage", FORMAT(VAL(Fields!UK.Value),"p0"),
    Fields!Type.Value = "Date", FORMAT(cdate(Fields!UK.Value), "HH:mm tt")
    True, Fields!UK.Value
    )

Note the finale True acts like an ELSE

The final output looks something like this...

For reference. "n0" means use thousand separattor with zero decimal places and "p0" means, format as percentage with zero decimal places.

UPDATE AFTER MORE INFO FROM OP

If you have a date as a string e.g. May 20 2020 11:00AM then you can add an extra line to the switch, before the `True, Fields!UK.Value. I've edited the expressions above to allow for dates are strings

using Fields!Type.Value = "Date", FORMAT(cdate(Fields!UK.Value), "HH:mm tt")

all we are doing is converting the string to a date using CDATE() then formatting tat to show the hours only in AM/PM format.

=FORMAT(cdate(Fields!dt.Value), "HH:mm tt")