Business Central: Exporting the Data Behind the Report

Sometimes when you’re analyzing data, you want a report. Sometimes it would be easier to have the actual data. Exporting reports from ERP / Accounting systems to Excel has been possible for a long time, but the data you wind up with in Excel is often ugly. Because it is formatted with totals and subtotals, not to mention spacing, lines, titles on every page, etc., it can be harder to use than just rekeying the data. 

Microsoft added the capability to export the data behind the report a couple of versions ago (it was optional in 2021 Release Wave 1, it appears for all users in 2021 Release Wave 2). It’s easier to demonstrate than discuss, but here are the steps followed by a video.

  • Select the report you want to export to Excel. Complete the filters just as you would if you were going to print the report.
  • Select “Send to…” and choose the option “Microsoft Excel Document (data only)”
  • Open the document in Excel and remove any data you don’t need.

Note that you’ll also see data here that appears as literal text on the report such as “Aged Customer Balances” in the example below. Business Central also sends text fields to the report formatter along with the data. Just ignore this information and hide or delete it from your worksheet.

Here’s a video demonstrating this feature.

Adding Attachments With the Business Central Mobile App

Business Central’s mobile app has been out for several versions now. It displays pages in a size friendly (adaptive) way on mobile devices. Horizontal menus at the top of pages are converted to page-filling scrolling buttons that are easy to tap, even on a small phone.

Limits of a Mobile Device

Of course, because of the amount of data entered for an order (item number, description, and quantity at a minimum), you might expect to have a bit more difficulty with these tasks. Business Central’s app converts lines in a grid to full pages, making them easier to navigate. The one issue I had was getting a “full picture” of an order. For example, answering questions like, “How many lines are on this order?” or “What is the total and tax?” or “Does that include the discount?” All of this is possible, it just takes a bit of scrolling and pinching to get to it.

Attaching Photos to Documents

One thing we’ve been asked for in previous versions is the ability to take a photo and attach it to an order. Perhaps a customer signature on a delivery ticket. Or the items delivered on site (like Amazon’s delivery).

There is not a “take a picture” button in the current Business Central app. It has always been possible to take a photo and attach it to a document in Business Central. Depending on mobile internet speed, this can be quite quick. In the latest version of iOS, however, we found a way to take the picture and attach it without the step of saving it and locating it again.

Demonstration on iPhone

It’s easier to show this than tell about it, and this also gives you an idea about the look and feel of the app as well.

Let us know if this is helpful.

Business Central Security: Permission Sets

Picture of hands in handcuffs to illustrate security As I write this, I am looking at the security in the newest version (19.1) of Microsoft Dynamics 365 Business Central. We had noticed in the previous version that some of the permission sets give more access than the name indicates. This post will offer some actual examples of what we found in the newest version.

Permission Set: D365 Team Member

A Team Member License is a low-cost license that provides read-only access to the tables in Business Central. It also provides the ability to enter quotes and a few other items. We generally describe it as designed for management and sales people. It does not allow posting. When you assign a Team Member License to a user, the D365 Team Member permission set is assigned by default. Let us assume that you have a salesperson using this license.

Here are some interesting permissions assigned by this permission set:

  • Bank Account: read and modify
  • G/L Entry: read, insert, modify, and delete
  • G/L Account: read and modify
  • Customer: read and modify
  • Vendor: read and modify
  • Item: read and modify
  • Job: read and modify

This license type is prevented from posting, so these permissions will not allow the team member to post general ledger entries. However, it does allow read-only access to any table in the system. A person with these permissions will be able to see all of the general ledger entries. This will include entries into the salary accounts, and if salaries are posted in detail in the G/L, amounts will be visible. In addition, the user will be able to see the chart of accounts and balances.

Obviously, you may not want to give this permission set to some users, particularly if you do not want them changing customers, vendors, items, and jobs.

Permission Set: D365 Basic

Let us say you decide to pick a more minimal permission set. This permission set, D365 Basic looks as if it would provide basic access to the system, and it does. It also provides the ability to read general ledger accounts and entries, and the ability to read, insert, and modify customers, vendors, and items.

