Ask the Expert | Year-End Special | Dec 2024

Transcript

Evie: All right, let's get going. Hi everyone, I'm Evie, the Customer Success Manager here at Solution 7, and welcome to our final Ask the Expert session of 2024. This year has gone crazy fast; I can't believe it is less than a week until Christmas.

Today, Simon is going to give us an update on the migration plan from Legacy data source to NetSuite 2, with a sneak peek at our new and shiny migration tool. After that, we'll move on to the Q&A. So if you have any questions during the webinar, please post your questions in the Zoom Q&A box at the bottom of your screen.

All right, Simon, what have you got for us?

Simon: Oh, hello everyone. Welcome to the last session of the year. We labeled this as a year-end special. I can't work out whether we're going to be talking about year-end functionality or the end of the year, but we'll see what questions come up in the chat.

So yeah, hello, I'm Simon, founder of Solution 7. As Evie said, I wanted to go through the NetSuite 2 plan. I'm going to go through this in basically every webinar we do, up until the completion of the process.

Just as a reminder, NetSuite are deprecating the Legacy data source, and we are being forced, along with you guys, over to a data source that they've called NetSuite 2. This is something that we have no control over. We've spoken to NetSuite about it in the past; it's not something that they're going to back down on.

So this is a journey that we're all on together, and a journey that we all have to complete together.

We've been working on the NetSuite 2 migration now for probably the best part of two to three years. I'd say we've probably got four to five man-years’ worth of effort invested in this process, so we are a long way along the journey.

To remind you of the plans we have in place to help get our customers over the line, we have an engineering plan this is the code within the software to support the NetSuite 2 data source   and that's going to be in our 412 release.

We have a communication plan, just so we can let customers know what's happening, when it's your turn, and what to expect.

Finally, there's the migration plan itself, which is helping customers to do that migration. Evie, I'm going to ask you a question in a second if you can let me know, when I come to the migration plan, how many customers we've migrated, that'd be super helpful.

To talk about the engineering plan: these are the steps that we've completed so far and the steps that are in progress. As I've said before, we are feature complete. We have what we think is a pretty good user experience. We've got mappings between the Legacy and NetSuite 2. We've got backwards-compatible functions, and we've got migration code for lists.

That is currently limited to migrating lists on a single sheet, which, as we realized during testing, if you're a user with lots and lots of sheets in a single workbook, you'll have to actually go into each sheet one at a time to migrate the lists.

In development now is the ability to do that across an entire workbook rather than on a single-sheet basis. That’s now in development to finish off that functionality.

We are also adding more mappings between the Legacy data source and the NetSuite 2 data source as we are starting to come across mappings that have been missed before, or any sort of enhancements that we need to throw in. I'll go through that as part of my demo at the end of this session.

The communication plan: the primary source for communication at this stage is these webinars.

Next, we are notifying customers that there is a need to migrate to NetSuite 2. Then we are updating customers on our side of the story through the webinars. As we get closer to being able to give more concrete dates in terms of who, when, what, and why, we'll actually start emailing the customer base.

At the moment, we are keeping our communication just to these Ask the Expert webinars. That’s because, as I said before, we need to identify the different groups and the process that those groups need to go through in terms of upgrading.

We then need to physically communicate out and deal with all the back-and-forth that goes with that. As you can imagine, with several customers   we think we've got about 500 customers that need to go through this migration process   all the communication back-and-forth is not a small task.

So at the moment, we're just keeping the communication as outbound communication via the Ask the Expert webinars.

For the customer migration plan, as I said: NetSuite 2 customers, NetSuite 2 only. That was completed in June.

At the moment, I am reviewing all of the customizations that we've created for our customers, and I'm looking at trying to automate that process. That automation is mostly complete.

We can now take any customization   you may remember that you jumped on a call with either Evie or Maria   and we would have actually plugged your customization into a system that then pushed out the new customization for you.

We've now taken those datasets and can run them through something that will output a NetSuite 2 version and also output all the mappings as well. That was completed probably last week from a coding perspective. We're now going into testing.

As I say, we are reviewing all the customizations, running them through this migration tool, and seeing what it pushes out. Hopefully, by the next webinar, we will be well into that testing process and will have some more feedback on what that looks like. Hopefully, we can start the process of actually communicating out to customers the new shape of the customizations and what that process will look like.

