http://www.sqlservercentral.com/blogs/pturley/2010/03/16/return-the-value-of-a-specified-dataset-row-outside-a-data-region/

Printed 2014/04/20 01:07AM

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

By pturley, 2010/03/16

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:

>

=Code.GetRowValue()



Weblog by Paul Turley and SQL Server BI Blog.
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.