RDLC Report in Business Central

In today’s blog post, we’ll be discussing reports in Business Central. Specifically, we’ll cover the creation of a client report definition (RDLC) report. Enjoy!

When we create a new report for Business Central, there are two things we must consider:

1. Defining the report dataset of data items and columns

2. Designing the report layout.

These steps will show how to create a simple report based on an RDL layout. A report is composed of the following items:

Now we will create an AL report in in Microsoft Dynamics 365 Business Central. In Visual Studio Code, create a new project using the AL:Go! command:

Now create a new file, for example: CustomerListReport.al

In the CustomerListReport.al file, use the treport snippet to create a report and then change the ID, Name and add fields and an option page, as in the following example:

Here, we’re using fields from the Item table to build the dataset for this report. We’ve also added a variable “ShowUnitPrice.” Additionally, we also added this variable in the Request Page, which can be used to display the unit price in the report.

We defined the UsageCatagory as ReportandAnalysis, so we can search the report directly from the Search option in Business Central Client. We defined Layout as RDLC and specified the path in RDLCLayout.

Now build the extension (Ctrl+Shift+B). The ItemListReport.rdl file will be created in the root of the current project.

Click on the ItemListReport.rdl and then right-click and select Open Externally, which will open the layout file in SQL Server Report Builder.

Now, add the columns from the Datasets folder into the table and save the .rdl file and add all the columns in the Report layout. Below is the sample of our report design:

SAVE and CLOSE the SQL Server Report Builder.

In Visual Studio Code, press Ctrl+F5 to compile and run the report in Dynamics 365 Business Central.

Search for ITEM LIST AL report and select Report, specify filter if required, and run the report:

That’s it! We just quickly and easily created an RDLC report in Business Central.

Happy D365’ing!

Creating Permission Sets with the Recording Feature

Setting the proper permissions in Dynamics 365 Business Central can be overwhelming. There are many out-of-box (OOB) permission sets available that can be copied and manually edited to meet your individual needs – but there are thousands of tables and permission objects within the software and locating them all is not always easy. This can lead to a lot of trial and error and inevitably, frustration. Using the recording feature to set these permissions might be a better alternative. The recording feature will track along with you, recording every object you access, and then save it to your custom user-defined permission set. There are a couple of different options for this: 

1. To start, search for “permission sets” in the TELL ME WHAT YOU WANT TO DO box and select Permission Sets

 

2. Click + New to create your user-defined permission set. 

 

 

3. Once you have chosen a permission set name and a description, select Permissions

 

4. You’ll see that this permission set is empty. Select Start to begin the recording process. 

 

5. You will be prompted to start the recording. Click Yes. Now you will need to go back to the TELL ME WHAT YOU WANT TO DO box and start selecting things you will want included in your permission set. For instance, type “Customers” and go to your customer list. Select a customer. Then return to the recording window. Select Stop (located next to Start). You will be prompted to add the recorded permission. Select Yes. Your permission set now includes customer access. However, at this point you’ll see that you only have Read rights to the customer table. The reason for this is that you viewed a customer but took no other actions.  

 

6. You can manually edit the rights on this screen, or you can start recording again and do the actions you wish to have access to. To do this, start recording, go back into your customer list, create a new test customer, change the name of that customer, and finally, delete that customer. Stop the recording again and add the permissions. The permissions for customers have been updated. In addition to read permissions, you now also have InsertModify, and Delete. Your list of objects has also been increased. If you don’t want delete rights in this permission set, simply change that Yes back to a blank field.  

 

7. Repeat these steps as necessary for all desired access (lists, tasks, reports, etc.) and when you are done, assign this permission set to the desired user(s).  

In most cases you will use an OOB permission set as your framework and then add the additional permissions you need but whether you start from scratch or use an existing permission set, using the permissions recording feature can help you to get the exact permissions you’re looking for.

Happy D365’ing! 

Opening Balances: How to enter them in NAV/BC

