"Hey boss, look a this Cube View I made - it shows our EUR Fx rates against all currencies in our app. Cool, uh?"
"FX rates we loaded last month aren't cool. You know what's cool?"
"... You?"
"Real-time FX rates."
Alright boss, let's get rich competent. How can we quickly achieve something like that in Onestream? A custom FXRate routine is out of the question, a single cool CubeView is not worth the risk of messing up the whole app. We could build a UD8, but there's a metadata change-management process in place, someone would have to approve our new member, and it's Friday afternoon so nobody will answer their phone just to rubberstamp our fancy bit. Oh, if only there were a way to run some arbitrary code in your CV... oh wait, there is!
Custom GetDataCell functions are an often-overlooked feature that can be pretty powerful. It allows us to execute custom code to populate a CV cell with totally-arbitrary data. Their ergonomics are not the best (more on this later), but with a bit of care they can be a useful weapon in your arsenal of reporting tools.
In order to create a custom GetDataCell function, you have to add your code in the relevant block of a Business Rule of type Finance. Note that we don't need to attach it to a cube.
Select Case api.FunctionType
Case Is = FinanceFunctionType.DataCell
' ... your code hereLike in many other rules, we can have multiple functions within a single class, so the first thing we should do is to check which function was actually requested.
Select Case api.FunctionType
Case Is = FinanceFunctionType.DataCell
If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("CoolFXFetch") then
' ... do something
Else If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("LessCoolStuff") then
' ... do something else
End IfThe actual VB code to retrieve rates is long, so it's better isolated in its own private function. Let's assume that we've done that already, so we can just focus on Onestream stuff for now, and make it fetch the EUR rates to start with.
Select Case api.FunctionType
Case Is = FinanceFunctionType.DataCell
If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("CoolFXFetch") then
return me.GetRealTimeFX(api, globals, "EUR", api.Pov.Cons.Name)
' we pass Api and BRGlobals because we might need them in the bowels of our codeSo we have working code in this rule, but how do we trigger it from the Cube View? You will have to place the following call in your Member Filter property of a row or, like in this case, a column:
GetDataCell(""BR#[MyCoolGDCRules, CoolFXFetch]""):Name("EUR Live")
And that's it! Now, when the CV displays, the function will be called for every cell in the column.
That's nice already, but it's a bit wooden: what if we wanted to see rates at an arbitrary point in time? We would have to somehow pass a date as parameter. This can be done in a few different ways, particularly if your CV will live in a Dashboard; I'm going to focus on what is maybe the most awkward practice, just to show what's possible when you absolutely want to keep things barebone.
Technically speaking, there isn't a formal syntax to pass parameters to custom GDC functions. However, you can effectively hack them in your function name, and then unpackage them in the code.
Let's change our CV filter to this, to try and tell our code to get rates on the last day of last year:
GetDataCell("BR#[MyCoolGDCRules, CoolFXFetch~date=2022-12-31]"):Name("EUR Live")
Note how the function name now contains the obscure character ~, followed by the parameter declaration. We could have chosen another special character to act as separator, but ~ is a decent choice: we know it's never going to be used in a date, or in any other parameter we might be likely to ever pass.
In our code, we now have to change how we check for the function name. First we'll split the name from parameters, then compare the first element of the resulting List:
Select Case api.FunctionType
Case Is = FinanceFunctionType.DataCell
Dim callELements as List(Of String) = StringHelper.SplitString( _
args.DataCellArgs.FunctionName, "~", StageConstants.ParserDefaults.DefaultQuoteCharacter)
' callElements(0) now contains "CoolFXFetch"
' callElements(1) now contains "date=2022-12-31"
If callElements(0).XFEqualsIgnoreCase("CoolFXFetch") then
' ... do the work
End IfThe last bit is handling the passed parameters. We could have simply hacked in raw values, but that makes for poor, brittle coding. Instead, it's better to structure a list of key/value pairs, e.g. "param1=12,param2=34". This way, if we were to add other parameters in the future, our code would not break; and it self-documents "what is what" at a glance.
Handling such a structure in OneStream is super-easy:
If callElements(0).XFEqualsIgnoreCase("CoolFXFetch") Then
' parse the string passed in
Dim params As New NameValueFormatBuilder(callElements(1))
' now retrieve the parameter value for "date" from the dictionary;
' if "date" is not present, return 2020-01-01 instead
Dim paramDate As String = params.NameValuePairs.XFGetValue("date", "2020-01-01")
' do the work
Me.GetRealTimeFX(api, globals, "EUR", api.Pov.Cons.Name, paramDate)Et voilà! We now have a custom function to dynamically retrieve FX rates for a specific point in time. Time to dilute the stock to screw our business partner show it to the boss and get a victory lap.
PS: the code below shows the full rule. There are many APIs for FX rates, this is just a sample - it's not endorsed or recommended by OneStream, or related to OneStream in any way. The code is also very dumb, not using any sort of caching, error checking, or input sanitizing, so it's absolutely not recommended for any production usage.
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
' extra imports for this particular rule
Imports System.Net
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq
Namespace OneStream.BusinessRule.Finance.MyCoolGDCRules
Public Class MainClass
Private Property AccessKey As String = "YOUR API KEY HERE"
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
Try
Select Case api.FunctionType
Case Is = FinanceFunctionType.DataCell
'---------------------------------------------------------------------------------------
' Custom GetDataCell function to retrieve live rates.
'
' Parameter "date" : optional - specify a point in time to retrieve rates for
'
' Example: GetDataCell(""BR#[MyCoolGDCRules, CoolFXFetch~date=2023-01-31]"")
'---------------------------------------------------------------------------------------
Dim callELements As List(Of String) = StringHelper.SplitString( _
args.DataCellArgs.FunctionName, "~", StageConstants.ParserDefaults.DefaultQuoteCharacter)
' callElements(0) now contains "CoolFXFetch"
' callElements(1) now contains "date=2023-01-31"
If callElements(0).XFEqualsIgnoreCase("CoolFXFetch") Then
' do we have parameters or not?
If callELements.Count > 1 Then
' parse the param string passed in
Dim params As New NameValueFormatBuilder(callElements(1))
' now retrieve the parameter value for "date" from the dictionary;
' if "date" is somehow not present, return 2020-01-01 instead
Dim paramDate As String = params.NameValuePairs.XFGetValue("date", "2020-01-01")
' fetch rate at specified time
Return Me.GetRealTimeFX(api, globals, "EUR", api.Pov.Cons.Name, paramDate)
Else
' no date ? just fetch latest
Return Me.GetRealTimeFX(api, globals, "EUR", api.Pov.Cons.Name)
End If
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
#Region "FX Api Calls"
Private Function GetRealTimeFX(ByVal api As FinanceRulesApi, ByVal globals As BRGlobals, _
ByVal baseCurrency As String, _
ByVal destCurrency As String, _
Optional ByVal historicDate As String = Nothing _
) As Decimal
Dim query As String
Select Case historicDate
Case Is <> Nothing
' historic rate
query = $"v1/{historicDate}?&symbols={destCurrency}"
Case Else
' latest
query = $"v1/latest?&symbols={destCurrency}"
End Select
Dim result As JObject = Me.FetchXRateEndpoint(api, globals, query)
If(Boolean.Parse(result("success").toString) = True) Then
Return Decimal.Parse(result("rates")(destCurrency).ToString)
Else
Throw New Exception(result.toString)
End If
End Function
Private Function FetchXRateEndpoint(ByVal api As FinanceRulesApi, ByVal globals As BRGlobals, _
ByVal queryString As String, Optional ByVal postBody As String = "" _
) As JObject
' example site, not endorsed by or related to OneStream.
Dim url As String = $"http://api.exchangeratesapi.io/{queryString}&access_key={Me.AccessKey}"
api.LogMessage(url)
Dim req As HttpWebRequest = HttpWebRequest.Create(url)
req.Method = "GET"
req.Accept = "*/*"
req.UserAgent = "onestream_blog_GDC/0.1"
req.Timeout = 5 * 60 * 1000 ' milliseconds
req.AllowAutoRedirect = True
req.ContentType = "application/x-www-form-urlencoded"
Try
Dim response As HttpWebResponse = CType(req.GetResponse(), HttpWebResponse)
Dim responsedata As Stream = response.GetResponseStream
Dim responsereader As StreamReader = New StreamReader(responsedata)
Dim responseBody = responsereader.ReadToEnd()
If responseBody.Length > 0 Then
Return JObject.Parse(responseBody)
Else
api.LogMessage($"{response.StatusCode} - {response.StatusDescription}")
Return New JObject()
End If
Catch ex As WebException
Dim content As String = ""
If ex.Response IsNot Nothing Then
If ex.Response.ContentLength <> 0 Then
Using stream = ex.Response.GetResponseStream()
Using reader = New StreamReader(stream)
content = reader.ReadToEnd()
End Using
End Using
End If
api.LogMessage(ex.Status.toString)
For Each k In ex.Data.Keys
api.LogMessage(k.toString)
api.LogMessage(ex.Data(k))
Next
End If
Return Nothing
End Try
End Function
#End Region
End Class
End Namespace
4 Comments
