VBScript expressions are widely used throughout a report definition to retrieve, calculate, display, group, sort, filter, parameterize, and format the contents of a report. Some expressions are created for you automatically (for example, when you drag a field from the Toolbox onto a section of your report, an expression that retrieves the value of that field is displayed in the text box). However, in most cases, you create your own expressions to provide more functionality to your report.

Note the following differences between VBScript expressions and statements:

• Expressions return values, you can assign them to things like Field.Text, for example:
   Field1.Text.Expression = "iif( 1=1, 1+2, 1+3 )"
  
• Statements don't return values. You can assign them to event properties like OnFormat. For example:
  c1FlexReport.OnOpen = "if 1=1 then msgbox("OK!!!") else msgbox("ooops")"
  

C1FlexReport relies on VBScript to evaluate expressions in calculated fields and to handle report events.

VBScript is a full-featured language, and you have access to all its methods and functions when writing C1FlexReport expressions. For the intrinsic features of the VBScript language, refer to the Microsoft Developer's Network (MSDN).

Global Scripts can be written in the new VBScript Editor. This editor allows users to define VBScript functions and subroutines that are accessible throughout the report. To directly access the VBScript Editor, press F7 and to close the editor and save the changes, use the shortcut key Ctrl+W. Users can switch between scripts and also change options such as fonts or colors within the editor. The editor also makes the scripting experience intuitive and easy for developers with advanced features such as syntax check, pre-defined VBScript functions, and rearranged scripting functions.

To write global scripts using VBScript Editor option,

1. Go to Home Tab of FlexReportDesigner.
2. Click VBScript Editor and write desired global script; for example,

        function Backcolor()
        Detail.Backcolor=rgb(255,0,0)
        end function

You can also write global scripts using GlobalScripts property of FlexReportDesigner as follows:

1. Select the report in which you want to write global script.
2. Go to the GlobalScripts property of the report and then click ellipsis. This opens VBScript Editor dialog box.
3. Write the global script as above, in the VBScript Editor. So, you have defined a global function 'Backcolor()', which can be used throughout the report.

 

The VBScript Editor has the following additional features:

• IntelliSense: Provides auto code completion prompts for the scripts supported by VBScript Editor. IntelliSense in VBScript Editor has following features:
  • The IntelliSense window that displays a context-dependent list of available words also displays a detailed help on VBScript functions and keywords in a small tooltip or help window. The italic font on the detailed help basically shows the category to which the current item belongs (such as 'VBScript function', 'C1FlexReport aggregate script function', .NET object property, and so on).
  • On editing DataSource.Filter, the editor opens as Expression Editor - DataSource.Filter and IntelliSense shows keywords or functions available in that with corresponding help.
  • Icons associated with IntelliSense entries indicate the type of the entry. The icons' color palette is different for VBScript, report built-in stuff, and DataSource.Filter.
  • When a user types and Intellisense window is opened, the list is filtered according to the letters being typed for example, typing 't' will only show words that contain the letter 't', typing 'te' will narrow the list to words that contain 'te', and so on.
  • Backspace in the IntelliSense window undoes the last filter.
  • Pressing square bracket '[' shows the list of available db fields.
  • Pressing dot '.' after the name of an object such as a report, field, or section shows the .NET properties available for that object
  • Pressing Ctrl+J, Ctrl+Space, or a letter after a non-letter character shows the list of available VBScript functions, keywords, etc. depending on the context.
  
  
• Split Window: Lets you view or write two same or different scripts in single VBScriptEditor. By default, the VBScript editor opens as a single window.
  
  To switch to Split Window
  Switch to the split window mode by clicking the split window glyph and dragging it down to open another editor at the top. The windows can be resized by dragging the divider between the windows.
  
  To switch back to the single window
  Click the 'x' glyph on the top right corner of the window to close the top window, turn the split mode off, and zoom out the bottom window. The enabled or disabled state of ribbon buttons depends on the current window, which is shown with a light green caption bar. The split window mode has following additional functionalities:
  • Switch between the two windows by pressing F6.
  • Hide the top window in a split window mode by dragging the split window glyph or the divider line high enough across the top window.

C1FlexReport extends VBScript by exposing additional objects, variables, and functions. These extensions are described in the following sections.

Aggregate Functions

Aggregate functions are used to summarize data over the group being rendered. When used in a report header field, these expressions return aggregates over the entire dataset. When used in group headers or footers, they return the aggregate for the group.

All FlexReport aggregate functions take two arguments:

