Using a MOD function in SSRS

  • Can someone please let me know how I convert this Crystal derived columns formula to a valid SSRS expression? All of the fields can remain the exact same names.

    if {eSourceProvider.PGMIDANNUAL} > 0 then

    {@EmAnnual} % {eSourceProvider.PGMIDANNUAL}

    else

    0

    Thanks for looking!

  • If I understand your question and the title, it will be something like this:

    =IIf (Fields!eSourceProvider.PGMIDANNUAL.Value > 0, Parameters!EmAnnual.value Mod Fields!eSourceProvider.PGMIDANNUAL.value, 0)

  • Thanks so much for the reply. I think we're close but not quite there. This is what I converted what you wrote to based on specs of what I needed:

    =IIf (Fields!PgMidAnnual.Value > 0, Fields!EmAnnual.value MOD Fields!PgMidAnnual.value, 0)

    But, this is the error I get. The report renders but there's an "Error#" printed in the cell where this expression resides. This is what the error list text is reading:

    Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox23.Paragraphs[0].TextRuns[0]’ contains an error: Attempted to divide by zero.

  • Not sure about that one.

    I would try 2 things. First substitute a hard value to see if you can get it to work like 12 MOD 5.

    Second check the data types of your two fields. They should both be the same type. I am not sure, but they might both have to integers. If that is not the case then you will have to cast one or the other to be the same data type.

  • Just for historical purpose:
    The error is because the IIf function evaluates both paths, the if and the else statement, regardless the condition.
    That is why you get an "Attempted to divide by zero" error.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply