SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Return the Value of a Specified Dataset Row Outside a Data Region

I want to return the field value for a specific row to a textbox below a table in my report. To pull this off, I have to be a little creative as there isn't a simple way to return a value from a specific row in a dataset outside of a data region other then using the FIRST() or LAST() functions. One technique is to write a custom function and then pass values on each row of a table through it to capture the row ID and value that you will want to return to a textbox outside the table. here's an example:

The report contains a parameter named RowNumber used to specify the row whose value I want to return.

In the report properties Code window, I've entered the following VB.NET code:


Private dRowValue As Decimal

Function SetRowValue(CurrRowID As Integer, ReturnRowID As Integer, Value As Decimal) As Decimal

If CurrRowID = ReturnRowID Then dRowValue = Value

Return Value

End Function

Function GetRowValue() As Decimal

Return dRowValue

End Function

In the textbox in the detail row of my table that shows my field value, I use the following expression:


=Code.SetRowValue(Fields!ID.Value, Parameters!RowNumber.Value, Fields!Value.Value)

...and in the textbox below the table where I want to see the value for the row specified by my RowNumber parameter, I use the following expression:



Weblog by Paul Turley and SQL Server BI Blog.


No comments.

Leave a Comment

Please register or log in to leave a comment.