SSRS Label Formatting with expression-CodePudding

I'm currently working on an SSRS report and would like the format to change based on if the value is over a million Can anyone help me with the syntax of the code?

=IIF(Sum(Fields!ID_Total_Spend_.Value, "DS_ClientsYear") > 1000000, £#,,.0M;('£'#,N2,'m'), £#,0,k;(‘£’#,N2,’k’))

CodePudding user response：

I'm not sure exactly what you are trying to get to but here's an example I built that should get you close enough.

I've assumed that

If the value is over 1000000 then you want it shown as a number of millions, e.g. 12341234 would show as £12.34M
If the value is over 1000 but less than 1 million then you want it shown as a number of thousands, e.g. 12341 would show as £12.34K
If the value is less then 1000 then show is as-is to 2 decimal places e.g. 123 would be shown as £123.00
Negatives are just surrounded with brackets

I created a sample dataset using the following (in case you want to reproduce)

DECLARE @t TABLE(id int, amount float)
INSERT INTO @t VALUES
(1, 1), (2,99), (3,99123), (4, 999123), (5, 1000000), (6, 12345678),
(7, -1), (8, -99), (9,-99123), (10, -999123), (11, -1000000), (12, -12345678)

SELECT * FROM @t

I then added a simple table showing

ID column (for reference), the raw amount unformatted, the raw amount formatted with the "c2" standard currency formatting, the converted amount based on the amount value and finally the converted amount with a custom formatting applied.

I'll only explain the final column as that's probably what you want.

The first things I did was change the expression of the final textbox to

=SWITCH(
    ABS(Fields!amount.Value) >1000000, Fields!amount.Value / 1000000,
    ABS(Fields!amount.Value) >1000, Fields!amount.Value / 1000,
    True, Fields!amount.Value 
    )

This simply divides the amount based on it's value. SWITCH stops when it hits the first expression that returns true so there is no need to worry about amounts between certain values. Note I test the ABS(amount) to the division works on both the ive and -ive numbers in the same manner. The final True acts like an else.

You can see the output this expression gives in "raw converted amount" column.

In the format property of the final textbox I set the expression to

=SWITCH(
    ABS(Fields!amount.Value) > 1000000, "£#.00M;(£#.00M)",
    ABS(Fields!amount.Value) > 1000, "£#.00K;(£#.00K)",
    True, "£#.00;(£#.00)"
    )

We are doing the same checks that we did to convert the amount but this time returning a valid format string. In this example I used a 2 section string, the bit before the semi-colon applies to ive number and zero. The second part applies to -ive numbers only.

The final output looks like this...