20 thoughts on “Using Custom Code Functions in Reporting Services Reports

  1. Great article I have be going crazy trying to figure out how to combine list data for weeks now. I have one question though:

    In the first example on this page both tables MUST be present on the page for this to work. If I hide the first table the Net Income field reads $0. Is there a way to show only the second table in the report and still have the calculation perform as expected?

    Like

  2. Hi Paul,
    I have a matrix report with only one row group. I need to calculate percentage between two rows in the row group. I have write the following expression but it do not work. “iif(Fields!Col.Value = “Discount % on Revenue”,iif(Fields!Col.Value = “Discount”,Sum(Fields!value.Value),0)/iif(Fields!Col.Value = “Turnover”,Sum(Fields!value.Value),0),Sum(Fields!value.Value))”. Can you please help. Thank you

    Like

  3. I retyped the entire code and now I get this message
    There is an error on line 8 of custom code: [BC30205] End of statement expected.

    Like

    • Hi, I’ve created an example of this report and added the RDL file after the last screen image. I copied and pasted the code right out of the post. Please take a look and let me know if you have any issues.

      Like

      • Thank you Paul, your example rdl file helped. I now have the feature working. Can you tell me how I can reverse it so it goes from lowest value = Green and Highest value = Red?

        Like

      • Just reverse the assignment of the OutR and OutG variables in the function. I’ve updated the code and uploaded a new example to the original post. The new code has a Direction parameter. You should be able to use the values of these three variables to output any color you want using some logic to do the color mixing math.

        Function HeatMapColor(ThisValue As Decimal, Direction As String) As String
        Dim OutR As Byte, OutG As Byte, OutB As Byte
        OutB = 0

        If Direction = “RtoG” Then
        OutR = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
        OutG = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
        Else ‘GtoR
        OutG = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
        OutR = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
        End If
        Return “#” & Right(“0” & Hex(OutR), 2) & Right(“0” & Hex(OutG), 2) & Right(“0” & Hex(OutB), 2)
        End Function

        Like

  4. this is what I have

    Private LowVal As Decimal, HighVal As Decimal
    Function SetHeatMapRange(LowValue As Decimal, HighValue As Decimal) As Object
    LowVal = LowValue
    HighVal = HighValue
    End Function
    Function HeatMapColor(ThisValue As Decimal) As String
    Dim OutR As Byte, OutG As Byte, OutB As Byte
    OutB = 0
    OutR = 255 – (((ThisValue – LowVal) – (HighVal – LowVal))* 255)))OutG = 255 *(((ThisValue – LowVal) / (HighVal – LowVal)))
    Return “#” & Right(“0” & Hex(OutR), 2) & Right(“0” & Hex(OutG), 2) & Right(“0” & Hex(OutB), 2)
    End Function

    Like

    • It looks like the problem might be with the double quote characters. Text editors and word processors often replace quote characters with “smart quotes”. Try taking this into NotePad and retyping the quotes and then copy the code back into the SSRS code editor.

      Like

  5. I have an report that extends your Heat map sample a little. Say for example you have a grouiping above region, say Country. Now within the Region you want all four cells with the same background/fill of the smallest number. Is there a way to do that?

    Like

  6. I found this Article very useful. I have similar to this scenario problem that i’m facing. I have an assignment in the report that i have to bring data from 2 different datasets and each dataset from different database to display in the same data regions. here is example of my query

    Dataset UnitProd: Select ID, Description, permitTypeID, OverallQouta from Units where permitTypeID = @PermitTypeID // This from Prod database

    Dataset UnitTest: Select ID, Description, permitTypeID, OverallQouta from Units where permitTypeID = @PermitTypeID // This from Test Database

    Here is my report will look like

    ID | Description | OverallQouta | | ID | Description | OverallQouta |
    54| AA Unit | 10 | | 54 | AA Unit | 15

    *************************************************************
    the requirement to compare between 2 database for OverallQouta if overallQouta is not the same will highlight that row.

    for now i use Lookup to bind dataset to display data in the report but when i compare i use expression something like

    =IIF(First(Fields!OverallQouta.Value, “UnitProd”) First(Fields!OverallQouta.Value, “UnitTest”),”Yellow”,”Transparent”)
    but when it compare and check that overallqouta is not the same it will highlight very row , what i want i just want to highlight only row that different.

    Do you have an advise for me to accomplish this requirement?

    Thank you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s