Versions Compared

Version Old Version 50 New Version Current
Changes made by

Scott Pearce

Scott Pearce

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Anchor
RangeParameterFormulae
RangeParameterFormulae
Range Parameter Formulae

Range parameter formulae contain a number of "Keyword=Value" lines. These lines are entered into the formula as comments so that Crystal Reports ignores them (but Jiwa knows to act upon them). Therefore, a typical line inside a range parameter formula would look like "//Keyword=Value" (without the surrounding quotes).

Below is a list of all of the keywords that Jiwa will recognize and act upon, if found inside a range parameter formula.

Keywords

Caption

The value (remember the format is Keyword=Value) is used as the description for the range parameter, and is presented to the user alongside the range parameter when the report is loaded.

Possible Values:

Any text

Example:

//Caption=Select Starting Part No.


Anchor
RangeParameterFormulaeType
RangeParameterFormulaeType
Type

This defines the basic type of the range parameter, such as string or number. This affects the values accepted by the formula (i.e. "a" is not valid for a "Type=Number" formula), and also the way the parameter is presented to the user in the ranges grid of the report screen.

 A full description of possible "Type" values is given below.

Possible Values:

String - A simple text box.

Number - A simple number box. Formatting such as the number of decimal places to display is controlled by other keywords such as "Decimal".

NumberCombo - A dropdown combo box from which the user may select 1 item.

Date - A date of the format dd/mm/yyyy.

CheckBox - A two state clickable box (tick or no tick).

Example:

//Type=String


The value may be "Yes" (case insensitive) or non-yes, or the keyword may not be used in the formula at all (which implies non-yes). If the value is the string "Yes", a clickable button is enabled alongside the range parameter value on the screen. When this button is clicked the Jiwa search screen is launched. The contents of the search screen and how the results of the search screen will be used are determined by the inclusion/values of other keywords (i.e."Select", "Order", and "Default"). 

Possible Values:

Yes

No

Any text (this implies that this range parameter value is NOT Jiwa searchable)

Example:

//Search=Yes


Select

If the range parameter has been defined as a searchable one (i.e. "//Search=Yes"), then the value for this keyword is combined with the value for the "Order" keyword (if one exists) to build the T-SQL Query string to be used by the search screen. The first item in the SELECT clause is what is returned to the ranges grid when a selection is made from the search screen.

Possible Values:

Any valid T-SQL query that excludes the ORDER BY clause (Ordering is handled by a separate keyword, "Order").

Example:

//Select=SELECT PartNo, Description FROM IN_Main


Order

If the range parameter has been defined as a searchable one (i.e. "//Search=Yes"), then the value for this keyword is combined with the value for the "Select" keyword to build the T-SQL Query string used by the search screen.

Possible Values:

Any valid T-SQL ORDER BY clause (including the "ORDER BY" syntax itself).

Example:

//Order=ORDER BY IN_Main.Partno


Min

If the range parameter type is numeric (i.e. Type=Number), then the value for the "Min" keyword dictates the smallest number that the user is allowed to input.

Possible Values:

Any numeric value.

Example:

//Min=0


Max

If the range parameter type is numeric (i.e. Type=Number), then the value for the "Max" keyword dictates the largest number that the user is allowed to input.

Possible Values:

Any numeric value.

Example:

//Max=100


Decimal

Controls the number of decimal places to allow in the range parameter.

Possible Values:

Any positive numeric value.

Example:

//Decimal=2


Add

