0

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!

Billy B
  • 37
  • 5

1 Answers1

0

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.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks for the response, wow, I have a lot to learn! I did everything you said and it makes sense (in theory at least, but i have more to learn), but it's not quite working. It works when the next date is 1 day forward, but it's still not happy if it's 2+ days ahead. To me, it seems as when you click the "next record" button, it grabs the date from the old dataset, then adds one day to it; as opposed to passing the "survey_date + 1" value to a newly-run dataset. If that makes sense. – Billy B May 13 '20 at 23:45
  • I did this all from memory so it might not be 100% accurate. The first thing to do is to run the dataset query in SSMS with various dates and check that you are getting the correct results back. So run it for a couple of dates where there is actual data on that date and then a couple of dates where there is no data on the specific date to make sure it gets the next available data. (If I understood you each report execution should only return data for a single day.) Let me know how that check goes. – Alan Schofield May 14 '20 at 07:52
  • Coming back to this a few weeks late, thanks again for your help. The query works fine in SSMS, when I pass any date in the query, it correctly gives me the correct next date (even if it's not the next day). When I run the report in SSRS, it only works correctly when the "next date" is the exact next day. Again, it seems to me that when I click the "textbox action button", it bypasses the query/dataset and pushes the "next day" value directly to the report. Are you sure that pushing the action button sends the parameter back to the query/dataset? – Billy B Jun 02 '20 at 19:16
  • So, I actually figured out how do it on my own (with help from you). I essentially used two different lookup functions. I first used a lookup function to match the parameter date to the "increment" field of my query. I then used another lookup function to make the "increment value plus one" to the same query, and then passed that result through the "action button". Thanks again for the help! – Billy B Jun 02 '20 at 23:34
  • Glad you got it working but I did test my approach and it worked as expected so I'm not sure what was different. Anyway, if this answered your question please mark it as accepted. – Alan Schofield Jun 03 '20 at 08:43