VBScripting 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 ComponentOne Samples folder.