4

I have been tasked with creating a dashboard to be displayed on TV monitors. This dashboard has 5 different charts that need to be displayed in a slideshow manner - in other words, one chart at a time, rotating to the next over a timed interval.

I used a solution posted on here with the visibility property =IIf(Second(Now()) >= 48 AND Second(Now()) <= 60, False, True) and so on with the report auto-refreshing at 12 second intervals.

However, my manager came back to me with the feedback that 12 seconds was too short of an interval, and that he would like to see each report for 20 seconds before it is rotated to the next one.

Does anyone have any ideas on how to make this possible?

Thanks so much.

Bruno Franco
  • 2,028
  • 11
  • 20
Chris Perry
  • 43
  • 5

2 Answers2

2

You want to move from a 60 second cycle to a 100 second cycle.

Based on your existing code, you could use something like:

=IIf(DateDiff(DateInterval.Second, Today(), Now()) Mod 100 >= 80
    AND DateDiff(DateInterval.Second, Today(), Now()) Mod 100 <= 99
  , False
  , True)

You get the 100 second cycle by taking the number of seconds since the beginning of the day Modulo 100. You can then break this down into buckets of twenty in your expression instead of buckets of 12.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • Thank you so much, that worked perfectly! I was having trouble yesterday wrapping my mind around the modulo concept and how I could apply that to a solution - thanks so much for your help. – Chris Perry Sep 05 '14 at 14:32
  • Ian, I am attempting to add another chart to this rotating deck - so I changed your code to Mod 120 and added the additional time bucket. However, for some reason Report Builder seems to be breaking when increasing the number of intervals to over 5. That is to say, it will display random charts when it refreshes, then display no charts, then display one chart, and so on. Overall it is not functioning like it should be, where it displays one chart at a time, in order, like it did when I only had Mod 100 and 5 time intervals. Any ideas on why I cannot get this to function properly? – Chris Perry Oct 08 '14 at 19:53
0

I have expanded on Ian Preston's solution with custom code. I basically wanted to make the solution fully expandable.

Use this in the Row Visibility Expression: =Code.SubReportHidden(RowNumber("Tablix1"), CountRows("Tablix1"), Parameters!ReportRefresh.Value, Now())

This is a custom parameter to change the refresh on the fly. You will also need to reference it in the Reports AutoRefresh property.

Parameters!ReportRefresh.Value

Add this to your reports Custom Code:

Public CurrentTimeInSeconds As Long
Private MinCycleRange As Long
Private MaxCycleRange As Long
Private PageCount As Integer
Private Page As Integer
Private FullCycle As Long
Private CurrentTime As DateTime

Public Function SubReportHidden(ByVal page As Integer, ByVal pageCount As Integer, 
  ByVal interval As Long, ByVal now As DateTime) As Boolean

If page.Equals(0) Then Throw New ArgumentOutOfRangeException("page")
If pageCount.Equals(0) Then Throw New ArgumentOutOfRangeException("pageCount")
If interval.Equals(0) Then Throw New ArgumentOutOfRangeException("interval")

Me.PageCount = pageCount
Me.Page = page
FullCycle = interval * pageCount
CurrentTime = now
SetCurrentTimeInSeconds()

SetMaxCycleRange()
SetMinCycleRange()

Dim visable As Boolean = True
If InRange() Then
  visable = False
End If
Return visable
End Function

Private Function InRange() As Boolean
Dim insideRange As Boolean = False
If CycleProgressPercent() > MinCycleRange AndAlso CycleProgressPercent() <= 
  MaxCycleRange Then
  insideRange = True
End If
Return insideRange
End Function

Private Function CycleProgressInSeconds() As Long
  Return CurrentTimeInSeconds Mod FullCycle
End Function

Public Function CycleProgressPercent() As Integer
  Return CInt(CycleProgressInSeconds() / FullCycle * 100)
End Function

Private Sub SetCurrentTimeInSeconds()
  CurrentTimeInSeconds = DateDiff(DateInterval.Second, DateTime.Today, CurrentTime )
End Sub

Private Sub SetMinCycleRange()
  MinCycleRange = CLng(((Page - 1) / PageCount) * 100)
End Sub

Private Sub SetMaxCycleRange()
  MaxCycleRange = CLng(((Page / PageCount) * 100))
End Sub

I have taken the time to rewrite the code as well, check out my GitHub for a testable solution. GitHubSSRS_Item_Cycle_Demo

Jeffrey Stines
  • 1