JackLacava
Honored Contributor

"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."

JackLacava_0-1695634608063.gif

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 here

Like 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 If

The 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 code

So 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.

JackLacava_1-1695473184338.png

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")

JackLacava_0-1695473158180.png

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 If

The 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.

JackLacava_1-1695634903474.gif

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
wnindl
Contributor

This is not only a cool cube view function but Jack also gives us a perfect example of connecting and using an API to get FX Rate.  Coincidently, i needed an example to fix my own error with the API and it is right here.
Owe you double, Jack!!! Thanks for sharing your knowledge.

MarkBird
Contributor II

Hey Jack

Is it possible to pass values from a cube view into a custom calculate function?

I've tried a few variances of the code below but nothing I've come up with has worked.

GetDataCell(BR#[BRName=MyDynamicCalcs, FunctionName=CalculateCustomVariance, Col1=CVC(Col2), Col2=CVC(Col1)]):Name(Custom Variance)

At the moment I am just trying to return a variance between the two values. But if I manage to prove the concept then my calculation will be a bit more advanced, hence why I'm not using the standard variance formulas.

Regards,

Mark

JackLacava
Honored Contributor

@MarkBird I expect CVC/CVR will only be recognised in a regular GetDataCell script - where you have them now is effectively in the BR declaration so they probably won't be resolved, I guess the function will receive the string "CVC(Col2)" instead.

I think Substitution Variables and Parameters should work though, so you can try to be clever with them.

MarkBird
Contributor II

Thanks Jack.

Little less than ideal, but I ended up getting around it by putting the same filter as Col1 into the formula:

GetDataCell(BR#[BRName=MyDynamicCalcs, FunctionName=CalculateCustomVariance, Col1=[V#YTD:S#Actual]...

 and then calling api.Data.GetDataCell() in the business rule:

Dim Col1 As String = args.DataCellArgs.NameValuePairs.XFGetValue("Col1", "")
Dim Col1Value As Decimal = api.Data.GetDataCell(Col1).CellAmount