Can you rotate through visible sub reports in report builder 3?

Question

I have one main report with several sub reports. Essentially I want to show each sub report in rotation for about 30 seconds before hiding the first one then showing the next and restarting again after all have had their time up.

Thanks

Answer 1

I 'think' you can do this, but there are some caveats.

You will need to setup a database table to store the current loop position, if you have several of these reports you could key it on report name for example.

(note these names are for the main report, nothing to do with the subreports)

ReportName     LoopPosition LoopMax
MyMainReportA        0         3
AnotherReport        7        10 

Add a dataset (lets call it dsLoop) to your main report that updates this value and returns it with something like. (Untested)

DECLARE @LoopPosition int
SELECT @LoopPosition =  CASE LoopPosition WHEN LoopMax THEN 1 ELSE LoopPosition + 1 END
    FROM myReportLoopTable 
    WHERE ReportName = 'MyMainReportA'

    UPDATE myReportLoopTable Set LoopPosition = @LoopPosition WHERE ReportName = 'MyMainReportA'

SELECT @LoopPosition as LPos

This code simply adds 1 to the LoopPosition or resets it to 1 if we've hit the maximum value. It then returns this value.

Now add a parameter pLoopPos to your main report (this can be a hidden parameter) and set it default value to our new dsLoop dataset.

Now change the hidden property of each subreport to only show the subreport when Parameters!pLoopPos.Value = x where x is the order of the subreport.

Now, when the report runs it will update the loop position and get the new value. The first subreport will show as the pLoopPos will be 1 . When your report refreshes (via the AutoRfresh property) the dsLoop dataset will be reevaluated which will run the code to update the value. The pLoopPos value will increase and the next subreport will be displayed.

You may have to force the parameter to always be refreshed (from the parameter properties).

PLEASE NOTE THIS IS UNTRIED AND UNTESTED. This is just off the top of my head so I suggest a simple test report before spending too much time trying to implement it.

UPDATE: 2018-04-10 Following on from your followup question, it looks like using the parameter wont work as it does not get refreshed. However you can use the value the dsLoop returns directly. To make the change, simply swap out

Parameters!pLoopPos.Value with =First(Fields!LPos.Value, "dsLoop")

Note: I modified the dsLoop query slightly to give the final result a name (LPos).

You should now be able to delete the parameter as its no longer used.