If you want to unlock the full potential of the OneStream platform, you want to learn a bit of VisualBasic.NET. Don't be scared! It's much easier than it looks. We'll now cover the basics (lol) of the language, in a short series of posts.
First, a general suggestion: as soon as you open some formula or rule, don't be scared or put off by what looks like a wall of gobbledygook. Programming is all about breaking down complexity into smaller and smaller chunks that are individually simple. Start from the top, read one line at a time, and try to make sense of each line; as you move towards the bottom, you'll get an understanding of what the code is doing. With a bit of experience, you'll quickly be able to "speed-read" your way through the longest rules; it's just a matter of time. Remember that Rome wasn't built in a day! This is a very important approach because, in practice, you will be reading code much more often than you'll be writing it - this is a fact of life for programmers of all stripes, regardless of language.
With that, I've already revealed to you how this language works: a computer will read one line of code at a time, and execute any command on that line, before deciding what to do with the next. This is why it's called an *imperative* language: like an emperor, you issue command after command, and the computer executes them in sequence. It's like you were Santa Claus with a personal elf, who can be incredibly quick and industrious - but also fairly obtuse. So we will have to be very precise, if we want to make toys that actually work.
There is one exception to the line-by-line rule: any lines starting with a single quote character ' will be ignored by our "elf"; you can use them to describe what the code is supposed to do, which is why we call them comments.
Now, OneStream is all about calculating numbers, so we'll want to keep those numbers around, somewhere in memory. We do that with variables, little "bags" in which we can place one thing at a time; and to distinguish them, each bag will have a name. This is done in VB with the Dim keyword, which means "create a variable called ...".
' create a variable called salesAmount
Dim salesAmountWe will typically want to also label our bag to specify which type of data it can contain. Although not always required, it's best practice and will avoid trouble later on. This is done by adding the As keyword, followed by the type of data we will accept.
' create a variable called salesAmount, that can contain a number
Dim salesAmount as IntegerAt this point our bag is empty. You can enter (assign) data into a variable using the = special character (the assignment operator in our "elfspeak"). Here are a few examples with different types of data:
' create a variable called salesAmount, that can contain a number,
' and assign the number 100 to it
Dim salesAmount as Integer = 100
' create a variable called preciseSalesAmount, that can contain a number with decimals,
' and assign the value 100.34 to it
Dim preciseSalesAmount as Decimal = 100.34
' create a variable called dayOfWeek, that can contain text,
' and assign "Wednesday" to it
Dim dayOfWeek as String = "Wednesday"
' create a variable called "areWeDone", that can contain a boolean
' (true/false), and assign False to it
Dim areWeDone as Boolean = FalseNote how our String (i.e. text) is delimited by double-quotes ". These signal to VB.NET where data-text begins and ends, to distinguish it from regular VB keywords.
We are not limited to specifying individual bits of data to the right of =, we can have complex operations:
' create a variable called salesAmount, that can contain a number,
' and assign to it the result of 123 * 72
Dim salesAmount as Integer = 123 * 72
' create a variable called dayOfWeek, that can contain text,
' and assign to it the words "Thurs" and "day" joined up
Dim dayOfWeek as String = "Thurs" & "day"These operations can include looking up values that VB or OneStream make available to us.
' create a variable called salesAmount, that can contain a number,
' and assign to it the amount we find in a cell
Dim preciseSalesAmount as Decimal = api.Data.GetDataCell("A#SalesAmount").cellAmountWow wow wow, hold on, what is all that "api.Data..." rigamarole? That is us working with objects. Objects are elements that the program creates and manages in memory as part of its execution, which is why VB.NET is called an object-oriented programming language.
Some objects are always available, some will be available only in certain situations, and others we will have to create ourselves. For example, the api object is available in Finance rules, but not in Extenders; and if we want to create a List of things, we will have to create it ourselves (more on this later). Even basic data like numbers or text is really just a specific type of object.
Objects have properties, which are little variables attached to each object. We can look them up by appending . (dot) and then the name of the property. Some examples:
' si is a SessionInfo object, representing our login session.
' create a variable called applicationName, that can contain text,
' and assign to it the content of the "AppName" property from object "si"
Dim applicationName as String = si.AppName
' create a variable called appOpen, that can contain a boolean (true/false),
' and assign to it the content of the "isAppOpen" property from object "si"
Dim appOpen as Boolean = si.isAppOpenYou can chain dot-operations, so if a Property contain an object we can look up its properties right away.
' create a variable called nameLength, that can contain a number,
' and assign to it the number of characters that the AppName property of object SI contains
Dim nameLength as Integer = si.AppName.LengthObjects also have methods, which are sets of operations that can be kicked off in one go. Methods can be functions, if they output some sort of result back to us after they're done; or they can be subs ("subroutines") if they are just meant to run and that's it. There is no way to peek inside a sub or function to see what they do; you will have to rely on documentation to find out.
' DecimalToText is a function: it will transform a decimal number
' into its textual representation, and give it back (return it) to us,
' so that we can store it into a variable
Dim textAmount As String = api.Data.DecimalToText(100.34)
' Calculate is a Sub: it will just do something.
api.Data.Calculate("A#MyAccount = A#MyOtherAccount")Methods that are functions can also be dot-chained. This is what we did in our previous snippet:
Dim preciseSalesAmount as Decimal = api.Data.GetDataCell("A#SalesAmount").cellAmountWhat happens here is :
- we take the api object, and look up its property Data
- Data is an object, so we look up its GetDataCell method and execute it
- GetDataCell returns a DataCell, which is an object, so we look up its cellAmount property
- finally, we take the value contained in cellAmount and place it in our preciseSalesAmount variable
Chaining functions like this can be slightly dangerous (what happens if GetDataCell gives us back an invalid or empty cell...?), but very useful in most cases.
As you've seen above, functions and subs don't just have names (like properties), but can be followed by parentheses and some other stuff. That stuff are parameters, called arguments, so that the code can react differently to different values you pass. For example, .GetDataCell("A#SalesAmount") will give us a cell belonging to account SalesAmount, and .GetDataCell("A#Expenses") will give us a cell belonging to the account Expenses instead.
Retrieving values into memory is good, but we want some action too! In most cases, we will want to do different things in different situations. For this we have so-called control statements. In VB.NET they are fairly easy to use; we will start with the classic If:
' if it's the first of the month...
If DateTime.Now.Day = 1 then
'... write something to the Error Log
BRApi.ErrorLog.LogMessage(si, "Welcome to a new month!")
End if
After If you specify the condition to check, and follow it with Then. You then (duh) move to the next line, and specify what should happend if the condition evaluates to True. You then have to add a line containing End If. Only when the condition is true, the computer will execute all the lines between "if" and "end if".
You can also specify an alternative set of operations for when the condition evaluates to False:
' if it's the first of the month...
If DateTime.Now.Day = 1 then
'... write something to the Error Log
BRApi.ErrorLog.LogMessage(si, "Welcome to a new month!")
' otherwise...
else
' ... write something different
BRApi.ErrorLog.LogMessage(si, "Keep truckin' until payday")
End ifNote how we added some tabs (or whitespace) at the beginning of lines inside our IF blocks. This is called indentation, and it's a great way to visually signal that a particular set of instructions are all executed after a specific control switch (in this case If-Then/Else). VB.NET does not force you to indent your code, but it's a best-practice that will make your code significantly easier to read - including when you will have to read it yourself, 6 months later, to remember what it's supposed to do.
To recap:
- in VB.NET we create Variables to work with data
- Variables can state which type of data they will contain
- Variables can contain the output of operations
- Data is organized in Objects
- Objects can have Properties, i.e. variables attached to them
- Objects can have Methods, i.e. sets of instructions that can be triggered
- Methods can be Functions, which return output data
- Methods can be Subs, which just execute
- Methods can accept parameters to perform different operations depending on some value
If/Then/Else/End If allow us to perform different actions depending on some value
This should give you a good start in the wonderful world of VB.NET. We will continue with more goodness in the next installment. Keep tuned!
PS: some references to dig a bit deeper:
