SSRS - Next/Previous Record Button Based on Date

Question

I have a report in SSRS that shows various tablixes based on a date parameter. The user will select a date and the report will populate based on that date. This works fine. However, I would also like the user to be able to click on a "next record" button (and a "previous record" button) and the parameter (and report) will change based on the next date in the database.

The way I would normally accomplish this is by creating a text box with an action. For example, I would create a text box "Next Record", go into text box properties > Action > Go to report >, and send a new parameter that normally would be something like Parameters!survey_id.Value + 1. This works well if I want to increment the numeric ID by a static number (ie, increment by 1). I assume it works for dates too if I want to increment the day by 1 day; but how do I do this if the next record is not one day?

One way I thought would work is to create a row_number field in the dataset that provides the values to the parameter. Here is the code:

select distinct sm.survey_date
,row_number() Over (order by survey_date) as increment
from survey_main as sm
group by sm.survey_date --sometimes there are multiple surveys in one day
order by sm.survey_date desc

which gives the results

survey_date         increment
2019-09-16          194
2019-08-24          193
2019-01-14          192

My thought was I could use the increment field in the text box action to find the next survey_date, but I cannot figure out the code (or if it's possible).

Is there a way to make that work? Are there any other suggestions or workaround that you can think of?

Thank you!

Answer 1

There are a few ways to do this, but this is the way I would approach it.

If the reports dataset was something like (this could be refactored but it easier to digest like this)

DECLARE @nextDate date

SELECT @nextDate = MIN(sm.survey_date) 
    FROM survey_main sm 
    WHERE sm.survey_date >= @reportParameterDate -- this is the date passed in from the report

SELECT * 
    FROM survey_main sm
    WHERE sm.survey_date = @nextDate

So, first time you run the report, if you passed in 2019-08-24 then you would get data for that date, if you passed in 2019-08-25 you would get data for 2019-09-16

Then on the action for the textbox "button" the date parameter would be and expression something like

=DATEADD("d", 1, FIRST(Fields.survey_date.Value, "myDatasetName"))

This will add 1 to the survey_date that is in the dataset (these should all be the same, hence FIRST() will be fine to use).

Going back to the first run, we set the parameter as normal to 2019-08-24 and get data for that date, now when we click the 'Next Record' button it will take the FIRST survey_date (which will be 2019-08-24) and add 1 day to it and pass it to the report via the action. So we will be passing 201-08-25. The reports dataset will get the MIN survey_date that is >= the date we passed in, in this case 2019-08-25, and return 2019-09-16 which we stick in @nextDate which subsequently filters the data returned.

Hope that makes sense.