A Bit More Info on Permission Sets

It appears that Microsoft has been reworking the permission set code in the last few versions. Developers can combine permission sets to make new permission sets. The permission set D365 BUS PREMIUM, for example, includes the following code:

    IncludedPermissionSets = "D365 BUS FULL ACCESS",
                             "D365PREM MFG, EDIT",
                             "D365PREM SMG, EDIT",
                             "D365PREM SMG, SETUP";

This code copies in all the permissions from the other permission sets. It is a good move from a maintenance standpoint, since it means that a change in one permission set will cascade through others. It makes it a little bit difficult to be sure exactly what you are getting when you assign a permission set.

Conclusion

The biggest lesson we have learned from this exercise is that you cannot just assume that permission sets provide logical permissions. In other words, you cannot take for granted that they mean what their name says they mean. And this means that any company needing tight security will be designing their permission sets pretty much from scratch.

Our hope is that Microsoft will add functionality to the permission set extension to allow us to DENY permissions that have been granted. As of this date, Microsoft documentation on the permission set extension says the following:

The permission set extension object in Business Central adds permissions to an existing permission set defined in AL. A permission set extension object cannot remove permissions from an existing permission set, it can only add permissions.

You can read more about the permission set extension object in the Microsoft documentation.

Be careful that the security you assign is the security meant to assign in Business Central.

Automatic Business Central Upgrades: Bad Information in the Wind

I got an email from Microsoft the other day about a Business Central upgrade for one of our clients. Part of the email read as follows:

Your Business Central environment could not be updated to version 19.0 because one or more of the installed extensions failed to update successfully. Review the error information below and complete the recommended actions to resolve the issue before the update can be attempted again.

The extension identified was an extension from Microsoft AppSource, and is key to the client’s business. The email contained a laundry list of errors that were preventing the upgrade. Since we don’t have access to the source code, we couldn’t fix any of them.

Business Central Upgrade Troubleshooting Process

Here is the process we followed:

  • Created a sandbox with an exact copy of the customer’s data (this takes a couple of clicks).
  • Upgraded the app to the newest version using the App Management panel in admin.
  • Rescheduled an update for the sandbox.

The result? We got the same list of errors. An email to the vendor resulted in this suggestion (from the vendor’s tech support):

To be able to proceed without errors, you will have to uninstall the [vendor name removed] app, do the update, and then reinstall the app as workakround. [sic]

So I’ve uninstalled the app in the sandbox I created. Now we wait to see if it upgrades. [A quick aside: to do this in production would mean that the app was not available when the upgrade was done. If the app proves incompatible after the upgrade, we would not be able to reinstall it. That would be a problem.]

Reloading the App from AppSource

While I was waiting, I verified that I could install an AppSource App in a sandbox. I found some documentation that suggested that it was possible, but nothing with a set of detailed instructions. I also found some blog posts that suggested that it was not possible (see below for why I don’t trust blog posts completely).

Frustrated Employee during ERP implementation photoIn the course of all of this, I ran across the following statement on a site. It is accompanied by a video of what appears to be an experienced man stating–emphatically–that AppSource apps update automatically, and are required to be compatible in advance of the release of a new version. The problem is…I had an email from Microsoft proving just the opposite. Here’s the statement from the website and video:

The benefit of using apps is that Microsoft will update your Business Central every month – and you don’t have to do anything. No cost. No tasks. No upgrading project.

Here’s my pet peeve: this site is selling itself as a training site and it is completely, totally, and dangerously wrong! If the app version you have loaded isn’t compatible with the upgrade, Microsoft will eventually upgrade and delete the app. The data will still be there, but you may have an anxious client while you reload the app.

Microsoft Documentation on AppSource App Upgrades

But don’t take my word for it. Here’s the first paragraph of the documentation on Upgrading Apps from Microsoft:

When an updated version of an AppSource app becomes the active version in the Dynamics 365 Business Central service, tenants do not automatically get this updated version. This upgrade must be done manually by getting the latest version of the app in AppSource.

Ok. All the advice you get in the marketplace from “experts” isn’t good advice: not by a long shot. Now that that rant is out there, back to verifying that I can load an AppSource App in a Sandbox.

If you need some help with or information on a Business Central Upgrade or Dynamics NAV upgrade, contact us with an overview of your situation or schedule a chat.

Business Central – SIFT Technology, NAV Conversions, and Performance

Mature man getting a headache from taxes and billsWe’re in the process of moving a client from a 3.6(!) database to Business Central 18 (Spring 2021). In the database, there is a custom table that has several Flow Fields defined as sums of the Amount fields in G/L Entries and the Detailed Customer Ledger Entry table.

Just for the giggles, you should know that the history goes back to 2003, and there are 1MM+ records in the tables, but not many more than 1MM records. To make it a little more interesting, we didn’t do the original customization, so we’re figuring out the customization as we go.

Fighting the Performance Battle

We’ve converted the data, refactored everything to an AL extension, and are testing reports. The problem came when we hit a couple of reports based on a table added in the extension. The table has 4 flow fields. And the reports take forever to run (hours in one case). They are slow on a local copy of the old database, but they are much slower on BC18. Much slower. Like ten times slower.

So we looked at the old code. As it turns out, the flow fields were based on data from the base Business Central table as well as data from the extension. Previous customization in C/AL (3.6) had added a key on all of these fields. As you may know, table extensions cannot use fields from the base table as part of keys.

Because flow fields rely on keys, performance took a huge hit.

The Solution

the solution was actually fairly straightforward. Since we needed the data from the base table in the extended table, we created triggers to copy the data. We were then able to build keys (SQL indexes) on the data in the table extension.

    trigger OnBeforeInsert()
    begin
        CopyFields();
    end;
    trigger OnBeforeModify()
    begin
        CopyFields();
    end;
    trigger OnBeforeRename()
    begin
        CopyFields();
    end;
    procedure CopyFields()
    begin
        "ext G/L Account No." := "G/L Account No.";
        "ext Document Date" := "Document Date";
        "ext Posting Date" := "Posting Date";
        "ext Amount" := Amount;
    end;

The result was dramatic: a report that took 2.5 hours to run before the change ran in 15 minutes after the change. It still seems like we can optimize this a bit, but there also a tremendous number of calculations happening in the report.

Tech Support Woes

As both a provider of tech support and a consumer, I understand both ends of the conversation. I still tell the story of the customer who called and said, “My computer monitor just went black. Poof.” 

I asked, “On the power button over there to the right on the computer tower, there is a light. Is it on?”

“No,” they said.

“Could you press the power button?”

“Sure,” they said. I heard a computer whirring to life in the background. There was an uncomfortable silence.

“I bet you were taking a disk out of the drive, weren’t you? The eject button is right above the power button,” I offered.

“Thanks, that got it.” was the reply. Click.

And then there’s this, from yesterday. We are working with tech support for an extension to the eCommerce platform, Magento. We got the following email, “It seems that we do not have access to your Magento admin any longer (link to the picture above). Please update the access data so our Tech Team could look into the details and assist you. (Link to the secure site for submission of the credentials.)”

CAPTCHA is the little utility that asks you to select all the cars from a checkerboard of nine pictures. Irritating, I know, but it prevents hundreds of bogus registrations. I’d guess that the Tech Team at this vendor can’t distinguish cars from traffic lights.

More to the point, we’ve observed that this particular vendor likes to gaslight us. With 35+ years of experience, I’ve probably got more years’ experience in IT than most of the Tech Team has drawn breath. But this brings up a bigger issue.

It’s a common thing in the tech world–or so it seems–to gaslight end users who aren’t technical (act like they are crazy or don’t know what they’re talking about when they report an issue). Don’t get me wrong, there are users who can’t change the battery in a wireless mouse. There are also times when the data provided isn’t adequate to solve the problem, or when the problem comes and goes without leaving definite traces or symptoms.

