Quick Way to Troubleshoot a Report.

Hi Everyone,

Report is usually connected to one or many data sources, in Acumatica they are represented by Data Access Classes (DAC). Sometimes DAC is not really what we have in SQL database. DAC may have calculated synthetic fields, added during its [long] life. DAC may also have additional fields from other tables.

For example: FABookHistory data access class has field CreatedByID which corresponds to field in its main table from SQL DB, but also it has field CreatedByID_Description, which is coming from joined table with user names.

And all of us, are professionals with SQL of course. :)
If something goes wrong with our report what we can do?

1. Run SQL Profiler and try to catch query our report is running.

Well its ok, except if we trace it on production environment with multiple users printing similar reports or simply loading the server with their requests. I am not saying Profiler is a bad choice, it is OK, but sometimes it may be difficult to trace.

2. We just added a much simpler way to get SQL query report runs. And this query will include all the parameters we added to report. Here is how to see it.

First, run the report with all the parameters

Second, once you saw the report outcome, press Help->Trace

Third, find on the trace block that responsible for our query

Copy that query to clipboard. Then you can either paste it into your Query Analyser or use one useful tool to format it before. Magic Tool

Be Happy,

Sergey.

Hardware Upgrade? Compatibility Issues?

Hi Guys,

Recently I got a call from one of the legacy ERP system (Solomon IV) customer. They are still using software released in late nineties. It is no longer supported by Solomon or Microsoft.

On the client side they are using Windows XP. Solomon IV works fine with XP. But once we upgrade the PC, operating system will also get replaced. Most of the current laptops are supplied with Windows 7, and option to run XP in virtual mode seemed not to be the best.

Really, what are they upgrading their PCs for, to run a slow VM with XP???

So the only path for them seemed to be an upgrade to the latest version of that Solomon software line... Migrate all the customizations to that new version. Reinstall the client software on their PCs. Retrain the people to use new version... Looks like a full scale upgrade with a small implementation attached.

But what will happen in 5 years’ time, when the current version will age and require replacement due to hardware upgrade? Should we invest in customizations upgrade and migration, reinstallation etc.? Again? Just because of the Hardware Upgrade?

We proposed them to think about upgrade to Acumatica instead.

WEB based system do not require any specific compatibility with the hardware. And when you decided to buy new gadget, Acumatica will start working on it right away.

It does not require local client installation; as long as you have a WEB browser you can use it.

Cloud deployed ERP would be even better solution, not only clients became independent from Hardware Upgrade issues but the server as well.

They are thinking about it and if it turns to be a win, I will let you know.

All the best,

Sergey.

Cloud Mechanics. Field News.

Hi Guys.

When we started our first meeting with one of the customers, two IT persons were on it. When it was decided - we go for a cloud, true satisfaction was expressed on their faces - At last, we give up all this maintenance burden to someone else.
It was just a beginning...

Clearly, main benefit of moving out the server from the office supposed to be a maintenance. IT staff no longer need to do anything they usually do to service the instance. Which is what:

• Hardware Maintenance and Repair
• Power Supply - stable of course
• Internet bandwidth to the server
• Security of the hardware
• Protection from external attacks
• Application Maintenance (upgrades, fixes etc.)

When we actually deployed the server on the cloud, Azure cloud, more challenges appeared.

Issue Number One. Performance.
When you have a server box, you know how many CPUs are there and literally how much time it takes to add 2 plus 2. And anytime you try to add 2+2 it will always take the same time to get a result, we are expecting some consistency, right.
But when we are on the cloud, especially on Azure, mechanics of this one is such that it uses Virtual Machines, without assurance on performance consistency.
Here is what Azure team states:

"At the present time the SQL Azure Service Level Agreement guarantees Uptime Service Levels but does not guarantee Performance Service Levels, for example the Service Level Agreement does not contain any provision that guarantees a certain level of throughput or ability to accommodate any particular level of workload. ....“At this time, although there are availability guarantees with SQL Azure, there are no performance guarantees. Part of the reason for this is the multitenant problem: many subscribers with their own SQL Azure databases share the same instance of SQL Server and the same computer, and it is impossible to predict the workload that each subscriber’s connections will be requesting.”"

Azure Elasticity Guide

You see, the server, hardware server, sitting on that cloud, don't forget cloud is actually a data center on the ground, well, that server, is hosting many virtual machines, and not always we have a luxury to know how many as it is defined by the cloud provider. Then, what happens when all these users or "Guests" are starting to do something with the CPU or hard drive on that hardware server, well obviously it gets slow. So we can not expect the same time for execution of our statement at any point of time. This is terrible thing, especially for ERP, where end users expect low latency on response and constant performance characteristics.

