How to Reference a VB Function in Reporting Services Grouping

Question

Good Morning,

I'm using Reporting Services 2005 to produce a one-time report that will group records according to the PLANCODE field. I've created the following VB function:

Function PayorGroup (ByVal ipc as String) as String
set ipc = PLANCODE
Select Case ipc
Case 'M01' to 'M99'
PayorGroup = "Medicare"
Case 'A01' to 'A99'
PayorGroup = "Medicare"
Case 'C01' to 'C99'
PayorGroup = "Cash Pay"
Case 'T01' to 'T99' 
PayorGroup = "TCMS"
Case 'W01' to 'W99'
PayorGroup = "Work Comp"
Case 'Y01' to 'Y99'
PayorGroup = "Medi-Cal Managed Care"
Case 'Z01' to 'Z99'
PayorGroup = "Medi-Cal"
Case Else
PayorGroup = "Managed Care/Other"
End Select
Return PayorGroup
End Function

My intent is to have the above function evaluate the PLANCODE field and return the appropriate PayorGroup which will be used to group data in my report. I've placed the above function in my Report Properties Code Window.

How do I reference the above function in a new calculated field so that the report will group data based on the results of this function? The doucmentation states that the expression should be something like =code.PayorGroup. However, when I try this expression in the Calculated Field window, I get the infamous red squiggly line stating Unrecognized Idnetifier.

Can someone provide me some direction as to what the best route would be for this solution?

Thanks, Sid

Answer 1

I would suggest creating a lookup table in SQL for this & join it with the base table/query that you have.

It has several advantages

1. The logic won't be frozen inside the report.
2. Any additional value can be taken care of using the lookup table.