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)