Using a MOD function in SSRS

  • hans.johnson

    Mr or Mrs. 500

    Points: 556

    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!

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    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)

  • hans.johnson

    Mr or Mrs. 500

    Points: 556

    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.

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    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.

  • fropen

    Grasshopper

    Points: 11

    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