This keyword is used to add values to a combo box type range parameter (i.e. //Type=NumberCombo). A formula may contain several "Add" lines.

Possible Values:

Of the form X, Y Where X is a positive integer, and Y is a string.

Example:

//ADD=1,Classification
//ADD=2,None


Default

The default value that should be used as the formula value. Depending on the "Type" of the formula, this value may be text, a number, or a T-SQL statement. There are some special keywords that can be used as the value for the "Default" keyword to represent a varying value (i.e. the current date). These special keywords are:


  • MachineDate:
    DateTime.Now.ToString("dd/MM/yyyy")

  • LoginDate:
    Manager.Database.SysDateTime.ToString("dd/MM/yyyy")

  • StartOfCurrentMonth:
    "01/" & DateTime.Now.Month.ToString & "/" & DateTime.Now.Year.ToString

  • EndOfCurrentMonth:
    Dim currentDateTime = Me.Manager.Database.SysDateTime
    Dim endOfCurrentMonth = New DateTime(currentDateTime.Year, currentDateTime.Month, DateTime.DaysInMonth(currentDateTime.Year, currentDateTime.Month))
    formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("ENDOFCURRENTMONTH ", endOfCurrentMonth.ToString("yyyy-MM-dd"))

  • StartOfCurrentCalendarYear:
    "01/01" & DateTime.Now.Year.ToString

  • EndOfCurrentCalendarYear:
    "31/12" & DateTime.Now.Year.ToString

  • StartOfFinancialYear:
    Dim currentStartDate As Date
    Manager.GeneralLedgerConfiguration.GetCurrentYearStartingDate(currentStartDate)
    currentStartDate.ToString("dd/MM/yyyy")

  • EndOfFinancialYear:
    Dim currentStartDate As Date
    Manager.GeneralLedgerConfiguration.GetCurrentYearStartingDate(currentStartDate)
    DateAdd(Microsoft.VisualBasic.DateInterval.Second, -1, currentStartDate.AddYears(1)).ToString("dd/MM/yyyy")

  • CurrentYearNo:
    Dim currentStartDate As Date
    Manager.GeneralLedgerConfiguration.GetCurrentYearStartingDate(currentStartDate)
    Dim yearType As GeneralLedger.Configuration.YearTypes
    Dim yearNo As Integer
    Dim yearStartDate As Date
    Dim yearEndDate As Date
    Manager.GeneralLedgerConfiguration.GetYearDetails(currentStartDate, yearType, yearNo, yearStartDate, yearEndDate)
    yearNo.ToString

  • Today:
    Manager.Database.SysDateTime.ToString("dd/MM/yyyy")

  • NextYearStartDate:
    Dim lastPeriodStartingDate As Date = Manager.GeneralLedgerConfiguration.GetLastPeriodStartingDate()
    formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("LASTPERIODSTARTDATE", lastPeriodStartingDate.ToString("yyyy-MM-dd"))

  • LastYearStartDate:
    Dim lastYearStartingDate As Date
    Manager.GeneralLedgerConfiguration.GetLastYearStartingDate(lastYearStartingDate)
    formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("LASTYEARSTARTDATE", lastYearStartingDate.ToString("yyyy-MM-dd"))

  • NextPeriodStartDate
    Dim nextPeriodStartingDate As Date = Manager.GeneralLedgerConfiguration.GetNextPeriodStartingDate()
    formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("NEXTPERIODSTARTDATE", nextPeriodStartingDate.ToString("yyyy-MM-dd"))

  • LastPeriodStartDate:
    Dim lastPeriodStartingDate As Date = Manager.GeneralLedgerConfiguration.GetLastPeriodStartingDate()
    formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("LASTPERIODSTARTDATE", lastPeriodStartingDate.ToString("yyyy-MM-dd"))

  • UTCOffset
    Dim UTCOffset As TimeSpan = TimeZoneInfo.Local.GetUtcOffset(DateTime.UtcNow)
    Dim UTCOffsetInMinutes = UTCOffset.TotalMinutes
    formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("UTCOFFSET", UTCOffsetInMinutes)

There is also a special function that can be used for date calculations:

  • DateAdd - Resolves to the result of DateAdd(DateInterval, DateNumber, DateBase). This works the same way as the DateAdd function in Visual Basic. Possible values for the DateInterval are:

 

yyyy - Year

q - Quarter

m - Month

y - Day of year

d - Day

w - Weekday

ww - Week of year

h - Hour

n - Minute

s - Second

 

Possible Values:

Any text

LoginDate

MachineDate

Today

DateAdd(X,Y,Z) (Where X is a valid VB DateAdd "interval", Y is amount to add, and Z is the date which the DateAdd operation is being performed on).

Examples:

//Default=DateAdd("m",1,Today)

//Default=SELECT PartNo FROM IN_Main ORDER BY IN_Main.PartNo ASC

//Default=Today

//Default=1

//Default=Enter some text here...


Hidden

If this Keyword exists and has a value of True (case insensitive), then the range parameter will not be presented to the user for input.

Possible Values:

True

False

Example:

//Hidden=True


Anchor
#SampleRangeParameterFormulae
#SampleRangeParameterFormulae
Sample Range Parameter Formulae

Below are a series of sample range parameter formulae - these samples cover each of the different types. For further examples, open up the standard reports that ship with Jiwa and browse the report source code.

String

//Type=String
//Caption=Enter your name:
//Default=


//Type= String
//Caption=Select Starting Part No.
//Search=YES
//Select=SELECT PartNo, Description FROM IN_Main
//Order=ORDER BY IN_Main.Partno
//Default=select PartNo FROM IN_Main ORDER BY IN_Main.PartNo ASC
''



//Type= String
//Caption=Select Ending Part No.
//Search=YES
//Select=SELECT PartNo, Description FROM IN_Main
//Order=ORDER BY IN_Main.Partno
//Default=select PartNo FROM IN_Main ORDER BY IN_Main.PartNo DESC
''


Number

//Type=Number
//Caption=Please enter number of days
//Default=5

//Type=Number
//Caption=Please enter number of hours
//Default=5.5
//Decimal=2


NumberCombo

//Type = NumberCombo
//Caption = Select Grouping
//ADD=1,Classification
//ADD=2,None
//Default=1
1


Date

//Type=Date
//Caption=Enter Start Date
//Default=Today

//Type=Date
//Caption=Enter End Date
//Default=DateAdd("m",1,Today)

//Type=Date
//Caption=Enter transaction date
//Default=01/01/2009

//Type=Date
//Caption=Enter transaction date


CheckBox

//Type = CheckBox
//Caption = Show 0 Value Rows
//Default=0
0


...