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

  1. Hi Paul. I simply want to stop execution of a report using a function preferably with a message and without adding or affecting textboxes if either my min balance parameter is zero or max balance parameter is zero. is this possible say without enabling remote errors?.

  2. Hello, is it possible to use the same code in case of synchronizing two tables from different datasources? I’ve tried but couldn’t get the Salary values, just zeros instead.

    1. Yes. If you can’t join tables in a single query, usually the best bbet might be to use the LOOKUP function or one of its variations. That would be much easier than writing your own code. Just be mindful about using this technique with small sets of data. There are usually performance trade-offs. Otherwise, look to a linked server query or some method to mashup the data before it reaches the report, such as SSAS.

  3. Hi Paul,

    I have a specific requirement in which i need the latest cube refresh date in my report dataset and show it on my report.However while running this report simultaneously when the cube is processing I get a error message saying rsProcessingAborted: Query execution failed for DataSet “DataSetName” etc. My question is can i hide this error and show any custom message when user tries to open the report while the cube is down for processing or not available.

    Is it possible to write a custom try catch block to handle these error type properly from SSRS and show the custom message when someone tries to open the report at that time.
    Appreciate your help to solve this.

  4. Paul,
    This is old, but until now I have been able to avoid the need. … When writing a cusom function in Report Code (VB), what is the name space for Report Objects themselves? That is if one wanted to check a report object properties to determine a conditional statement outcome in VB (ie like your System. namespace at the top of the article) I know it exists, because a couple of years ago, before I found a better way, I was accessing report objects in Custom Functions in Report Code, but i have not found where I archived those reports.


    1. I’m not sure what you did here but it looks like your question was entered into the Name field in the comment form. Regardless, you ask a good question. There are a quite a few conditions that would cause an expression to throw an error, like using DATEADD with a Null value. Both branches of an IIF statement are executed whether the expression being evaluated is true or false. One way to deal with this is to encapsulate the expression logic into a custom function and then call the function using =Code.FunctionName(…)

  5. 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?

  6. 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

  7. 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.

    1. 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.

      1. 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?

      2. 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

  8. 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

    1. 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.

  9. When implementing this I get error:
    BC30289 Statement cannot appear within a method body. End of method assumed.

  10. 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?

    1. This isn’t something that Reporting Services was designed to support. Perhaps if you could explain what you are trying to accomplish, in greater detail, I can offer some suggestions.

  11. 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.

  12. Nice Article. Many Thanks.

    For those who are novice to SSRS, make PersSal as Public Shared instead of just Public

Leave a Reply