Get 2nd Value in Dataset in Reporting Services

Question

This seems to be a very simple question, but I am trying to get the 2nd value in a dataset to display as a matrix's header value.

In this report, lets say that I have 2 datasets. In Dataset1, I have a query that pulls down 3 values for a parameter dropdown selection. In Dataset2, I return a result set and have bound it to my matrix.

Within the matrix, I have my repeating columns, and then 3 additional grouped columns to the right that have aggrigate values that I want to display. On the header of those 3 columns, I want to display the 3 values displayed in my Parameters dataset. Within the context of the matrix (and its dataset), I can get the first and last values of a different dataset (Dataset1 in this case) by using:

=First(Fields!DateDisplay.Value, "Dataset1")
=Last(Fields!DateDisplay.Value, "Dataset1")

I need to get something like:

=Second(Fields!DateDisplay.Value, "Dataset1")

How do I pull this off without violating the scoping rules on aggregate columns?

Answer 1

For SSRS 2008 R2, you can do this if each row of your dataset has an identifier column by using the LookUp() function.

=LookUp(1,Fields!Row.Value,Fields!DateDisplay.Value,”Dataset1”)
=LookUp(2,Fields!Row.Value,Fields!DateDisplay.Value,”Dataset1”)
=LookUp(3,Fields!Row.Value,Fields!DateDisplay.Value,”Dataset1”)

If you do not have an identifier column you can use ROW_NUMBER() to build one in.

Query:

SELECT ROW_NUMBER() OVER(ORDER BY DateDisplay) AS Row, DateDisplay
FROM Dates

Results:

Row DateDisplay
--- ---------
1   June 1st    
2   March 12th      
3   November 15th

Here is a link to a similar thread in MSDN Forums: Nth row element in a dataset SSRS

Answer 2

If you are using SSRS-2012 or 2014 then one has to use below expression.

=LookUp(AnyRowNumber, Fields!RowNumber.Value,Fields!DisplayField.Value,”DatasetName”)

I have tried above it was not working in my case.