Questions Building My First Excel Formula

Hi Rowlf,

Writing a Velixo Reports formula is the same as writing an Excel formula.

If you go to the Insert Function button...

... and scroll down to the last Category called Velixo Reports Excel Add-In ...

... you will see the Velixo Reports functions that are available: 

Before you go any further, I suggest running the Trial Balance Summary report in Acumatica:

Note in the screenshot above that it was run for the 11-2017 period and the HQ branch.

So, let's try to reproduce the 21,458,384.14 number associated with the 10200 GL Account.

Back to the Excel formula. Since the Trial Balance is an Ending Balance, I'm going to use the ACCOUNTENDINGBALANCE formula and click OK

That brings us to the Function Arguments window:

Note that there are the following arguments available to this function:

Connection: This is the Name field that you filled out in the Connection Manager screen when you setup a connection to your Acumatica instance (click here).

Ledger: This corresponds to the Ledger ID field of the Ledgers screen in Acumatica. So you could report on your ACTUAL ledger, your BUDGET ledger, your STAT ledger, etc.

AccountClass: This corresponds to the Account Class field of the Chart Of Accounts screen in Acumatica. Velixo Reports will go out and find all GL Accounts with this Account Class and bring them together.

Account: This corresponds to the Account field of the Chart Of Accounts screen in Acumatica, but with a twist. This can take the same arguments as ARM (Analytical Report Manager). So, using the Accounts from the Trial Balance screenshot above as examples, I could put in 10200 to just grab one account. Or I could put in 10200:10800 (note the colon) to grab 10200, 10800, and every Account in between. Or I could put in 10200,10800 (note the comma) to grab 10200 and 10800, but no Accounts in between. Or I could put in 12??? to grab any Account beginning with 12. Or I could put in ???12 to grab any Account ending with 12. Or I could put in ??12? to grab any Account with 12 in the middle. Or I could put in something like 10200:10800,11010,12??? to grab a range of Accounts between 10200 and 10800, plus 11010 additionally, plus any account beginning with 12. So it's pretty flexible.

Subaccount: This corresponds to the Subaccount field of the Subaccounts screen in Acumatica.

Branch: This corresponds to the Branch ID field of the Branches screen in Acumatica.

AsOfPeriod: This corresponds to the Financial Period ID field of the Financial Periods screen in Acumatica. Note that you have to enter in the dash as well, so you would enter in 11-2017 if you wanted to match the period of the Trial Balance that we ran earlier.

In order to try and match the Trial Balance Summary report that I ran earlier, I'm going to put in the following function arguments.

Note that sometimes you need to put double quotes around a function argument, depending on the value that you put in. So, rather than having to remember when to use the double quotes, I just always put them in.

Note that you can see the expected output of the function (21458384.14) before you even click OK.

Once you click OK, it puts the function with the arguments into the cell that you were in. In my case that's cell A1.

I can format the cell with some commas and now I have a nice looking value that, if you scroll back up in this post, matches the value from the Trial Balance Summary report. Also, you can see the full function with arguments that was generated by looking in the Excel Formula Bar highlighted in the screenshot below.

Now, next time I want to write a formula, I would probably just start typing in the Formula Bar and the IntelliSense will find the function name for me. It's just faster that way.

Wow, this really is just like working with regular Excel functions. What a pleasant experience!