Evie, just out of interest, did you get a chance to see how many customers are now migrated from NetSuite 1 to NetSuite 2?

Evie: Yes, we have the grand total of 94.

Simon: That's awesome, thank you. And just out of interest, Evie, is that 94 customers on NetSuite 2, or is that 94 customers that have migrated to NetSuite 2?

Evie: That's 94 that are on NetSuite 2. I haven't got the numbers for who we've migrated over.

Simon: Okay, well, let's pick a number. Let's say 10% of those have been migrated. So, you know, we're into double digits in terms of migrating customers, and we have a good chunk of customers that are using the NetSuite 2 data source on a day-to-day basis.

The great news for December is that the 412 download now includes the migration tool. It does come with some caveats, which I will go through with you now.

We've made it available for download as of, I think, last Wednesday. We always tend to do a launch before we announce that it's available to download. If you downloaded last Wednesday's build   or, I think, yesterday's build   you now have that migration tool in place.

However, it is clearly marked as a beta migration tool because we are still testing it internally, and we are testing it with customers on support calls. We hand-select some customers to actually take them through that migration process, typically customers that don't have customizations.

For those that do have customizations, the customization migration tool is not fully available yet. We've had one or two customers with customizations, and we've manually handcrafted a NetSuite 2 customization from the NetSuite 1 version to prove that the migration is going to work.

So, we are slowly migrating customers using the tool. You can now download it if you choose to migrate from NetSuite 1 to NetSuite 2. You'll need to download the latest version, switch your config file over to NetSuite 2, and I'll go through that as part of this session.

As I said, it is labeled beta. Download it at your own risk   but I don't think it's that risky. Just be aware that we are going into the holiday season. If you download the customization migration tool, migrate all your workbooks, and then hit a problem between Christmas and New Year, support may be thinner than usual.

If you're not comfortable with migrating now, you're probably better off waiting until the new year. If you do want to migrate now, as long as you know how to go forwards and backwards and save copies of your workbook, you can safely try it.

If you successfully migrate, please drop an email through to support to let us know, and we will add you to our list of customers that have successfully moved from NetSuite 1 to NetSuite 2.

Let's jump in and have a look. Here I am in Solution 7. I've got one of our demo workbooks.

Before I show you the migration, let me show you how to switch between NetSuite 1 (Legacy) and NetSuite 2. That's done through the config file.

If I go into "Change File" for me, you'll see I've got three config files available. The reason I've got three is I'm testing between Legacy and NetSuite 2. The third one helps me build the migration tool.

If you've got a single connection file connecting to NetSuite 1, it will probably look something like this. The simplest way to switch over to NetSuite 2 is to go into "Change File." Personally, I would take a copy; I wouldn't actually change what you've got. I would take a copy of your config file, paste it back into the same folder, give it a new name (not Legacy   call it NetSuite 2), then open that file, and change your config settings.

If you've got 412 installed, when you drop down the data provider, you'll see test connections. You'll see something like "SuiteAnalytics Connect NetSuite 2." That's the setting you want to choose. Check your username, password, account ID, and role ID. Hit OK, then restart. Then you're in NetSuite 2.

Switching between NetSuite 1 and NetSuite 2 is now as simple as changing config files. You just choose your Legacy or NetSuite 2 config file and flick between the two really easily.

Let me jump in as NetSuite 2 and let's connect to our demo NetSuite 2 instance now to show you what the migration process looks like.

I'm going to upgrade one of my very simple demo workbooks. This demo workbook has three lists in it that need to be upgraded. We've got a list of subsidiaries, I think here, we've got a list of locations, and here we've got our chart of accounts. Now all three of those need to be migrated.

You'll see if you try and right-click and refresh, you'll get an error message: "This list is disabled." Open List and Table Manager, you'll see if I hit the refresh button   oh no, the refresh button is actually only going to refresh the formulas. Let's just try that. Oh no, there we go.

"There are lists or tables in this workbook which cannot be refreshed," see the List and Table Manager. So when you hit the refresh button, you get a warning; when you right-click, you get a warning.

The answer is to go into the List and Table Manager. You'll see your lists will come up in red. You'll see you get a warning: "One or more lists are not compatible with NetSuite 2 and have been disabled."

This is where our migration functionality sits. Hit the Migrate button. This is where we say: this is clearly in Beta/Preview.

