Hello everyone, I have a report that contains a matrix with quarters in a column group and products in a row group. I have a stored procedure that will always return 5 quarters of data, but I'd like to create a matrix that will allow me to display only the first quarter and the last quarter out of the set of 5. I'd like to do some first quarter/last quarter type of comparions. Is this possible? I'd like to reuse the query to display some charts that will show all quarters, so I'd like to control this from the matrix if at all possible. Thanks! Mark

Since both FIRST() and LAST() are valid aggregate functions that should return the dataset rows you want within the group, you should be able to do this with two total columns in the matrix. First of all, I'm assuming that you are using SSRS 2008 or newer. Create two total columns for the column group based on the Quarter and then remove or hide the detail column. For each of these columns, use an expression to return the result of the FIRST() and LAST() function [example: =FIRST(Fields!SomeNumericField.Value) ]. Optionally, you can control the expression scope and reference the column group [ =FIRST(Fields!SomeNumericField.Value, "NameOfTheQuarterGroup") ] but I don't think this should be necessary in this scenario. Please let us know how this works and let me know if this was helpful.Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com

Hi Paul, I tried using First() and Last() to reference the appropriate quarters in my report, and it works to a point. The problem is that SSRS seems to assign the first non-empty member of the set of quarters to the First() position. When I subtract the value from the First() position from the value in the Last() position and the First() position is null the value in the second position is substituted. I tried an IIF statement to test for Is Nothing in the first position but that didn't seem to work in my calculation. Do you know of any kind of First() with a non-empty clause? I'm on SSRS 2008 by the way. Thanks for your help. Mark

I'm not sure if this will work (it might be what you tried): =FIRST(IIF(IsNothing(Fields!SomeField.Value), 0, Fields!SomeField.Value)) Another option is to create a calculated field for the dataset that converts all Nulls to zero and then use that field in your FIRST() & LAST() expressions.Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com