We teach our Tech Support employees to respect and understand our customers. I’ve learned a lot in 35 years by listening to our customers. Some days I thought their way was better; some days I thought my way was. We always worked out a solution by combining ideas.

That’s the way it should be: IT folks should help end users figure out how to use their technology better. That’s what we do at Data Guidance Group.

My two cents.

 

SolarWinds Security Product Breached

DHS reported a breach of several government agencies, apparently by Russian hackers. The full article is here. More details on the technical implications of this are here.

Many government agencies use SolarWinds products; any organization relying should follow the detailed developments closely.

The most technically interesting and sophisticated item in this report has been the hackers’ ability to bypass two factor authentication as discussed in the second article above.

No Action Required iPhone Hack

For most people, the thought of having their computer or smart phone taken over is frightening. Our standard advice to prevent hacks: do not open anything from someone you do not know. Do not open anything you were not expecting. Do not open anything that looks suspicious.

Wi-Fi iPhone Hack

For this newest iPhone hack, you don’t have to do a thing. Take a look at the video in the article below to see a rapid, frightening hack:

https://mashable.com/article/iphone-hack-scary/

Just to check your “hackability,” let me ask this: Did you at least think about it before you clicked that link?

Protecting Yourself

There is redemption in the article: keep your devices updated; this prevents the hack. I know there can be irritating changes in iOS with new versions, but nothing like the irritation of being hacked.

PPP Loan – IRS Releases Rulings on Deductability of Expenses

When the CARES Act that created the PPP (Paycheck Protection Program) was written, Congress included a provision that specified that PPP proceeds that were ultimately forgiven under the program were not taxable. The IRS has issued a series of Revenue Rulings (starting with 2020-32) that state that expenses paid with tax-free income are non-deductible.

Many people believe that this does not represent the intent of the legislation because–in effect–it adds the amount of the loan to the forgiven borrower’s bottom line. By denying the deductability of the expenses paid with the loan, the IRS accomplishes the same tax effect as including the proceeds of the loan in income.

Now the IRS has released ruling 2020-27 stating that even if the loan is not forgiven until 2020, the expenses are not deductable in 2020.

If you have questions about this, you should consult your tax advisor. For safety’s sake, it is important to plan for the extra income in 2020.

How to Try Microsoft Dynamics 365 Business Central

Microsoft D365 Business Central looks like a very busy web site when you login in. There is a learning curve; finding features and reports goes faster if you have a bit of help. We released an 11 minute video this week to help navigate through the system. A few features covered are discussed below.

Selecting Business Central Role Centers

The first page that comes up when you log in to Business Central is called a Role Center. There are different pre-built roles in Business Central like “Business Manager” or “Accountant.” Each of these has different Role Center links that make it easier for someone in that role to get to the functions they need. To see and change a user’s Role, click on the gear icon in the upper right corner and choose, “My Settings.”

Dynamics 365 Business Central Role Center screen capture.

Managing Multiple Companies

You can have as many companies as you want to set up in Business Central. Change companies on the “My Settings” page just like the Role. The license that installs with Business Central (the trial license) requires that company names begin with CRONUS.

Menus, Tabs, and Actions

The Role Center for Dynamics 365 Business Central also has a drop down menu at the top. Quick links (like Customers, Vendors, and Items above) take you directly to needed data. Actions are like quick links; actions preceeded by + will add a new item. So the Action labeled “+Sales Quote” creates a new sales quote.

More Information on the Video

The video covers this and more. Included are suggestions for using the sample data. For example, Blue, Red, and blank locations (warehouses) are good choices because they don’t require you to use the Warehouse Shipping and Receiving functions in the system to create and post Sales Orders and Purchase Orders. The full video runs 11 minutes and provides an introduction to finding what you need. We are planning a follow up video on making design changes in BC.

The Business Central video is available on YouTube. If you’d like to set up a trial of BC, you can do that here. More information is available on our website.