Dynamic formatting in SSRS matrix column cells

I have the following unpivoted dataset

Select
    *
From
    (
        Values
            (1,'name','article 1','nvarchar')
            ,(1,'price','123.00','decimal')
            ,(1,'warrenty','2026-01-01','date')
            ,(1,'amount','10','int')
            ,(2,'name','article 2','nvarchar')
            ,(2,'price','12.45','decimal')
            ,(2,'warrenty','2026-12-01','date')
            ,(2,'amount','13','int')
            ,(3,'name','article 3','nvarchar')
            ,(3,'price','00.99','decimal')
            ,(3,'warrenty','2026-06-15','date')
            ,(3,'amount','2','int')
    ) As a(number,col,val,coltype)

and output it in a matrix tablix with row group number and column group col like this:

I would now like to format some of the columns according to their type in coltype

I tried a custom format expression

=Switch(
    Fields!coltype.Value = "int"
        ,"0"
    ,Fields!coltype.Value = "decimal"
        ,"0.###"
    ,Fields!coltype.Value = "date"
        ,"dd/MM/yyyy"   
)

But the formatting is ignored. I think because date and number formatting is only recognize for date and number fields not string.

Therefore I added a corresponding conversion in the text field expression

=Switch(
    Fields!coltype.Value = "int"
        ,CInt(Fields!val.Value)
    ,Fields!coltype.Value = "decimal"
        ,CDbl(Fields!val.Value) 
    ,Fields!coltype.Value = "date"
        ,CDate(Fields!val.Value)
    ,1=1
        ,Fields!val.Value
) 

Result:

So I removed the custom format and tried to format the values of the text field directly with this format expression:

=Switch(
    Fields!coltype.Value = "int"
        ,Format(Fields!val.Value,"0")
    ,Fields!coltype.Value = "decimal"
        ,Format(Fields!val.Value,"0.###")   
    ,Fields!coltype.Value = "date"
        ,Format(Fields!val.Value,"dd/MM/yyyy")
    ,1=1
        ,Fields!val.Value
)

I tried N or n for int and D or d for decimal but the only result is the formatting string becomes the cell value. Interestingly, the string ShortDate or Long date works for date formatting. Unfortunately the wrong format.

So how can I dynamically format the value in a SSRS matrix cell or do I have to do it in the dataset query?

You were very close! You need to combine both the value conversion and the formatting.

Your value conversion failed because every value is evaluated even if it's excluded due to the condition in SWITCH(), therefore all the numbers were failing to be converted to dates etc... ( can't convert "10" to a date).

So, the value expression for the matrix 'cell' should be ...

=Switch(
     Fields!coltype.Value = "int" , VAL(Fields!val.Value)
    ,Fields!coltype.Value = "decimal" , VAL(Fields!val.Value)
    ,Fields!coltype.Value = "date" , CDATE(IIF(Fields!coltype.Value = "date", Fields!val.Value, "1900-01-01"))
    , True,  Fields!val.Value
)

Notice 2 things.

1. within the CDATE() we check if the coltype is "date" and if it isn't we swap the value for an arbitrary valid date (in this example 1900-01-01). This value will never be seen so any date will suffice.

You might wonder why we don't do a similar thing for the numeric data types? I use VAL() to convert to a numeric value, and this is quite happy to convert date strings etc, even if the result is not what you might expect (e.g. VAL("2026-12-01") will return 2026), it doesn't matter as these values will never be seen.

2. The final True acts as an else in the same way as you did with 1=1, just a bit neater (IMHO)

OK so that's the values sorted,

The format expression is pretty much what you had anyway, I've just used the shorthand format codes. I've use N to show thousand separators, just swap that to F is you don't want them.

=Switch(
     Fields!coltype.Value = "int" ,"n0"
    ,Fields!coltype.Value = "decimal" ,"n3"
    ,Fields!coltype.Value = "date" ,"dd/MM/yyyy"   
)

Hope fully that made sense.

Here's the output from my sample. The blue shaded columns are the converted versions, the white column are the raw database values for comparison.