Eval Tokens
#EVAL tokens can be used to evaluate an expression based on a strongly typed expression language via the Fast Lightweight Expression Evaluator (Flee) library. #EVAL tokens were created initially to do some simple runtime date manipulation such as StartOfMonth, EndOfMonth for SQLView Pro calendar parameter default values but I've found them to be useful in many areas including string formatting as well. I suspect that #EVAL tokens will be most useful in the SQLView Pro product with respect to template reports but other uses may be found as well.
Syntax
[EVAL# VALUE="expression"] where
expression is the expression to be evaluated using the
Flee Language.
It is important to note that expressions can also have simple tokens as part of the expression so you can work on dynamic data as well. For instance you could use the following statement in a SQLView Template report to format a field as currency. This example assumes you have a field in your query named
Sales.
[#EVAL VALUE='String.Format("{0:C}", [Sales])']Additional Functions
In addition to the
standard Flee language reference, I have also added some handy functions that allow you to generate some simple dates.
Dates
StartOfMonth(DateTime)
- given a date, provides the first date of that month
[#EVAL VALUE="StartOfMonth(DateTime.Today)"]
EndOfMonth(DateTime)
- given a date, provides the last date of that month
[#EVAL VALUE="EndOfMonth(DateTime.Today)"]
StartOfYear(DateTime)
- given a date, provides the first date of that year
[#EVAL VALUE="StartOfYear(DateTime.Today)"]
EndOfYear(DateTime)
- given a date, provides the last date of that year
[#EVAL VALUE="EndOfYear(DateTime.Today)"]
StartOfWeek(DateTime,DayOfWeek)
- given a date and dayofweek, provides the first date of that week
[#EVAL VALUE="StartOfWeek(DateTime.Today,DayOfWeek.Sunday)"]
EndOfWeek(DateTime,DayOfWeek)
- given a date and dayofweek, provides the last date of that week
[#EVAL VALUE="EndOfWeek(DateTime.Today,DayOfWeek.Sunday)"]
Additional Examples
Here are some additional examples that will demonstrate how you can use the #EVAL tokens.
Math
Adding Numbers
[#EVAL VALUE="1+3"]
[#EVAL VALUE="[Column1]+[Column2]"] - Column1 and Column2 are numeric fields in your query
Boolean
[#EVAL VALUE="1=1"] - evaluates to True
[#EVAL VALUE="[Column1]=1"]
Formatting
[#EVAL VALUE="DateTime.Today.ToString("YYYY MMM dd")"] - formats as 2010 May 09
[#EVAL VALUE="String.Format("{0:C}",1.23)"] - formats as currency $1.23
[#EVAL VALUE="StartOfYear(DateTime.Today).ToString("YYYY/MMM/dd")"] - formats as 2010/Jan/01Most of the standard .NET formatting codes should work as outlined here,
http://msdn.microsoft.com/en-us/library/az4se3k1.aspx.
Conditional
If
[#EVAL VALUE="If(String.Equals("[TOKEN1]","VALUE1"), "equal", "not equal")"]
Results in "equal" if [TOKEN1] equals "VALUE1" or "not equal" if [TOKEN1] is not equal to "VALUE1"
If with In
[#EVAL VALUE="If(100 in (100,200,300), "in", "not in")"]
Results in "in"