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

  • Nikpowar97's avatar
    Nikpowar97
    Contributor
    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
    25 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