Using Report Parameters in Header

Question

I've read other posts that recommend using parameters when displaying dynamic data in the header because it renders correctly in pdf. I am trying to implement that solution but have run into an issue. It works when data is returned for a parameter, but it throws an error if data is not returned. Here is my setup:

• Hidden
• Allow null value
• Allow blank value
• Available values: From query
• Default values: From query

The textbox in my header has the following value:

=IIf(IsNothing(Parameters![Report Parameter Name].Value), "", Parameters![Report Parameter Name].Value)

When a row is not returned for the default value, it displays the error:

The '[Report Parameter Name]' parameter is missing a value.

I'm new to reporting services so I may be missing some large concept. To me, no data returned fits the Allow null value criteria.

Answer 1

The problem with using IIF in cases with IsNothing and also in similar divide by zero cases is that it is not a language construct, it is actually a function. Therefore, the parameters are evaluated before calling the function, which means that the expression that you think shouldn't be evaluated IS actually evaluated before the function call.

I know it seems crazy that both the true and false parts of an IF expression are calculated but think of it as a function call and not a language feature. There is no path short circuiting like in a language, the error is in the calculation of ALL the parameters prior to calling the function.

You can replace this with a custom code function to do the same effect that you are after. So something along the following lines:

Right-click the body of the report and choose Properties. Click the Code tab and enter the following code:

Public Function GetParamVal(ByVal ParamVal) As String
    If IsNothing(ParamVal) Then
        Return ""
    Else 
        Return ParamVal
    End If
End Function

Then call it in your header like so:

=Code.GetParamVal(Parameters!MyParam.Value)

(I'm not in front of a system to test this on but you should get the idea)