So, if you're not comfortable using software that's labeled Beta, you can use 412; it's just this functionality under the Migrate button that is designated Beta. The rest of the code is just core Solution 7 code.

If you're not comfortable using a Beta or Preview at this stage, please hold back from actually running this process.

All you have to do is hit "Yes." We whiz down the workbook, upgrading the lists. The first time, it takes a bit longer because we have to load some details from NetSuite   I think we load your subsidiary information from NetSuite, which is why there was a slight pause.

There, we can see that the migration has completed.

As I said in the introduction, we are looking to improve this because at the moment, it will only work for the currently selected worksheet. So if you have a workbook with 15 worksheets in it, you've got to select the worksheet, migrate, select the worksheet, migrate, and so on.

The next piece of functionality the devs are working on is the ability to filter the lists by every sheet in the workbook, which means you'd get a full list down. So if you've got, say, 100 lists in your workbook, you're not having to go in one at a time. You can literally select the whole lot, hit the Migrate button, and then the whole lot will get updated for you in one go.

Again, that's going to save you a lot of time. That is in progress now; I'm hoping that's going to be finished mid-January. Let's see how we get on after the holidays.

Now that I've run that through, I have an upgraded workbook. First thing I should do is save a copy. I'm actually not going to save this workbook, but I should save a copy so that I've got the version I created before I migrated. I've got the original and I've got the post-migration copy. You can never have enough backups.

All I need to do to check it's working is hit the refresh button. Hit "OK." I can see that the formulas are coming through. I know I've connected to the NetSuite 2 data source.

If I right-click on one of my lists, you can now see that I get the proper right-click menu come through as opposed to that warning saying that my list has been disabled.

Have I missed anything, Evie?

Evie: No, that looks great. I did the same process with someone today and it was like magic. They had about 50 lists in their sheet. I just ended them all, and it was great they were very happy.

Simon: I think that's the demo done. So, should we dive into the Q&A?

Evie: Yes, let's do it. I have the first question: Is the session being recorded?

Simon: Oh, of course it is. Yeah, this session is being recorded, and a copy of the recording will be sent out following the webinar. I don't know what the plan is to get that email out. What day are we today? Thursday? We will try to do it before the Christmas break, but don't hold me to it.

Evie: I think we can live with that. All right, so had a few questions about NetSuite 2. I think you kind of answered all of them, but I'm going to ask the first one anyway.

Does NetSuite 2 impact me?

Simon: NetSuite 2 impacts every Solution 7/NetSuite customer, and there are no exceptions unless you can raise a ticket with NetSuite and change their entire NetSuite 2 strategy. There are no exceptions.

Unfortunately, this wasn't what we wanted to hear, and it's not what a lot of our customers wanted to hear. Are you impacted? Yes, you are unless you're already on NetSuite 2. Then I guess no, you're not, because this is a migration to NetSuite 2. So sadly, yes, you are.

At this stage, I would also say: we do get a lot of emails through to support asking about NetSuite 2. We do have a plan in place, and we are rolling that plan out. It's the communication plan that’s really important. We're just not there yet with communicating the detail out.

If you can, please hold any questions to this webinar rather than emailing us about NetSuite 2. As soon as we have anything more concrete, we will let you know.

Don't worry, we've got until I think April 2026 to get all of our customers migrated. So we have February, March, April....16 months to get people upgraded, sorry, migrated, from NetSuite 1 to NetSuite 2.

We feel we've got a lot of contingency. We'd like to think we will start migrating customers on mass probably in the March/April time frame, moving out of just doing one, two, five, or ten customers to actually 50 or 60 at a time, and actually having sessions like this where we help people migrate.

I'm just trying to manage the support inbox between now and the next webinar, so I do appreciate that.

Evie: We have a couple of people who have asked about documentation for migrating.

Will that be coming?

Simon: There is one button. There really isn’t any documentation. What you've seen is a one-off exercise that we need to go through. There will be an email   it will tell you about the button. We've got documentation on how to create a config file.

What I’ve just shown in my demo is the process. Whether we put together a document that explains the same thing, we haven't thought that far ahead yet.

Evie: For people who have customizations, it's not as easy at the moment anyway.

Simon: It’s not that simple. If you are working with custom fields and custom segments, there is no documentation that we can produce for you at this stage that would help. We are still trying to get your customizations migrated.