Issue Number Two. Backups.
Cloud does not allow to make a backup neither for the server itself - bare metal backup, simply because the servers on cloud are clustered and backed up in a special way, and if failed will be restored by provider. But most importantly - we can not make a backup of a Database. By design. The reason mentioned by cloud architects was - if we allowed it, most of the Internet traffic will be eaten by those backups transferred from the cloud to client premise, therefore we restrict the backup, by design. The only way to "backup" database was running a script that will copy all the tables to remote database. It was not a backup but rather copying the information in different format. Well the idea of backup is to keep a file, just in case if user enters something wrong we can restore the backup and problem will be settled. Cloud do not have it.

Issue Number Three. Men in Black (i.e. Internal Audit)
That was the most weird issue I ever had in my ERP implementation life.
When Internal Audit came and heard about the cloud - they started asking questions.
Can we see the server, where is it? - No you can not it is on secured premises and Microsoft does not allow anyone to come to their data centers.
Does it comply with special requirements for hosting - We can't know that as Microsoft does not provide any documentation on that.
How secure is the data center, can we check - No you can not, we do not even know the exact address of MS hosting centre.
What happen if server crash? - Well its not on premise server, its on the cloud, meaning its clustered it can't crash, even if the whole cluster crashes they have a backup and will restore it.
Who provides SLA and what is the up time - Here we have SLA from Microsoft, seemed to be OK.
Do you have a mirror server to run in case main one dies - We can have one but it will cost double as it must sit on the cloud - see post about the backup - we can not keep server on premise because its a cloud one so we have to pay twice for the cloud mirror.
How protected is the access to the server - Well it uses HTTPS so its protected
Can it be hacked - well everything can be hacked, but it is extremely difficult to do.
Plus lot of standard questions they ask even about on-premise installation.

It all makes me thinking that the main stopper for the cloud systems now is not a user any more. User wants to get rid of the hardware, move it to the cloud. But rather, old fashioned audit rules that were created to be satisfied by on-premise requirements, and yet to be adjusted to cloud reality of today.

All the best,

Sergey.

Alternate Payee.

Hi Everyone.

Alternate Payee is a concept in Accounts Payable and Accounts Receivable. It is when your vendor is either managed by another company, who receives payments on their behalf or simply vendor wants you to send money to a different company from their own. It could be just settlement between them and third party.

The problem is how to achieve it, when printing checks or processing GIRO (Singapore term for ABR) transfers.

In Acumatica we implemented multiple Vendor Locations, which can be used to indicate Alternate Payee. Each location has Payment Settings attached. And you can make that location to be taken as default when preparing a check.

Here is how it looks at Vendor Master screen:

And here is the place where we can set Payment Specific by location:

That is all for now.

All the best,
Sergey.

Splitting an Asset.

Hi All,

Recently I got a request from one of our customers, what if we purchased 100 chairs, added then as a single asset, then later decided to split between 2 different places?

Immediate answer was - ok, may be you can dispose it and create a new one - tough way.

Then we decided to add a functionality to Acumatica Fixed Assets module, that does exactly what is stated in the header - Asset Splitting.

In fact the operation of Asset split is not simple. We should divide Accumulated Depreciation and Cost of Asset between spinoffs, however existing Depreciation Expenses should not get split. It simply because the new asset owner - would it be a place or department, may not wish to get incurred expenses from the previous one.

Also, we can use asset split if need to dispose asset partially.

So what this operation do - it takes the original asset, reduces its cost and accumulated depreciation, it also reduces quantity if it was more than 1 before split.
Then it creates a new spin off assets, as many as you decided during splitting, it can be just one spin off or multiple. All will get a fraction of a cost, then accumulated depreciaion.

Usefull Life of the spin offs should not change, it must follow the original asset. At least at the moment of split.

Thanks to our development team, we had implemented this functionality fast and accurate in version 3.0

All the best,

Sergey.

Adding View to Report.

Hi Everyone,

Adding tailor made views to Crystal report seemed to be an easy task. Same for Acumatica. Native Report Designer will only "see" your view if it was properly published.

For example let's modify a Batch Register report from General Ledger. It has summary information, but I want to see the number of transaction lines per each batch. Well I am sure it is possible to do it just inside a report without adding any view, but just for training purpose lets do it our way.

Here are the steps to add tailor made view to a standard report:

1. Create a view in an SQL, here is nothing special. Let's do it using TSQL or Management Studio. What I added is:
create view GLCount as
select CompanyID, BatchNbr, COUNT(*) as NbrTrans
from GLTran
group by CompanyID, BatchNbr

