Sorting Tablix with an Expression as its Value

Question

I am new to SSRS and having trouble sorting my data.

I have a column that I am trying to sort that has the following expression: =((SUM(VAL(IIF(Fields!SecTypeBaseCode.Value="cs",Fields!Marketvalue.Value,0))))/ (Sum(Fields!Marketvalue.Value)+Sum(Fields!AccruedInterest.Value)))/(Fields!EquityTarget.Value/100)

I order to try and sort its calculated values, I have created a calculated field with the expression. Then I tried sorting based on the calculated field. However, every time I do this it gives me an error because the expression has an aggregate. Any idea how to fix this issue or sort it ?

Have you tried just using the expression directly as the sort order, rather than in a calculated field? — Alan Schofield, Jul 16 '21 at 14:46
Hi, yes I have tried doing that. I have posted the following: =IIF( ((SUM(VAL(IIF(Fields!SecTypeBaseCode.Value="cs",Fields!Marketvalue.Value,0))))/(Sum(Fields!Marketvalue.Value)+Sum(Fields!AccruedInterest.Value)))/(Fields!EquityTarget.Value/100) = ">1", 1) , still get the same error of there being an aggregate. — JMCZUCK, Jul 16 '21 at 14:53
The expression in your comment is incorrect, there are an uneven number of parentheses and the last part is comparing a a number with a string. try taking your original expression and just putting it in a column in a table, make sure that returns the correct numbers and then you should be able to put that into the row group's sorting properties. — Alan Schofield, Jul 16 '21 at 15:08
You have do aggregate sorting on the GROUP properties. You can't do it from the tablix or dataset. — Hannover Fist, Jul 16 '21 at 16:04
Still trying to figure this out, I appreciate the information. Seems like when I put the sorting function in the Group Properties, it doesn't sort the data, although the error does disappear. Is it possible to use the Me.Value in the function instead of the whole formula ? — JMCZUCK, Jul 16 '21 at 18:08

Hannover Fist · Answer 1 · 2021-07-16T21:29:45.083

The aggregate expression for sorting needs to be on the individual item's Group and not on the dataset, table or chart's properties.

You may be getting a silent error that lets the report run but fails to evaluate the expression due to a possible divide by zero issue. If you preview the report in Visual Studio, you may see the error in the Error List.

SSRS like to be smart and check for possible issues with dividing by zero. Unfortunately, it finds any place that a field could be zero. To get around this, use IIF statements so that it replaces the possible zero divisor with a 1 and the numerator with a zero. Your expression is a bit more complicate since you have two divisors but it should be something like

=   IIF(Sum(Fields!Marketvalue.Value + Fields!AccruedInterest.Value) = 0 
        OR Fields!EquityTarget.Value = 0, 
            0, 
            SUM(IIF(Fields!SecTypeBaseCode.Value="cs", Fields!Marketvalue.Value, 0))
        )
    / 
    IIF(Sum(Fields!Marketvalue.Value + Fields!AccruedInterest.Value) = 0, 
        1, 
        Sum(Fields!Marketvalue.Value + Fields!AccruedInterest.Value)) 
    / 
    IIF(Fields!EquityTarget.Value = 0, 1, Fields!EquityTarget.Value)
    * 
    100

See also: Avoid divide by zero in SSRS expression

SSRS Expression Divide by Zero Error

Attemted to divide by zero error in SSRS Expression

Sorting Tablix with an Expression as its Value

1 Answers1