By the way, that sounds like it's a big deal, but it won't be. The intention is it won't be a big deal. We will send you a new customization file/adapter file, similar to when you first went live. It will contain all the changes, all the mappings, and all you have to do is reference that file, load it into your NetSuite 2, hit the Migrate button, hit Save, do a bit of testing finished.

Please stick to these webinars and stay tuned to these Ask the Expert sessions; we will keep you as up-to-date as possible.

Evie: Another question about NetSuite 2 and custom fields: Cam is asking: "We're looking to add custom fields to Solution 7. Should we wait until the migration to NetSuite 2?"

Simon: No. Drop an email through to Evie, probably now. If you can wait until the new year, because it's unlikely we’ll be doing customizations over the holiday period, that’s fine. But no   drop an email to support@solution7.co.uk (on screen now), and either Evie, Maria, or someone on the team will pick up that request and have those customizations ready for you.

If they’re NetSuite 2 customizations from day one, all of this migration talk becomes redundant.

Evie: How do we know if customers are on NetSuite 2? Not us as the customer how does the customer know?

Simon: Very simple. Go into your Configure button under the Data Provider. It will either say "SuiteAnalytics Connect" or "SuiteAnalytics Connect NetSuite 2."

I’m running a Dev build, so the data providers I see are probably slightly different from the production version.

Evie, are you running a Dev build or a customer build?

Evie: I'm in a Dev build.

Simon: Okay, so you'll see a long list like this we will send out a screenshot following the webinar with a big arrow showing what NetSuite 2 looks like in terms of the Data Provider.

Evie: Questions on adding custom fields: yes, we can build custom fields to your Solution 7. Just email support, and we can get a ticket in to get that sorted.

Simon: Part of the benefits of doing the migration work is we are putting code together that will allow customers to support their own custom segments.

One of the goals of Solution 7 has always been to not show people too much information because too much information can be overwhelming almost as bad as showing too little information.

The structure of your NetSuite instance and what's available to report from it is the reason. At the moment, we add metadata into Solution 7, which exposes the functionality from your NetSuite instance. You don’t see everything NetSuite has hundreds of data points. We want to keep Solution 7 focused on what is relevant to our users.

The longer-term plan is to allow a user to go into NetSuite 2, tick the options they want, hit the Go button, and generate their customization, rather than raising a support ticket. That’s the real benefit of this migration process: ultimately, we’re looking to make this self-service.

Evie: Moving away from NetSuite 2 now, questions about general use of the software:

Braden asks: "What are the options for ensuring that my Excel file doesn’t end up with an error, especially when I’m sending a live spreadsheet to someone who does not have Solution 7?"

Simon: We’ve developed locking functionality for this.

The problem is when you have a live formula. For example, a live Solution 7 formula: NSG Bal (Quick Basic Training Session, NS Next Suite General Ledger Account Balance). That’s why the function is called NSGL Bal.

It’s a function embedded into any installed copy of Solution 7, physically into Excel.

If the user doesn’t have that function installed Google Sheets, Excel Online, or another user   Excel will just generate a #NAME or #VALUE error depending on the software.

To solve this, we developed locking and dropping. Think of it as enhanced copy-paste-special-values. Instead of throwing formulas away, we re-encode them in an IF statement, tricking the Excel engine into not recalculating the false side of the IF.

We take the current value of the cell, hard-code that into the IF statement, and retain the original formula.

Where we go better than copy-paste-special-values is that you can lock a formula and unlock it later, returning it back to the original formula that you entered.

So, in addition to being able to then being able to lock a formula we do one other thing that copy-paste-special-values doesn't do, which is we don't touch the non-Solution 7 formulas in your workbook.

The benefit of that being that although these are essentially frozen formulas, should you, say, be going through a forecasting cycle where you want to load the data into Excel Online or Google Sheets, you still have the ability to edit a formula. And although you've over-keyed our formula, we still retain the dynamic of the workbook itself.

So any SUM functions, any IF functions, any SUMIF functions anything that doesn't contain a Solution 7 formula that will remain. That will keep its original formula, which means that as you edit the workbook, your workbook remains live, live to the extent that it can remain live, which again is perfect for budgeting.

