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 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/01

Most 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"