• A string containing a VBScript expression to be aggregated over the group.
• An optional string containing a VBScript expression used as a filter (domain aggregate). The filter expression is evaluated before each value is aggregated. If the filter returns False, the value is skipped and is not included in the aggregate result.

FlexReport defines the following aggregate functions:

Function	Description
Avg	Average value of the expression within the current group. For example, the following expression calculates the average sales for the whole group and the average sales for a certain type of product: Avg(SalesAmount) Avg(SalesAmount, ProductType = 3)
Sum	Sum of all values in the group.
Count	Count of records in the group with non-null values. Use an asterisk for the expression to include all records. For example, the following expressions count the number of employees with valid (non-null) addresses and the total number of employees: Count(Employees.Address) Count(*)
CountDistinct	Count of records in the group with distinct non-null values.
Min, Max	Minimum and maximum values for the expression. For example: "Min Sale = " & Max(SaleAmount)
Range	Range between minimum and maximum values for the expression.
StDev, Var	Standard deviation and variance of the expression in the current group. The values are calculated using the sample (n-1) formulas, as in SQL and Microsoft Excel.
StDevP, VarP	Standard deviation and variance of the expression in the current group. These values are calculated using the population (n) formulas, as in SQL and Microsoft Excel.
Median	Returns median from the values in the group.
Mode	Returns mode from the values in the group.

To use the aggregate functions, add a calculated field to a Header or Footer section, and assign the expression to the field's Text property.

For example, the "Employee Sales by Country" report in the sample NWind.xml file contains several aggregate fields. The report groups records by Country and by Employee.

The SalespersonTotal field in the Footer section of the Employee group contains the following expression:

=Sum([SaleAmount])

Because the field is in the Employee group footer, the expression returns the total sales for the current employee.

The CountryTotal and GrandTotal fields contain exactly the same expression. However, because these fields are in the Country group footer and report footer, the expression returns the total sales for the current country and for the entire recordset.

You may need to refer to a higher-level aggregate from within a group. For example, in the "Employee Sales by Country" report, there is a field that shows sales in the current country as a percentage of the grand total. Since all aggregates calculated within a country group refer to the current country, the report cannot calculate this directly. Instead, the PercentOfGrandTotal field uses the following expression:

=[CountryTotal]/[GrandTotal]

CountryTotal and GrandTotal are fields located in the Country and Report Footer sections. Therefore, CountryTotal holds the total for the current country and GrandTotal holds the total for the whole recordset.

It is important to realize that evaluating aggregate functions is time-consuming, since it requires the control to traverse the recordset. Because of this, you should try to use aggregate functions in a few calculated fields only. Other fields can then read the aggregate value directly from these fields, rather than evaluating the aggregate expression again.

For example, the "Employee Sales by Country" report in the NorthWind database has a detail field, PercentOfCountryTotal, that shows each sale as a percentage of the country's total sales. This field contains the following expression:

=[SaleAmount]/[CountryTotal]

SaleAmount is a reference to a recordset field, which varies for each detail record. CountryTotal is a reference to a report field that contains an aggregate function. When the control evaluates this expression, it gets the aggregate value directly from the report field, and does not recalculate the aggregate.

For the complete report, see report "Employee Sales by Country" in the Nwind.xml report definition file, which is available in the Documents\ComponentOne Samples\WinForms\vx.0\FlexReport\CS\FlexReportExplorer\Data folder.

VBScript Elements

The following tables detail VBScript elements, objects, and variables.

Operators

The following table contains the VBScript operators:

Operator	Description
And	Performs a logical conjunction on two expressions.
Or	Performs a logical disjunction on two expressions.
Not	Performs a logical disjunction on two expressions.
Mod	Divides two numbers and returns only the remainder.

Reserved symbols

The following table contains the VBScript reserved symbols and how to use them:

Keyword	Description
True	The True keyword has a value equal to -1.
False	The False keyword has a value equal to 0.
Nothing	Used to disassociate an object variable from any actual object. To assign Nothing to an object variable, use the Set statement, for example: Set MyObject = Nothing Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to any actual object. When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing, either explicitly using Set, or implicitly after the last object variable set to Nothing.
Null	The Null keyword is used to indicate that a variable contains no valid data.
vbCr	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbCrLf	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbLf	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbFormFeed	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbNewLine	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbNullChar	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbTab	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbVerticalTab	When you call print and display functions, you can use the following constants in your code in place of the actual values.
vbBlack	Black. Value = 0x0.
vbRed	Red. Value = 0xFF.
vbGreen	Green. Value = 0xFF00.
vbYellow	Yellow. Value = 0xFFFF.
vbBlue	Blue. Value = 0xFF0000.
vbMagenta	Magenta. Value = 0xFF00FF.
vbCyan	Cyan. Value = 0xFFFF00.
vbWhite	White. Value = 0xFFFFFF.

