Search code examples
reporting-servicesssrs-2012

Get previous first value from parameter

I have a parameter date from dataset date: Sample of data

Key Month Year Row num
20210131 January 2021 3
20201231 December 2020 2
20201130 November 2020 1

I need to do an expression that returns the previous of selected value for example when a user selects January 2021(date parameter) it will return December 2020 in expression in text box.

I try previous(first(fields! Month_year. Value, "group name")) but it returns error, is there any way to resolve it?

Solution

  • I would suggest that you extend the dataset query to include the previous value against each row.

    Assuming you dataset query is called DataSet1 and uses a table called 'myTable' then something like this (I changed the sample column name from Key to KeyID to avoid keyword problems).

        SELECT *
        , LAG(KeyID) OVER(ORDER BY RowNum) as PreviousMonthKeyID
        FROM myTable

    NOTE: This assumes row number is always in the correct order, if not then you could change this to order by KeyID.

    You can then use a LOOKUP() to find the correct value, so if your parameter is called selectedDate and it's value is set to the KeyID column, use something like

    =LOOKUP(Parameters!selectDate.Value, Fields!KeyID.Value, Fields!PreviousMonthKey.Value, "DataSet1")

    This reads "get the value in selectdDate, find this value in the column KeyID in the dataset called DataSet1 and return the value found in the column called PreviousMonthKeyID"