Because you may use a series of Solution 7 formulas to actually generate the workbook, send it out locked, and then have your users over-key the formulas. And if you then need to change anything, you can pull it back, unlock the formulas, copy them around the workbook, relock it, and send it out again.

So it allows for a much more interactive use of the workbook in that specific scenario.

So yeah, really, that's the best way to stop the formulas from going to an error.

What you do lose, though, is the ability to refresh the workbook. So you can't send a workbook out to a Google Sheets user and expect it to refresh from NetSuite, because there is no Google Sheets version of Solution 7 just yet.

So it stops it from going to an error, but it also prevents you from being able to recalculate.

Evie: Thanks, Simon. Okay, so our next question is from Emily at Ideal Image H. She has got a pivot table that won't refresh when clicking “Refresh Workbook” or “Refresh Current Sheet.” How can we fix this?

Simon: Okay, so Evie and I actually discussed this question earlier today, and this is something that we're a little bit confused about, so we may need to have a deeper conversation. But it's also a good opportunity to talk about a couple of concepts within Solution 7.

Let's insert a pivot table. As you know, Solution 7 can pull transactional data, insert it into a pivot table, and then use that against the standard pivot table functionality to slice and dice your data.

I'm inserting something called a flattened pivot. For anyone who doesn't know, a flattened pivot is essentially a pivot table with all of the pivoting functionality switched off.

Let me just put some parameters in here: 2023, December 2023. I'm doing this for last year. Click OK.

Here, we're pulling in all the sales codes for 2023. This is going into a transaction pivot, so essentially it's just like a listing. There we go, it's just a listing.

You can edit a pivot table through the Edit button. You can customize a pivot table through the Choose Column screen. So if you want to move things around for example, double-click and maybe group, apply some grouping you can. That's just pivot tables in general.

As I said, this is a flattened pivot, so it's much more of a transaction listing.

We also have a regular pivot integration, which is here, which is just a regular pivot table where you get the column and the row definition appear at the side. With the flattened pivot table here, you can see I've hidden the detail, so you get a little plus indicator, little minus indicator if you want to create a pseudo drill-down.

Now, I've kind of skirted around the question, but to answer the question of how can I make it refresh: that's actually down to something in the Options screen. You can see here that you have a choice of automatic and manual.

What that means is if it's set to automatic, it will automatically refresh when you hit the Refresh button. So if I were to refresh at the sheet level, because that's set to automatic, you can see here that it's updating my pivot table.

If it's not updating, it'll be because that option is set to manual.

Now, I did have a conversation with our developers, and I did ask them if at any time in the past the functionality was set to manual by default and not automatic. That may have been the case, but actually we don't think we ever went down that route. We think they were always automatic by default.

So for some reason, that particular pivot table has either gone into a manual mode by default, or there's a problem, and we need to raise a ticket and figure out what the problem is. But they should be automatic by default when you insert them.

If you don't want LIS or pivot tables to be automatic, you can switch them over to manual, which means that you can only refresh them by hitting the Refresh Table option here. If you try and do it through Refresh Sheet, nothing happens.

Hopefully, there are a few different concepts that I've gone over there.

If anyone is having a problem where they're having to manually refresh pivot tables that are currently set to automatic, can you raise a ticket with Evie and the team? We'll have a look into it for you.

When you insert a pivot table for the first time, if it's set to manual by default, please upgrade to the latest version of Solution 7 and see if that fixes the problem. If it doesn't fix the problem, again raise a ticket with Evie and the team, and we will have a look into it for you.

I hope that answers that question.

Evie: Thanks, Simon. Now, looking at formulas: Denise at StepStone has asked if Solution 7 can pull in multi-book detail from NetSuite.

Simon: Yes, you can.

The question is: how do we support multi-book?

If we go into… I think it's done through the Advanced Balances, but let's have a quick look in Standard Balance functions and see if there's anything related to multi-book.

Okay, no, there isn't.

There are two sets of formulas in Solution 7: there are what we call our original functions you could argue they are legacy functions and we've got our Advanced functions.

The original functions here were actually written before multi-book even existed, so we never retrofitted multi-book into those functions. The main reason was that the problem you face is: where do you put the multi-book parameter? Arguably, it needs to be somewhere near the top. You choose your subsidiary, you choose your book, you choose your account.

