VBScript with ExpressionEditor

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:

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:

Reserved symbols

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

Built-in functions

The VBScript built-in functions are listed below:

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:

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