Application management : Tools : Data Aggregation : Configuring Data aggregation properties : Aggregation properties BO for ReportSX and CalculateSX : Variables to use in SQL
Variables to use in SQL
You can define your own variables, based on Planon ProCenter definition (container) and data level BOs, to use in your SQL. The following types are supported:
• STRING
• INTEGER
• DECIMAL
• REFERENCE (referenced field)
• DATETIME
• SYSCODE (primary key field)
• CODESCODENAME (pick list code descriptive)
• CODESNAME (pick list descriptive)
• DATETIME_PROPERTY
• DATETIME_TRANSACTION
• BOOLEAN
You can define more than one variable. These variables can be used in the SQL for data definitions and in other variables beginning and ending with ‘&’ character (see examples). These variables can be used as parameters in SQL for both data and details level and as parameters in other variable definitions.
There are 4 different kinds of variables you can define and use:
• Field (variable contains field value of definition (container) of data level BO)
• Constant (variable contains a constant value)
• SQL
• Expression
Key
Value example / Info
Extension
Variable.Field.­<VariableName>.­<TYPE>
Variable.Field.Property­Ref.­REFERENCE.INTEGER­=Data.BO.Name.Free­String12 (&PropertyRef&) Variable.Field.­ContainerRef.­REFERENCE.INTEGER = Data.BO.Name.­DataAggregation­DefinitionRef (&ContainerRef&) Variable.Field.StartDate.­DATETIME = Container.BO.­Name.BeginDate (&StartDate&)
Info
• Variable to be populated.­ Format: Variable.Field.­<VariableName>.­<TYPE>=<Field­Name>
• ReportSX: The value should contain the BO name and the field name.
For example : Data.BO.Name.­PropertyRef
• CalculateSX: The value should only contain the field name.
For example PropertyRef
• If it is a reference field then the reference field type should be configured as shown in the following example :
Variable.Field.­PropertyRef.­REFERENCE.­INTEGER
• ReportSX: BO can only be Data.BO.Name (data level fields) or Container.BO.Name.(definition (container) level)
• CalculateSX: only fields of the Calculate BO can be defined.
ReportSX/ CalculateSX
Variable.Constant.­<VariableName>.­<TYPE>
Variable.Constant.­HighVatRate.­DECIMAL=1.21 (&HighVatRate&) Variable.Constant.­UnitToLetOut.STRING­=UsrUnitToLetOut (&UnitToLetOut&) Variable.Constant.­RefDateSpec.DATE=­20/01/2014 (&RefDateSpec&)
Info
• Variable to be populated. Format: Variable.Constant.­<VariableName>.­<TYPE>­=<constant value>
• Only types STRING, INTEGER, DECIMAL, DATE and DATETIME are allowed for this type of variable.
• No quotes needed for STRING, DATE and DATETIME values
• Decimal separator for DECIMAL type always . (dot)
• Format value of DATE type: "dd/MM/yyyy"
• Format value of DATETIME type: "dd/MM/yyyy hh:mm:ss"
ReportSX/ CalculateSX
Variable.SQL.­<VariableName>­. <TYPE>
Variable.SQL.­WeeklyRateCompany­Car.DECIMAL= { Select WEEK From TRFGRP Where CODE = ‘CC’ } (&WeeklyRate­CompanyCar&)
Info
• Variable to be populated. Format: Variable.SQL.­<VariableName>.­<TYPE>=<­SQL-select statement>
• Only types STRING, INTEGER, DECIMAL, DATE and DATETIME are allowed for this type of variable.
• SQL string can be given as one line with no carriage returns or can be divided over more lines. In the last method the query must be started with { character and ended with }
ReportSX/ CalculateSX
Variable.Expre­ssion.<Variable­Name>.;<TYPE>
Variable.Expression.­WeeklyRateCompCar­InclVat.DECIMAL=& WeeklyRate­CompanyCar& * &­ HighVatRate­& (&WeeklyRate­CompCarInclVat&) Variable.Expression.­NrWPTotal.DECIMAL=­&NrFlex­& + &NrFixed& (&NrWPTotal&)
Info
• Variable to be populated. Format: Variable.Expression.­ <VariableName>. <TYPE>=­<expression>
• Add/Subtract support: STRING, INTEGER, DECIMAL
• Multiply/Divide support: INTEGER, DECIMAL
• Only simple expressions with two operands and one operator (+, -, * or /) are allowed here.
ReportSX/ CalculateSX