If we had just added the book parameter, we would have broken everybody's workbooks, because the book parameter would have been near the top of the formula signature. If we put it down at the end, it would have been a bit missing at the end. So there was nowhere really satisfactory that we could put the book parameter.

When we switched over to the Advanced Balances, because the signature is completely different and flexible, it was much easier to implement multi-book.

The main difference between our standard functions and our Advanced functions is that fundamentally, the first three or four parameters are the same: you need your subsidiary, you need to choose an account or a range of accounts for example, a big range, 2023 to December 2023.

The main difference is that although the initial set of parameters is the same, you now have a set of what we call option-value pairs. This allows you to switch functionality on, switch functionality off, change the way functionality works, and apply filters.

One of those is the accounting book. We've got all these different options: filter by class, filter by department, location, customer. We can change the basis of the calculation, switch the currency we're working with, filter by the subsidiary context, and apply a different accounting book.

If I click on accounting book as the first option, each option is an option-value pair. For that option, I now need to choose a value, and for that value, I have a choice of the different accounting books in my system.

Fingers crossed, we have some accounting books in our demo system here. We don’t, but that would allow you to choose which accounting book we want. I can actually multi-select the accounting book.

So, say I want to aggregate two accounting books together, I can hold down the Control key, click on the different accounting books that I want, click on OK. That then puts the option-value pair in. I can click on OK, and that will return the balance for all that subsidiary, all that range of codes for that range of periods for that particular accounting book.

Again, sadly, because I don't have an accounting book in this instance of NetSuite, I can't show you that as a live formula, but that's the theory behind it. Try it; if you have any problems, again, ping an email through to Evie and the team, and we'll jump on and take a look for you.

Evie: Thanks, Simon. Should have warned you about that one…

Simon: Oh well.

Evie: Okay, all right, so Cozy has a… well, has two questions actually.

The first one is: are you able to show the formula to get a year-to-date value?

Simon: Yes, J, do that first, or do you want to ask the follow-up question?

Evie: Yeah, go on. Do that first, and then ask the follow-up question.

Simon: Okay, so, year-to-date. Again, let's do a quick session on how to use periods.

The question is: how do I create a year-to-date formula?

As part of that, I'm going to explain how to do a single-period balance, how to do a balance sheet balance, how to do a year-to-date balance, and how to do multi-period balances.

For a single period, it's super easy: choose a subsidiary, choose the range of accounts. I'm having to use January 2023 because I know there's data in there.

For a single period, we should probably change the label for this at some point. Rather than just calling it “from period,” we should come up with a better name, because when you enter a single period, it's not really “from period,” it's just the period.

Here, I'm going to do a P&L balance of all the four codes for January 2023. Let's copy that sideways and turn that into a year-to-date balance.

This is where the two-period comes in. Let's do the same thing: let's call it April 2023, because I'm running it from January through to April. When I click OK, this is now an April year-to-date balance.

Obviously, you wouldn't hard-code the period names; typically, you would have them in the header somewhere. Again, we recommend using formulas to actually populate those.

If you've ever seen our standard demo, we go through the process of how to build out your periods. Have a look on YouTube and look at some of the demo videos we've got there it walks you through how to build an entire period-based P&L.

Because we're running January through to April, that's now year-to-date April.

Last piece of the jigsaw is the balance sheet. How do we do a balance sheet?

Balance sheet is slightly different from P&L in that the way NetSuite stores its data is always based on the delta for the month.

So if I enter just January, what I'm actually seeing is the delta for January, which for the P&L is the P&L balance, but for the balance sheet or for any balance sheet accounts it's the movement on the balance sheet rather than the balance sheet balance.

If you want the balance sheet balance, NetSuite doesn't actually store it, so you have to calculate it.

The way you do that is you simply go back to the beginning of time and roll from the beginning of time all the way through to the period that you want to report against.

When I click OK, we aggregate the data, and this is now the balance sheet balance. This is now the balance for all of my four codes.

Now, again, this is a P&L code, so arguably I'd want that to be a balance sheet code. So let's switch that from all the four codes, for example, to all of the one codes.

There we go that's our balance sheet balance for our assets section of the balance sheet in this particular example.

Now, there are other uses for periods.

If we go to the original year-to-date calculation and copy it down instead of across, I can edit those periods. Let's say I wanted to make it May 2023 through to July 2023. I can actually aggregate together almost ad-hoc months or ad-hoc blocks of time.

