8

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?

Nathan
  • 1,435
  • 5
  • 18
  • 28

2 Answers2

9

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

Gabrielius
  • 1,045
  • 12
  • 18
dotNetE
  • 616
  • 1
  • 6
  • 27
  • This is incorrect - if you try this in SSRS 2008r2 you get an error stating that the Lookup Function has an incorrect amount of parameters. It takes 4 not 3. I would vote this down but i can't! – leinad13 Oct 03 '13 at 16:25
  • 1
    @leinad13 I have edited the posting. This should be more clear now. My mistake, sorry for the confusion. – dotNetE Oct 03 '13 at 18:44
  • 2
    @dotNetE Looks like you have the parameters in the wrong order, should be =LookUp(2, Fields!Row.Value,Fields!DateDisplay.Value,”Dataset1”) – Automate Apr 08 '16 at 06:06
1

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.

Pedram
  • 6,256
  • 10
  • 65
  • 87