Built-in functions

The VBScript built-in functions are listed below:

Abs	Date	Iif	Minute	Sign
Acos	DateAdd	InputBox	Month	Space
Asc	DateDiff	InStr	MonthName	Sqr
Asin	DatePart	InStrRev	MsgBox	StrComp
Atn	DateSerial	Int	Now	String
CBool	DateValue	IsDate	Oct	Tan
CByte	Day	IsEmpty	Pi	Time
CCur	Exp	IsNull	Replace	Timer
CDate	Fix	IsNumeric	RGB	TimeSerial
CDbl	Format	IsObject	Right	TimeValue
Chr	FormatCurrency	LCase	Rnd	Trim
CInt	FormatDateTime	Left	Round	TypeName
CLng	FormatNumber	Len	RTrim	UCase
Cos	FormatPercent	Log	Second	WeekDay
CSng	Hex	LTrim	Sgn	WeekDayName
CStr	Hour	Mid	Sin	Year

For more information on the VBScript functions, see the MSDN documentation.

The key features of VBScript that are part of C1FlexReport are as follows:

• Aggregate functions (Sum, Average, StDev, Var, Count, and so on)
• Report and Database field names
• Page/Pages variables
• Report objects
• String functions (Chr, Format, and so on)
• Data Conversion (CBool, CByte, an so on)
• Math functions (cos, sin, and so on)
• Date/Time functions (DateAdd, Hour, and so on)
• Functions and Subs
• Conditional statements
• Built-in functions (Like and In)

Built-in script functions, Like and In have functionality similar to SQL operators LIKE and IN and return True or False.
Like(str, template): Compares 'str' to 'template', which can contain wildcard '%'. Some examples of Like function are as follows:

• Like("abc", "%bc") returns True.
• Like("abc", "%bcd") returns False.
• Like("abc", "ab%") returns True.
• Like("abc", "abd%") returns False.
• Like("abc", "%b%") returns True.
• Like("abc", "%d%") returns False.
• Like("abc", "abc") returns True.
• Like("abc", "abcd") returns False.
• Like("Abc", "abc") returns False.

In(obj, obj1, ... objN): Tests whether 'obj' is among objects 'obj1', ... , 'objN'. Some examples of In function are as follows:

• In(1,1,2,3) returns True.
• In(1,2,3) returns False.
• In("a", "a", "b", "c") returns True.
• In("a", "b", "c") returns False.
• In("A", "a", "b", "c") returns False.

As you can observe, both the functions are case-sensitive, so "abc" is not the same as "Abc".

Note that the following VBScript features are not supported in C1FlexReport:

• Arrays
• Select/Case statements

Statement keywords

The VBScript statement keywords are listed below:

If	ElseIf	To	While	Dim
Then	EndIf	Next	Wend	Redim
Else	For	Step	Const

Report Field Names

Names of Field objects are evaluated and return a reference to the object, so you can access the field's properties. The default property for the Field object is Value, so by itself the field name returns the field's current value. For example:

MyField.BackColor = RGB(200,250,100)
MyField.Font.Size = 14
MyField * 2 ' (same as MyField.Value * 2)

Report Section Names

Names of Section objects are evaluated and return a reference to the object, so you can access the section's properties. The default property for the Section object is Name. For example:
If Page = 1 Then [Page Footer].Visible = False

Database Field Names

Names of fields in the report's dataset source are evaluated and return the current field value. If a field name contains spaces or periods, it must be enclosed in square brackets. For example:

OrderID
UnitsInStock
[Customer.FirstName]
[Name With Spaces]

Report Variables

Page

The page variable returns or sets the value of the Page property. This property is initialized by the control when it starts rendering a report, and is incremented at each page break. You may reset it using code. For example:

If Country <> LastCountry Then Page = 1
LastCountry = Country


Pages

The pages variable returns a token that gets replaced with the total page count when the report finishes rendering. This is a read-only property that is typically used in page header or footer fields. For example:
"Page " & Page & " of " & Pages

Report Object

The report object returns a reference to the control object, so you can access the full C1FlexReport object model from your scripts and expressions. For example:

"Fields: " & Report.Fields.Count

Cancel

Set Cancel to True to cancel the report rendering process. For example:

If Page > 100 Then Cancel = True