With any system implementation, there comes a time when the closing balances from the legacy system become the opening balances in the new system. In today’s post, we’ll explain various methodologies that are available for this stage of the implementation and explain the pros and cons of each. Enjoy! 

To set the framework – let’s say that we have the following balances/information to bring into the new system: 

1. Open Customer Invoices (Accounts Receivable or AR) 

2. Open Vendor Invoices (Accounts Payable or AP) 

3. Closing Stock Quantity and Cost Value (Inventory) 

4. General Ledger Balances (The closing balances of all the G/L accounts. The total balance must follow the accounting equation, which is Assets = Liabilities + Equity) 

Items #1, 2, and 3 have their own sub-ledger as well as the total represented by the G/L account.  

The objective is to create the sub-ledger for customers, vendors, and stock, plus import all G/L balances. When we import the sub-ledger, it will impact the AR, AP, and Inventory Control accounts. The balances of these accounts will also be included in the G/L balance – otherwise, the accounting equation will not be balanced. This means that the balances in these control accounts may double up, as these accounts will be affected once for the sub-ledger import then a second time for the G/L balance import.  

There are two ways to work around this problem

1. Create a new G/L account as a balancing account in this method, a new G/L account is created (let’s call it a suspense account). When sub ledger balances are brought in, the balancing side is posted to this suspense account. When G/L balances are brought over, the balances in the control accounts are also sent to the suspense account, bringing the balance back to zero. 

2. Using the same control account as the balancing account in this method, the balancing entries for the sub-ledger are also sent to the same control account. This means that when the sub-ledgers are imported, the G/L balance in the relevant control G/L accounts will still be zero. The G/L balances can then be brought over as per normal. 

Both of these methods have their advantages and disadvantages.  

Method I: Create a new G/L account as a balancing account 

Let’s say that the new G/L account is called “Suspense Account.” The sub-ledger balances will be brought over in this manner: 

When bringing in the G/L balances, the AR, AP, and Inventory balances will be posted to the Suspense Account, bringing it back to zero. 

This method gives complete control and validation of the balances and is considered our best practice. If there are any reconciliation differences between sub-ledger and the G/L account balance, the balance in the suspense account will not be zero, forcing reconciliation. 

Unfortunately, the G/L account may never be used again and will just use up space in any accounts-based reporting. 

Method II: Use the same control account as the balancing account 

Using this method, this is how the entries appear: 

The balances in these control accounts will be zero and you can easily bring in G/L balances without any need to create a separate G/L account, which is of course an advantage. But the disadvantage is that extra entries (the balancing ones) are posted to the same account, making it very hard to read if you need to refer to the opening balance entries. 

We always use Method I, as it compels each client to ensure that their balances are reconciled and accurate. Thus, the risk of future issues is mitigated. 

Happy D365’ing! 

Filtering on Calculated Fields in D365 Business Central

There are many ways to access transaction history within Business Central. For instance, what if you are looking for cash transaction history for a month, a week, or even just one day? There are multiple reports you could run with date filters on the desired G/L account(s). However, if you want to see this data for a few different date ranges you’ll have to enter new dates and re-run the report each time. A quick and easy method to skip reports altogether while also allowing you to drill down to the data you’re looking for is to filter on a calculated field. Using the steps below, we’ll show you how to quickly get the data you need.

Using that cash account example, you’ll start with accessing your chart of accounts and selecting the G/L account for which you want to view history. Click on <Search> and enter the desired account. You’ll see two totals: the Net Change in account balance and the current account Balance.

If you click on Net Change now, you will see the entire transaction history for that account. However, we’re only looking for the transactions in a specific date range, so select the filter button instead.

This will bring up the filter pane. You’ll now see two filtering choices. The first is Filter list by:. The second option is the one you’re looking for – Filter totals by…

Select the second option and then click on the filter you would like to use. For this example, we’ll stick with date ranges and click on Date Filter.