2. Next step is to create a Data Access Class in Acumatica App that will represent that View for our report. System Management->Customization Management->Code Editor, select any project you with to add that DAC. If you have not started customizing your Acumatica yet, just create a new customization project and save it to database before this step. I selected MAIN project, then click New File:

Next choose IBqlTable type and in Class field type the name for our view - GLCount, check "Generate Members from Database" checkbox:

Then click OK.
It creates a DAC for us but we have to do 2 amendments to it before it can be placed inside a report.

A. We have to assign a namespace to the DAC. Since I work with GL, let me add the General ledger name space to my DAC.
B. We should indicate that BatchNbr is a Key field. This is simply because report should know the primary keys for the View. Since Company ID is obviousely a key, the BatchNbr remains the only one we should indicate manually.

After amendments the file should look like this:

Once all saved, have to publish the customization. It can be done via Project List screen.

3. Now we can amend our report. Before changes Batch Register report looks like below:

I get back to parameters and pressed Edit Version to open the report in designer:


 Once you open Report Designer, go to File->Build Schema, login to the database you are working with and here is our newly added View:

Here we go. The rest is just the same we do for standard Tables/DACs.
Set Relations, then add a field to report from our view. Here is what I got at the end:

And just to make sure that it does what it is, lets navigate to that batch with 12 lines:

Cheers,

Sergey.

Dashboards for Management Reporting.

Hi Everyone,

ERP user, especially manager, would be happy to see relevant information immediately upon login.
To achieve it in Acumatica I have to create one or more dashboards and place them on a home page.

Here is what I was trying to achieve and below is the video with 10 minutes efforts required to complete that.

Here is a demonstration video:

Every dashboard in Acumatica is based on Inquiry Screen. So first we have to find appropriate screen that shows us information we would like to present in a graph.

For the Top 10 Customers dashboard, I used screen Customer Summary from Accounts Receivable module. Just open the inquiry, choose the period we like to see and sort by PTD (Period to Date) Sales amount

Next I pressed Dashboard Button and selected options to represent it as a funnel, select only top 10 records and place it on my homepage.

Category and Value fields are the ones represented on the resulting dashboard, sorting and aggregation is optional in my case, inquiry screen did it for me already.

One hint may be useful, if you wish to navigate from the dashboard to the inquiry screen, just press on the Dashboard Name field, in my case it is Top 10 Customers field.

Hope it helps,
Sergey.

Reporting Tax. When Invoiced or when Processed?

Hi Guys,

When I receive an invoice from my vendor, when should I report my VAT(GST)?

If you ever had this question, then, probably in your country it is allowed to do in either way - based on invoice date or based on processing date.

Let me explain.

For example when we purchased some equipment from vendor A. We may receive invoice immediately on equipment delivery or much later, it depends on how we operate with vendor or how fast mail is delivered or how contract is drafted etc. etc.

The matter is - invoice can be received much later than it was issued. So how do we deal with revenue authority in this case?

Specifically say invoice was issued 15 MAR 2012, but receieved only on 15 APR 2012. A month later.

Well, technically, date of the invoice should be the date when we recognised our liability to our vendor. It is not a problem for us we can deal with it internally, it will only affect when we should pay for the bill. Right?

But for the Revenue Authority it is not that simple. We can report our tax either based on date of the bill, which is 15 March or date when we processed it - 15 April.

How it will affect us - if we decided to report based on date of the invoice, assuming (for simplicity) that our company fiscal periods are based on calendar months, we will post liability to Revenue Authority in 03-2012 period, but if we decided to account tax based on processing date - we should post it in 04-2012.

Seems logical. But not many ERP systems allow us to do it.

Issue becomes even more complicated when our Tax reporting periods are based on different intervals then our fiscal periods, let say we report Tax qurterly.

In my example then we should report Tax in Q1 if we process it based on Invoice Date, or in Q2 if we report based on processing date.

Well, here is the challenge.

In Singapore, IRAS (Inland Revenue Authority of Singapore) regulates it in the following way:

You should decide from the beginning how you process your tax either based on Bill date or Processing date. And company should use it forever.

Here is the link to that document: Claiming input tax in the right accounting period

And here is how this issue is addressed in Acumatica ERP:

Cheers,

Sergey.

Acumatica Singapore Blog Intro.

Dear Reader

This blog is just created. I will try to share info about events, features, techniques, basically any interesting things happening within and out - related to Acumatica software.

Which is as you already know - web based Enterprise Resource Planning solution for medium sized businesses.

Here is a link to our main web site: www.acumatica.com

From Singapore I will try to address South East Asia region.

Sincerely Yours,

Sergey Vereshchagin.