When do we want it? NOW()

When building out the GWAC Dashboard, I ran into two separate problems. First, the current date was supposed to appear in the upper right hand corner of the dashboard. Second, I really needed to set up some complex logic for conditional triggers. In a twist that would make M. Night Shyamalan look twice, it turned out that solving the first problem would give me the answer to the second.

I see data sources, and they don't even know they're supposed to refresh.

This post is going to deal with the first, and considerably simpler, question. How do you get your dashboard to do something as simple as display today's date? This should be child's play. Excel even comes with a built-in function to do it, NOW(). Create a cell that contains =NOW(). Format it to the date format you like. Create a label, and point the label to that cell. Presto! Today's date appears on the dashboard. But, like the cake, it is a lie.

Why is my dashboard lying to me? Well, it's less that it's lying to you, and more that it's ignoring you. See, Xcelsius tries to make things run a tinier bit more efficiently on the back end by only updating the cells that reference cells that have changed. Since the =NOW() function doesn't reference anything else, the calculation engine doesn't bother to update the cell. So instead of returning the current date, it is returning the date the last time the data model was updated (i.e., when you exported the dashboard to a SWF file). And that is the only date it will ever display. (How often is a stopped calendar right? Just once, I think. Unless you see this guy.)

How do I fix it? Fortunately, if you were reading closely, the answer is fairly simple. Xcelsius will recalculate cells if they reference a cell that has changed. So that's all we have to do. Find a connection that refreshes when the dashboard loads (hopefully you have at least one of those to fill in prompt values and the like). Let us suppose, for the sake of illustration, that the first cell that this connection fills is A5. We know that the value of this cell is going to change when the dashboard loads. So, we just build a reference to it!

=IF(A5="I Love BO",NOW(),NOW())

The odds are low that A5 will ever actually equal "I Love BO". Which is very sad, when you think about it. But it doesn't really matter. If a miracle occurs and it does equal that, your cell will return NOW(). If the love goes unrequited, then your cell will still return NOW(). Either way, you win. Your label will now return today's date!

But wait! What is it really returning? Ah, grasshopper, you ask too soon. The answer to that question is, of course, the key to answering the second of our original problems. Come back on Wednesday, and we will explore how this peculiarity of calculation allows us to build triggers that fire under very specific circumstances.