Forum Discussion

cons1's avatar
cons1
New Contributor III
2 years ago
Solved

FdxExecuteStageTargetTimePivot

Hi,  I am trying to explore the FDXExecuteStageTargetTimePivot if I can use it to show stage data. Does anyone have the business rule and share Thank you
Analytic Blend
  • Nikpowar97's avatar
    Nikpowar97
    2 years ago

    Hey,

     

    You can use, 
    Dim WFProfileName As String = BRApi.Workflow.Metadata.GetProfile(si,si.WorkflowClusterPk.ProfileKey).Name
    Dim WFScenarioName As String = ScenarioDimHelper.GetNameFromId(si, si.WorkflowClusterPk.ScenarioKey)
    Dim WFTimeName As String = BRApi.Finance.Time.GetNameFromId(si, si.WorkflowClusterPk.TimeKey)

    Dim ParentWFPRofile As String = BRApi.Workflow.Metadata.GetParent(si,WFProfileName).Name

    Dim dt As datatable = Brapi.Import.Data.FdxExecuteStageTargetTimePivot(si,ParentWFProfile,WFScenarioName,WFTimeName,WFTimeName,True,True,"AcT= 'TestAcc' and Si = '"& si.UserName &"'",1000,True)

    For Each Dc As DataColumn In dt.Columns
           Brapi.ErrorLog.LogMessage(si,"NIKCLMN: " & dc.ColumnName)
    Next

    Columnnames in the table to apply filter, Is tested this for 1 time period, Amount column will be with timeperiod as it is picvoted, So if we use 
    Brapi.Import.Data.FdxExecuteStageTargetTimePivot(si,ParentWFProfile,WFScenarioName,WFTimeName,WFTimeName,True,True,"AcT= 'TestAcc' and Si = '"& si.UserName &"'",1000,True) 'Use generic time columnnames boolean flag

    'Rt,Si,Lb,Tv,EtT,PrT,CnT,SnT,VwT,AcT,FwT,OgT,IcT,U1T,U2T,U3T,U4T,U5T,U6T,U7T,U8T,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A121,A14,A15,A16,A17,A18,A19,A20,Time1

    if we use,

    Brapi.Import.Data.FdxExecuteStageTargetTimePivot(si,ParentWFProfile,WFScenarioName,WFTimeName,WFTimeName,True,False,"AcT= 'TestAcc' and Si = '"& si.UserName &"'",1000,True) 'Use generic time columnnames  boolean  flag

    'Rt,Si,Lb,Tv,EtT,PrT,CnT,SnT,VwT,AcT,FwT,OgT,IcT,U1T,U2T,U3T,U4T,U5T,U6T,U7T,U8T,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A121,A14,A15,A16,A17,A18,A19,A20,T2023M1

WernerN's avatar
WernerN
Contributor
26 days ago

Thank you Nikpowar97, you saved me nights and nights of research and i probably still would not have figured out.
For the geeks: If you want to analyze the data table the above fdx returns here is the next few lines in my clone of the Nikpowar97's code:

If dt Is Nothing Then api.LogMessage($"{RULENAME} no dt for fdx")
api.LogMessage($"{RULENAME} dt.Rows.Count:{dt.Rows.Count}")
 
Dim columnNames As String = String.Join(" | ", From col In dt.Columns.Cast(Of DataColumn)() Select col.ColumnName)
api.LogMessage($"{RULENAME} H: {columnNames}")
 
' Print the rows (up to maxRows)
Dim maxRows As Integer = 2
Dim rowCount As Integer = Math.Min(maxRows, dt.Rows.Count) ' Limit to maxRows or table size
For i As Integer = 0 To rowCount - 1 ' Loop through the rows
'courtesy of Gemini and LINQ
 
Dim rowValues As String = ""
For Each col As DataColumn In dt.Columns
    Dim value = dt.Rows(i)(col.ColumnName)
 
    If value Is DBNull.Value Then  ' Correct DBNull check
        rowValues &= "" & " | "
    ElseIf TypeOf value Is Integer Then ' Correct Integer check
        rowValues &= CInt(value).ToString() & " | "
    ElseIf TypeOf value Is Decimal Then ' Correct Decimal check
        rowValues &= CDec(value).ToString() & " | "
    Else
        rowValues &= value.ToString() & " | " ' Other types
    End If
Next
 
api.LogMessage($"{RULENAME} {i}: {rowValues}")
Next