Let’s say that you want to only see cash transactions for the 2019 calendar year. You can either enter the exact dates or use the formula “1/1/2019..T” (tip: T is a shortcut to signify “today” within a date field in Business Central. If you’re someone who tends to forget what date it is, this is a safe way to make sure that today’s transactions are included in the desired date range). You will see that your account balance is unchanged, but your Net Change has recalculated. You can click on this if you’d like to drill down and see the individual transactions.

You now have detailed information on only the transactions that add up to the Net Change for 2019.

If you want to change your view again and see the Net Change for only January of 2019? Enter those dates into the Date Filter and recalculate. Once again, you can click on Net Change and see a list that contains only those transactions.

Reporting tools are great but can sometimes be overkill or too slow when you’re looking for quick access to transactional data over multiple date ranges. In a pinch, filtering on a calculated field can be the tool you’re looking for.

Happy Dynamics 365'ing!

Potential Errors with Business Central Outlook Add-in

Today’s post is part of an ongoing series devoted to Business Central. In our last installment, we walked through the process of integrating Microsoft Outlook with Dynamics 365 Business Central. Today, we’ll describe what to do when you’re using the Business Central Outlook add-in and you get stuck.

You may run into a scenario in which it keeps on asking for your credentials… again and again and again. So maybe you try the web version and discover that the same thing happens there. Next, you get creative and try various browsers – still nothing! You play with the settings but again, nothing. Finally, you check to make sure the mailbox is active. It is. So, what now?

Issue: Unable to sign into the Outlook add-in

Below are the errors we have seen: ​

business central

Fortunately, the solution is pretty simple:

1. Identify the users who wish to use the Outlook add-in​.

2. Temporarily give them the Super permission set​ with a blank company field.

3. Verify the users can sign into the Outlook add-in​.

4. Once the user(s) have successfully signed into the Outlook add-in, remove the Super permission set from the user​.

That’s it! Note that this happens as the result of a bug that has already been identified and fixed. However, the fix is not scheduled to be released until Update 26. We are currently on Update 24.

Hopefully this helps. Stay tuned for more Microsoft Business Central blogs in the future as we continue our series on the topic. In the meantime, be sure to subscribe to our blog!

As always, happy Dynamics 365’ing!

Business Central Integration with Outlook

In today’s post – part of an ongoing series devoted to Business Central – we’ll delve into Dynamics 365 Business Central and its integration with Microsoft Outlook. To mimic the example we’ll be describing, note that users must have Super rights in Business Central, as well have email configured and an Exchange license assigned to them in the Microsoft 365 Admin Center.

Business Central introduces the ability to manage business interactions with customers and vendors directly in Microsoft Outlook. With the Business Central Outlook add-in, we can see financial data related to customers and vendors, as well as create and send financial documents like quotes and invoices.

To install the add-in, we need to log into Dynamics 365 Business Central, and search for Assisted Setup, as shown below:

https://www.adv-usa.com/wp-content/uploads/092518_1848_HowtoInstal1.png

Click on it to open the ASSISTED SETUP view. From this view, click the option for Set up your Business Inbox in Outlook.

https://www.adv-usa.com/wp-content/uploads/092518_1848_HowtoInstal2.png

If you have already run this in the past, you will get a popup box asking if you want to reinstall or get a newer version of the add-in. Click Yes. Note that this is especially important if you installed the add-in before the Business Central upgrade.

On the OFFICE ADD-IN SETUP page, click Next.

https://www.adv-usa.com/wp-content/uploads/092518_1848_HowtoInstal3.png

On the following page, select My Mailbox to install the add-in in your Outlook client only; select My Organization to install it for every Outlook client in your organization. Click Next.

https://www.adv-usa.com/wp-content/uploads/092518_1848_HowtoInstal4.png

On the next screen, you have the option of receiving a test e-mail. This is optional. Click Next. Click Finish.

https://www.adv-usa.com/wp-content/uploads/092518_1848_HowtoInstal5.png

