SQLTeam.com | Weblogs | Forums

SSRS Report Custom Code Equivalent for CountRows("myDataSet")


#1

Hi

I am trying to do the following in an SSRS report that has multiple subreports:

=Iif(
      Parameters!MyMultiValueParameter.Count = CountRows("MyDataset"),
      "All",
      Join(Parameters!MyMultiValueParameter.Label,", ")
)

however I get #Error displayed instead of the list of items selected, this only occurs when the subreports are included in the report. It appears to be losing its focus.

If I pass parameter values to Report Custom Code it works however I am unable to pass CountRows("myDataSet") in order to do:

= Code.SetMyDept( JOIN(Parameters!p_dept_id.Label, ", "), Parameters!p_dept_id.Count, CountRows("dset_LkUp_Department") ) 
& Code.GetMyDept() 


Public Shared Dim MyDept as String
Public Shared Dim DeptsSelected as Integer
Public Shared Dim TotalDepts as Integer

Public Function SetMyDept (ByVal var_dept as String, ByVal var_deptsSelected as Integer, ByVal var_totalDepts as Integer)
    'MyDept = var_dept
    'DeptsSelected = var_deptsSelected
    'TotalDepts = var_totalDepts
    MyDept = IIF( var_deptsSelected < var_totalDepts, var_dept, "ALL" )
End Function

Public Function GetMyDept() as String
    'return IIF( DeptsSelected < TotalDepts, MyDept, "ALL" )
	return MyDept
End Function

Any ideas please?