Forum Discussion

dinishkrishna's avatar
dinishkrishna
New Contributor II
2 days ago

Fetching text property by time

 

Hi,  I'm trying to display a time-variant text property of the UD1 dimension member in a report. The challenge is that the text value can exist in any period of the member, so I currently loop through the forecast periods (spanning two years) to retrieve the values. While the code works, its performance is not good.

Is there a way to fetch or look up the text properties by time (possibly from a specific table) without iterating through all periods? Any command or optimized approach to achieve this efficiently would be highly appreciated.

Dim Date As String = $"{year}M{month}"   
Dim timeID As Integer = timedimhelper.getidfromname("Date")
Dim myVaryByTimeId As Integer = BRApi.Finance.Members.GetMemberId(si,DimType.Time.Id, Date)
Dim mbrText As String = BRApi.Finance.UD.Text(si, 9, mbrId, 1, -1, myVaryByTimeId)

Application Properties
Cubes
Metadata
Planning
  • SimonHesford's avatar
    SimonHesford
    Contributor II
    11 hours ago

    Hi,

    When you say performance is not good, what do you mean can you quantify? Also can you paste the code snippet you are using and is proving to be inefficient?

     

    • dinishkrishna's avatar
      dinishkrishna
      New Contributor II
      9 hours ago

      It takes around 10 minutes to loop through 2 years. I'm inserting the values into a table and displaying the results of that table in a dashboard. Here is my Dashboard Extender code:

          
      Dim strStartPeriod As String = "2025M1"
Dim strEndPeriod As String = "2026M12"
Dim strStartPeriodId As Integer = BRApi.Finance.Time.GetIdFromName(si,strStartPeriod)
Dim strEndPeriodId As Integer = BRApi.Finance.Time.GetIdFromName(si,strEndPeriod)
Dim startYear As Integer = Integer.Parse(strStartPeriod.Substring(0, 4))
Dim startMonth As Integer = Integer.Parse(strStartPeriod.Substring(5))
Dim endYear As Integer = Integer.Parse(strEndPeriod.Substring(0, 4))
Dim endMonth As Integer = Integer.Parse(strEndPeriod.Substring(5))

For year As Integer = startYear To endYear
    Dim maxMonth As Integer = If(year = endYear, endMonth, 12) ' Stop at endMonth in the final year
    Dim minMonth As Integer = If(year = startYear, startMonth, 1) ' Start at startMonth in the initial year
    
For month As Integer = minMonth To maxMonth
Dim priorMonth As Integer = 0
Dim priorYear As Integer = 0

If month = 1 Then 
priorMonth = 12
priorYear = Year - 1
Else
priorMonth = month -1
priorYear = year
End If

        Dim Date As String = $"{year}M{month}"   
Dim timeID As Integer = timedimhelper.getidfromname("Date")
Dim myVaryByTimeId As Integer = BRApi.Finance.Members.GetMemberId(si,DimType.Time.Id, Date)
Dim mbrText As String = BRApi.Finance.UD.Text(si, 9, mbrId, 2, -1, myVaryByTimeId)

If Not String.IsNullOrWhiteSpace(mbrText) Then

Dim sqlQuery As New Text.StringBuilder
sqlQuery.AppendLine("INSERT INTO MemberTextbyDate_Table ")
sqlQuery.AppendLine("(MemberId , MemberName, MemberDescription, Text, Date) ")
sqlQuery.AppendLine($" VALUES ('{mbrId}', '{Mbr}', '{desc}', {mbrText}','{Date}'); ")
sqlUpdate.AppendLine(sqlQuery.ToString)

End If

Next
Next