You may have to restart Outlook to see the new button, but the install is done in the background, and it will eventually look like this:

https://www.adv-usa.com/wp-content/uploads/092518_1848_HowtoInstal6.png

Adding the Add-ins Manually

In some cases, the add-ins do not get added automatically to Outlook. Even if we ran the assisted setup guide on behalf of the company, Business Central might not show up in Outlook. If we experience this issue, we can add the Business Central add-ins manually.

First, we must verify that we have access to the add-ins in our Office 365 account. This is quite simply open our Outlook Web Access in a browser, and then add /owa/#path=/options/manageapps to the URL in the address bar. This opens the Manage add-ins page, where we can enable Business Central for Outlook. Then, when we navigate back to Outlook, Business Central should be available.

Similarly, in the Outlook desktop client, we can verify that Business Central is listed on the Manage Add-ins page.

Stay tuned for more blogs about Business Central and don't forget to subscribe. As always, Happy Dynamics 365’ing!

Dynamics 365 Business Central Integration with Customer Engagement, Part I of II

This is the first in a series of blogposts devoted to Dynamics 365 Business Central. In the coming weeks, we’ll cover integrating Business Central with Dynamics 365 for Customer Engagement (CE), as well as the integration with Microsoft Outlook and even some known issues and workarounds with that integration. In today’s post, we’ll start with the integration between Dynamics 365 CE and Business Central.

Prerequisites and Getting Ready

Dynamics 365 Business Central: https://businesscentral.dynamics.com

Dynamics 365 for CE: https://OrganizationName.crm%5Bx%5D.dynamics.com Note that Organization Name in the URL is the name we have selected for our application in this example. This is of course customizable and is always validated for uniqueness within the respective data center.

Open Dynamics 365 CE. Copy the URL in the address bar and share with active users.

business central

Next, open Dynamics 365 Business Central. Please keep in mind that if you have two different admin accounts for your applications, you must use two different browsers – one for each application. Alternatively, you can use the same browser, but one must go InPrivate mode for each application.

business central

Before we get started with using the apps, we need to make sure we have all the necessary setups in place. Go to https://portal.office.com and visit the Active users page:

business central

business central

On this page, we can see the active user I used to sign in and function (Cecilie Norgaard is the display name). I have created an admin user where I originally set up Dynamics 365 for CE and for Business Central – the same admin user in both applications. Also note the second user (display name is bc). More on this in a minute…

business central

If I click on it, I will be able to see Product licenses assigned to it and Roles. In this example, Cecilie Norgaard is the user; she has the role of Global administrator.

As mentioned above, I also needed to create another user, which I called bc. If I don't create two users, I will run into problems with the synchronization, and it won't work properly. This integration user cannot be the same user that enters data into CRM. Once the setup is complete, the licenses may be removed for this user.

Click Add a user and fill in the necessary fields. For Roles, assign it Global administrator.

business central

Now we've got two users: bc and our admin, Cecilie. That is all that needs to be done in the Admin Center of Office 365. Once that is done, switch back to your Dynamics 365 for Business Central application.

What Can Be Synchronized

The integration can handle a defined set of tables that are listed below.  Note that some are considered bidirectional, while some data can only be synced in one direction:

Business CentralSalesSynchronization Direction
Salesperson/PurchaseUserBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552026846.jpgSales
CustomerAccountBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546551217575.jpgSales
ContactContactBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546551217575.jpgSales
CurrencyTransaction CurrencyBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552306397.jpgSales
Unit of MeasureUnit GroupBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552306397.jpgSales
ItemProductBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546551217575.jpgSales
ResourceProductBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546551217575.jpgSales
Customer Price GroupPrice ListBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552306397.jpgSales
Sales PriceProduct Price ListBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552306397.jpgSales
OpportunityOpportunityBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546551217575.jpgSales
Sales Invoice HeaderInvoiceBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552306397.jpgSales
Sales Invoice LineInvoice ProductBusiness Centralhttps://www.erpsoftwareblog.com/wp-content/uploads/Depositphotos_223565976_s-2015-e1546552306397.jpgSales

