TRANSFORM Function

Note: 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: https://community.infor.com/infor-birst/b/legacy-blog-posts/posts/advanced-transform-example-on-pivoting-rows-into-columns-at-report-runtime
STAT cannot be used inside a TRANSFORM statement.

Syntax

TRANSFORM(script)

This 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
         )

This 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
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
    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
        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
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
    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