We recently needed to add an option to an “extract report” to shift the values on a per row basis depending on criteria within the row. The report displays remaining payment streams for equipment leases where RP1 represents the next payment owed by the customer.
Contract # RP1 RP2 RP3 … RP120
In the current version of the report, RP1 represents the next amount owed on a lease regardless of when it was due. Our customer wanted to optionally have RP1 represent the same month for all leases. This requires us to shift each row a certain number of months to the right and have RP1 represent the earliest possible month in the data set. Initially, we considered creating another table in the data warehouse to hold the shift payment streams until we discovered that we could handle the shifting in the actual report using the following function:
Public Function GetValue(allFields as Fields, reportType as string, index as integer) as string
Dim offset as integer
offset = 0
if reportType <> “Standard” then offset = allFields!DueDateOffset.Value GetValue = “”
if (index + offset) <= 120 then GetValue = allFields(“RP” + CStr(index + offset)).Value
End Function
The function is called from each the report textboxes:
=Code.GetValue(Fields, Parameters!ReportType.Value, 5)
The key learning was that the collection of fields can be passed as a parameter and then accessed as an array using the column name as a string index.
References:
Jersey Dude 