Display a calcuated datetime in a Placeholder Label

#1

Is it possible to caclulate and format a custom datetime value using Placeholder Labels in a report?


Right now I have the [Current Date] displayed in my report header, but I would also like to show the last refresh date for my data (which in my case is the previous Sunday). Ideally, I would just add another label to the report, and figure out some .NET syntax to display the most recent date that is less than [Current Date] with DayOfWeek = 0 (for Sunday).


This field isn't being used for anything other than some nice-to-know information on the report, so I am thinking a label would be preferable to going back to my cube and adding a 'refresh date' dimension. I'm more DBA than web developer, so I usually end up going all the way back to my sql select and doing the work there, which I know is wrong but at least it gets done.


I understand labels in DBI can accept custom formatting, but not sure about performing a calculation like this. If it is not possible, what would be the correct (read: easiest) way to insert a calculated date on a label?

#2

The label formatting is not capable of handling such calculations. However, it is possible to create a calculated element inside the data cube. Calculated elements use DundasScript, which supports common C# code and functions, including the datetime functions used for this calculation.


We actually have some examples on our support site about handling datetime values through a calculated element:

https://www.dundas.com/support/learning/documentation/data-metrics/transforms/common/calculated-element#h5-expression-samples

#3

I had a need for a how long ago something was updated. I converted some Java Script into C# and use this as a calculate element in the Data Cube:

var seconds = (DateTime.Now - $Modified$).TotalSeconds;

var interval = Math.Floor(seconds / 31536000);

if (interval > 1) {
return interval.ToString() + " Years ago";
}
interval = Math.Floor(seconds / 2592000);
if (interval > 1) {
return interval.ToString() + " months";
}
interval = Math.Floor(seconds / 86400);
if (interval > 1) {
return interval.ToString() + " Days ago";
}
interval = Math.Floor(seconds / 3600);
if (interval > 1) {
return interval.ToString() + " Hours ago";
}
interval = Math.Floor(seconds / 60);
if (interval > 1) {
return interval.ToString() + " Minutes ago";
}
return Math.Floor(seconds).ToString() + " Seconds ago";


Then use that element in a Data Label.