So you're not limited to just period-to-date, and so on. You can actually pick any range of data and aggregate that.

If you want to do quarterly reporting, you can set it from the start of the quarter to the end of the quarter. If you want to do six-monthly reporting, from the beginning of the six months to the end of the six months.

If you have different financial years that you need to report for example, a calendar year report versus a fiscal year report again, just play with your periods to best match the style of report that you need to produce.

The period functionality should just look after you.

Evie: Okay, so now we're moving on to aggregating accounts.

I don't know if this is bringing in balances for aggregated accounts or pulling in all accounts that begin with a certain number, but I'll just ask the question.

Cozy also asked a formula for wildcards: if the NetSuite GLs begin with 113, how would I pick up all those accounts that start with 113?

Simon: Wildcards, 100%. Here, I've created a formula that picks up everything that starts with a four. If I want everything that starts with 113, I can do 113*, and I get zero because, guess what, we don't have any 113s in our chart of accounts.

Typically, you could do something like this: I can insert a column of accounts by number. Let's pretend that 4* is 113*.

If I type in 4*, you can see that in detail, that will give me all of the accounts that start with the digit four. I wanted to show you this way because you can actually reference individual accounts through one of our formulas.

Let me grab a formula, park it there, edit it, and grab the account, select all of those cells. Notice I've selected a range of cells, so I'm actually selecting 4000, 4002, 4004, and so on. This could be 1131, 1132, 1133, and so on.

You'll see that Excel shows it in this notation, and this is something that's called an array. We support the array syntax as well.

This is one way of representing that, where you just select the detail.

I've got another way of showing it, where I could say I want, using my wildcard or using my array syntax, all of the four codes aggregated with… sorry, I want my 4000 code aggregated to my 4002 code on that line, and I want my 4004 code aggregated with my 4006 code on that line.

That's hardcoding the array syntax directly into a cell. If I copy that formula down, I should get a balance. I don't.

Again, well, that was… that wasn't very satisfying, was it? Why didn't I get a balance? Probably don't have any balances on that account. But let's have a look: select that, select that… okay, there we go. I do copy that down. There we go. I've got two balances come back.

That's the balance of my 4000–4002 code, that's my 4004–406 code.

Going back to the question about wildcards: if I type in 4* directly into a cell, or 113* in the specific question, paste that in, I can use wildcards directly in the cell.

Three ways of doing it: referencing cells directly, referencing multiple cells, referencing arrays using wildcards, or any combination of those.

Evie: Thanks, Simon. Could you show us an array for multiple account ranges?

If you want the fours and the fives, for example.

Simon: Of course. If I take that one, let's copy it down. That's my fours, that's my fives that's the balance. You can mix and match literal values with wildcards, and that array syntax and that wildcard syntax is supported for almost every parameter in the system.

I say almost every parameter. The only places it's not supported are in some subsidiary fields. Subsidiaries don't support wildcards because we've got the word “Consolidated” so that it matches NetSuite, and it doesn't work for periods. But for most other parameters class, department, location you should be able to use wildcards.

Evie: Next question: is there a way to exclude accounts? For example, say you wanted all the fives, but want to exclude 5100.

Simon: That's really simple, and how you do that is through your formula.

So, here I've got all of the fours and all of the fives, and if I want to take out all of the fours or all of the fives, just take one formula and minus off another formula. In this case, I'm going to hardcode it into the formula itself: 5*.

So, I've got all the fours and all of the fives aggregated together, and I'm taking away all of the fives, which should give me all of the fours. This means I end up with 10,000 uh, 10, 84,000 which matches my number up here.

Yeah, really simple: just use a formula and take a formula away from it.

Evie: All right, a question now from Austin: if we are comparing actuals to budget, are we able to drill down into both at the same time? For example, if you want to see a variance by location or department, you can do it without having to drill down into each one separately.

Simon: Let's have a look and see if the specific functionality that I'm thinking about is supported. Let's just try that.

Bear with me while I grab that formula… one, two, three. I think the answer is no, but let me just experiment with something.

Something that a lot of people don't know is if you take two formulas that are the same and right-click on them, you get "drill compare." But drill compare is grayed out. Huh. That I wasn't expecting. Get drill down but not drill compare that I wasn't expecting.