We will discuss how to integrate BC with CE in the next post in this series. Stay tuned and happy Dynamics 365’ing!

Dynamics 365 Business Central Integration with Customer Engagement, Part II of II

This is the second in a series of blogposts devoted to Dynamics 365 Business Central. Specifically, it’s the second in two-part series on integrating Business Central with Dynamics 365 for Customer Engagement (CE). In the coming days and weeks, we’ll cover the integration with Microsoft Outlook and even some known issues and workarounds with that integration. In today’s post, we’ll finish our discussion on the integration between Dynamics 365 CE and Business Central.

Business Central Integration with Dynamics 365 for Customer Engagement

Login to Business central using your Integration user credentials:

Click on Search and type 365:

If you don’t see the Microsoft Dynamics 365 Connection Setup Page, you need to change the User Experience to Premium first.

Search for Company Information instead and click on it. Scroll down to the User Experience fast tab and change it to Premium. Refresh your browser and search once more for 365.

Click Microsoft Dynamics 365 Connection Setup > Assisted Setup.

Paste the Dynamics 365 for Sales URL and click Next.

The connection setup is to specify the user for the synchronization between the two services. Enter the integration user and click Finish.

The next window will prompt you to enter the admin user. Do so and click OK. The system will work on getting this connection ready. You can choose to map Salespeople at this point, or you can leave it for later.

Everything is set to this window; this is where we will see Dynamics 365 for CE URL, integration user, the active scheduled synchronization jobs, etc. Additionally, you can find some other important buttons in the toolbar, like Mapping and Synchronization. Before you go further, click Test Connection. You should receive a success message.

A few important notes about Mappings.

On the INTEGRATION TABLE MAPPINGS page, you can see the types of integration Synch. Job Queue Entries that are available. You cannot edit or create new. If you scroll to the right, you will see a column named SYNCH. ONLY COUPLED RECORDS, which matches data in both applications. In simple terms, if you have an Account in Dynamics 365 for Sales and the same Account exists in Dynamics 365 for Business Central, you probably want to couple them together to avoid having duplicates and mismatches.

When this box is checked, it means you want to couple/synchronize only those that are matched. In other words, if you have an Account in one application and not in the other, it won't synch because It synchs only coupled records.

In Synch. Job Queue Entries, if integrations are scheduled to be run, the status will change to Ready.

To the far right, there is a NO. OF MINUTES BETWEEN RUNS column, which you can change in two ways:

Go back to the INTEGRATION TABLE MAPPINGS page. Note that Contact and Customer are both directional. It means they can be sent and received in both applications.

Let’s start with customers first:

In the integration mapping for Customer, there is one condition:

The system will not take all accounts from CRM; instead, it will take only active ones with Relationship Type set to Customer. Additionally, if you want to create customers in Dynamics 365 for Business Central automatically, you can use Customer Templates:

For this blog, we’ll just go ahead and create a new one. Search for Set Up Customer/Vendor/Item Templates.

In this example, we open the Business-to-Business Customer (Bank) template and click New.

Press Esc and you can see the new one in the list:

Go back to the INTEGRATION TABLE MAPPINGS page in the TABLE CONFIG TEMPLATE CODE field, as shown:

Additionally, in this scenario, we don't have customers in both applications that we want to couple. Therefore, we uncheck the box, as shown:

Now, let's check what we have in both applications before we activate synchronization:

Check Dynamics 365 for CE > Accounts and Dynamics 365 for Business Central > Customer. Now Synch. Job Queue Entries:

Set It to Ready, wait for a few moments and check the JOB QUEUE LOG ENTRIES page:

If the job has run successfully, you can check the customer list again. You should be able to see new customers coming through.

There you have it! We hope this was helpful. Don’t forget to subscribe to our blog for more!

Happy Dynamics 365’ing!