Display Dynamic Date/Time Using Embedded Excel in Dashboard Designer(4.1 SP 2)
(If you do not see a clock above, please visit www.zimblybo.blogspot.com)
Sometimes, we need to display dynamic date or time in a dashboard and directly utilizing excel functions such as today() or now() just does not give us dynamic results. This post is about how to make it dynamic.
The simple clock displayed above was created in Dashboard Designer (4.1 Service Pack 2)
Import Value Box and Play Selector into your canvas.
In the excel sheet available within Dashboard Designer do the following
- In an empty cell enter a dummy value, (in this case the value 1 in cell B2)
- In another cell, enter the following formula for it to return the current time (in this case cell B3) – “=if(B2*1<>B1,NOW(),NOW()” – This returns the value of NOW() whether it the condition is met or not, but the formula is there just to force a recalculation on a change in value in either B1 or B2 .
Map the Value box to reflect data from the cell in point 2 above (in this case cell B3)
- In the General tab, select insertion type as Row; map the cell used in Point 2 above as Source Data (in this case cell B3); map an empty cell as the Destination (in this case cell B1); set Insert On to Data Change and Interaction – This enables forced recalculation and a fresh insertion each time there is a change in the source data (which is every 1 second in our example).
- In the Behavior tab, set Play Time to 1 second; enable Auto Play – This loops the play selector to play and display the data (which is the timestamp or the excel function NOW() in our example).
- In the Appearance tab, keep Transparency at 100% – This hides the play selector but keeps it active.
Now() in Dashboard Designer
Now() in Xcelsius
Today() in Dashboard Designer
Today() in Xcelsius