TRANSFORM Function
Important: TRANSFORM is not supported in Visualizer scripts.
Report-level transformations provide a way of directly manipulating or even creating from scratch the report-level result set. TRANSFORM operates row-by-row over the original result set from the query, using WRITERECORD to output new rows as results.
TRANSFORM functions just like an ETL Services script. In essence, the entire result set is re-written as a result of the TRANSFORM statement. All the input and output columns are identical to those from the original result set.
Tips:
- Only one transform is allowed per logical query.
- See the Advanced Transform Example in the Birst Community.
- STAT cannot be used inside a TRANSFORM statement.
Syntax:
TRANSFORM(script)
The following example is a very basic transform that writes out odd row numbers.
TRANSFORM(
DIM [Odd] AS INTEGER = 0
IF (Integer([Odd]/2)*2 = [Odd]) THEN
[Sum: Quantity]=[Sum: Quantity]
WriteRecord
END IF
[Odd] = [Odd] + 1
)
The following is a more sophisticated example that does complex aggregations:
TRANSFORM(
Dim [AmountIs] As Float
Dim [AmountWas] As Float
Dim [StageWas] As Varchar(60)
Dim [StageIs] As Varchar(60)
Dim [CloseDateWas] As DateTime
Dim [CloseDateIs] As DateTime
Dim [CreatedDateWas] As DateTime
Dim [CreatedDateIs] As DateTime
Dim [InPeriodIs] As Integer
Dim [InPeriodWas] As Integer
Dim [HasIs] As Integer = 0
Dim [HasWas] As Integer = 0
Dim [New] As Float = 0.0
Dim [Won] As Float = 0.0
Dim [Lost] As Float = 0.0
Dim [Moved In] As Float = 0.0
Dim [Moved Out] As Float = 0.0
Dim [Increased] As Float = 0.0
Dim [Decreased] As Float = 0.0
Dim [Begin] As Float = 0.0
Dim [End] As Float = 0.0
Dim [CurOpty] As Varchar(20) = Null
Dim [PerStart] As DateTime = DateTime(GetPromptValue('Period Start Date'))
Dim [PerEnd] As DateTime = DateAdd(Day,1,DateTime(GetPromptValue('Period End Date')))
Dim [AsOf] As DateTime = DateTime(GetPromptValue('As of Date'))
Dim [Compare] As DateTime = DateTime(GetPromptValue('Compare Date'))
IF [CurOpty] Is Not Null AND [CurOpty] <> [Opportunity ID] THEN
[InPeriodIs]=0
[InPeriodWas]=0
IF [HasIs]=1 THEN
IF [CloseDateIs]>=[PerStart] AND [CloseDateIs]<[PerEnd] THEN
[InPeriodIs]=1
IF [StageIs]<>'Closed Lost' THEN
[End]=[End]+Float([AmountIs])
END IF
END IF
END IF
IF [HasWas]=1 THEN
IF [CloseDateWas]>=[PerStart] And [CloseDateWas]<[PerEnd] THEN
[InPeriodWas]=1
If [StageWas]<>'Closed Lost' THEN
[Begin]=[Begin]+Float([AmountWas])
END IF
END IF
END IF
If [InPeriodIs]=1 AND [HasWas]=0 AND [CreatedDateIs]>[Compare] THEN
[New] = [New] + Float([AmountIs])
ELSEIF [InPeriodIs]=1 AND [StageIs]='8. Implemented' AND [StageWas]<>'8. Implemented' THEN
[Won] = [Won] + Float([AmountIs])
ELSEIF [InPeriodWas]=1 AND [StageIs]='Closed Lost' AND [StageWas]<>'Closed Lost' THEN
[Lost] = [Lost] - Float([AmountWas])
ELSEIF [InPeriodWas]=1 AND [InPeriodIs]=0 THEN
[Moved Out] = [Moved Out] - Float([AmountWas])
ELSEIF [InPeriodWas]=0 AND [InPeriodIs]=1 THEN
[Moved In] = [Moved In] + Float([AmountIs])
ELSEIF [InPeriodWas]=1 AND [InPeriodIs]=1 THEN
IF [AmountIs]>[AmountWas] THEN
[Increased] = [Increased] + Float([AmountIs]) - Float([AmountWas])
ELSEIF [AmountIs]<[AmountWas] THEN
[Decreased] = [Decreased] + Float([AmountIs]) - Float([AmountWas])
END IF
END IF
[HasIs]=0
[HasWas]=0
END IF
IF DateDiff(Day,[Snapshot Date],[AsOf])=0 THEN
[AmountIs]=Float([Sum: Historical Amount])
[StageIs]=[Stage]
[CloseDateIs]=[Opportunity Close Date]
[CreatedDateIs]=[Opportunity Created Date]
[HasIs]=1
ELSEIF DateDiff(Day,[Snapshot Date],[Compare])=0 THEN
[AmountWas]=Float([Sum: Historical Amount])
[StageWas]=[Stage]
[CloseDateWas]=[Opportunity Close Date]
[CreatedDateWas]=[Opportunity Created Date]
[HasWas]=1
END IF
[CurOpty]=[Opportunity ID]
COMPLETE
{repeated logic omitted for brevity}
END COMPLETE)