My guess is the answer to the question is currently no, but let's just have a quick look. Let me change that into a BUD function and add an extra parameter. Let's populate that parameter with a genuine budget code and compare actuals to budget.

Oh, that's interesting. You can tell these sessions are scripted because I was expecting that not to work.

If we right-click and select "drill compare by class," we get a difference and a variance. I have to confess I wasn't expecting that to work.

It would be interesting to get people's feedback on that functionality. Let us know if it works, and I apologize in advance if we don't respond to your email because we're probably going to get hundreds of them.

So yes, it appears to work. Have a play: select multiple cells, right-click, drill compare, and let us know if it works.

I've just flooded your inbox. Sorry, Evie.

Evie: I didn't even know this could happen. I knew drill compare was there, but I didn't know you could do it with budget and actual.

Simon: I expected it to work actual-to-actual; I didn't expect it to work actual-to-budget.

So that's functionality that's either been added since we first developed it, or it was meant to work that way.

Evie: All right, fab. Right, so we've got five minutes left. I think that probably gives us time for one more question.

Eric is asking if there is an advanced table where you can select multiple subsidiaries.

Simon: I would assume that means the pivot table functionality?

Evie: Yeah.

Simon: Okay. So I believe you are limited when you pull through a pivot table to only… I'm just going to try multi-selecting. Yeah, you can only select one subsidiary when you create your pivot table.

However, what you should be able to do again, I'll use Jan 23 to December 23… oh, what, help if I could spell once you've pulled your pivot table data through, what you should be able to do is lean on the pivot table functionality itself.

This was the reason why we integrated with Excel's pivot table engine. One of the great features in there is what they call a slicer. A slicer is effectively just a filter.

Let's park that over there. Oh, I don't have my subsidiary coming through. Let's pull the subsidiary through, right-click, choose columns… let's find the subsidiary. The subsidiary is… oh, for anyone who's never used the choose column screen before, let me just explain what we're looking at here:

This is the ability to pick columns out of the underlying NetSuite schema and into the pivot table, the list, or even type in your own formula. By default, because this is a transaction listing, these are the details that are available on the transaction lines.

From there, via the "More Information" tab, we can go up to the transaction detail. Let's go into the transaction itself, and from there, I'm hoping we can get to the subsidiary.

Okay, it's not available there, so probably means we have to go one more hop to get to the subsidiary.

Ah, now I know why he's asked that question because it's not there.

Oh, here. That's why we need to go into the subsidiary. From there, we can pull in the subsidiary name. I can click OK. Once we pull the subsidiary name through here we have the subsidiary name if I go back into the slicer, I can now choose the subsidiary itself. I can click OK.

I now have a slicer, or an additional filter, that I can use to filter the data. I can say, "Show me Honeycomb and Mexico together," in this case, in my pivot, in my… summary pivot. If this were a summary pivot, I'd have the same filter, showing summary information across the workbook.

From a filtering perspective in NetSuite, when you actually insert or edit the pivot table, no, you can't do it at that level, which does make it slightly different from the way our Advanced Balances functions work, because you can do it at that level for Advanced Balances.

However, once you've inserted the data, you can filter what you're seeing using Excel's filters or, in this case, using a slicer. At some point, we will bring pivot tables into line with our Advanced Balances and give you that multi-select functionality. At the moment, we'll have to go down the filtering route again, not quite the answer you want, but a way of being able to filter by subsidiary.

Evie: Thanks, Simon.

All right, I think that's a wrap on Ask the Expert 2024.

Thank you to everyone who comes along to these sessions. We do enjoy doing them. Thank you for all your great questions, and thank you to Simon for answering them.

As always, if we haven't been able to answer your question today, please send an email to support, and one of us will reach out to either arrange a session or see if we can answer your question by email.

Simon: Again, if you can be patient with us over the holiday season, we'll be back in January. If we don't get to answer your question before then, please bear with us, and we'll get back to you in January.

Evie: Yes, we do have people on every day except for Christmas Day and the 26th. If anybody has an urgent issue, there will be someone around to help you, so don't worry.

As a final reminder, the recording will be sent to you in a couple… well, I say in a couple of days. Hopefully in a couple of days; if not, then in January.

Wishing everyone a great Christmas and a Happy New Year.

Simon: Happy holidays, everybody. See you next year!