Add, Add by Dimension, Add by Filter, Add Related Entries, Analysis Views, BC18, Business Central, Change, Combinations, Completed, Correction, Correction Setup, Delete, Dimension, Dimension Corrections, Draft, Dynamics 365, Functional Tips, G/L Entries, G/L Register, General Ledger Entries, History, How To, Information, Modify, Posted General Journal, Reversing, Run, Schedule, Select Manually, Tip & Tricks, Trace, Undo, Updating, Validate, What's New

How can we Correct Dimension in Microsoft Dynamics 365 Business Central?

You can do correction by reversing the journal or creating a credit memo for the document, which will be time consuming and can attract to even more errors.

Now you can correct dimensions for general ledger (G/L) entries to ensure your financial reports is providing you with accurate insights. For G/L entries, you can now

  • change the dimension values
  • add new dimensions
  • remove wrong ones

You can also trace a history of the corrections that you have done and can undo corrections in case you had made a mistake in the corrections itself.

Before you continue let me remind you:

Although the Dimension Correction feature only updates general ledger entries, still it can be helpful in many cases with creating accurate financial reports.

You must decide when to use the corrections because when you perform correction, it will result in differences between the dimensions in finance and sub-ledgers. The earlier you decide on what corrections to use, the less manual corrections you’ll need to do in the future.

I am using BC18 Demo database.

 How to run the Dimension Correction

General Ledger Entries. You can change the dimensions for single or multiple entries just by selecting the entries.

G/L Register. You can change the dimension per batch posting. By using “From Entry No.”, “To Entry No.” as a filter for General Ledger Entries.

Posted General Journal. You can use “Document No.” and “Posting Date” as filters for General Ledger Entries.

Search for Dimension Corrections Page.

Draft Dimension Correction Screen:

Depending upon from where you Launched the Dimension Correction, it may come with pre-filled or as blank screen.

You have several options to work with dimensions:

  • You can Add New Dimensions
  • You can Remove existing Dimensions
  • You can Change the existing Dimensions

For Selecting Entries to which above changes applies you have different options to work with.

  • Add related entries. This requires at least 1 G/L Entry already to be selected, and then it adds all other G/L Entries with the same “Transaction No”.
  • Select Manually. You can manually select in the G/L Entries by just choosing them.
  • Add by Filter. Here you have all freedom of choice, how to filter your G/L Entries for the dimension corrections.
  • Add by Dimension. This can be useful if you want to change the dimension value Sales to Admin for all G/L entries. So, you find all G/L Entries with the Dimension Sales.

You can omit/remove selected entries, or change selection criteria for your entries to be applied with changes.

Updating Dimension Values

Above we create a Dimension Correction, the Status is initially DRAFT, it means the G/L Entries Dimension is NOT yet updated.

Once you are done in the Dimension change to move forward you have two option to go thru:

  • Click the Validate dimension change then click RUN.
  • Click RUN.

Validate change

This will check if it’s possible to make the dimension changes you specified.

  • Check Dimension Correction Setup.
  • Check Dimension Combinations.

Once the validation run successfully you can see it in the Validation status.

You can choose to run the job immediately or schedule in case you are operating on larger data set.

Next you can now use the Run action button. This will Apply changes to the General Ledger Entry Dimensions.

How to trace the correction

General Ledger Entries. You can see all the dimension corrections that you’ve done for the selected entries.

Search the Dimension Corrections. You can see all the Dimension corrections that you’ve done even the ongoing dimension corrections will be listed here.

Undo a Correction


In the dimension correction page just click the UNDO. This will be executed as a Job. The user will have to choose, how he wants to apply changes: running the job immediately or schedule it.

It is recommended to run the schedule if you are working with larger data set, to avoid the table locking during posting of other transactions.

Once the Job execution completes, you will see the status change to UNDO COMPLETED.

How to Update the Analysis Views

From the Dimension Correction page, you can also update you’re Analysis Views by clicking the Update Analysis Views button. This will be executed as a Job. You will have to choose, how you want to apply changes: running the job immediately or schedule it.

It is recommended to run the schedule if you are working with larger dataset, to avoid the table locking during posting of other transactions.

Hope you enjoyed the information. Will come with more similar information in my next posts. Till then keep exploring, learning and sharing your knowledge with others.

Remain safe, take care of your loved ones, put your mask, maintain safe distance and don’t forget to get vaccinated.

AL, Array, BC14, BC17, Business Central, Collection, Development Tips, Dictionary, Dynamics 365, How To, Information, List, Modern Development Tool, on-premises, Tip & Tricks, Visual Studio Code, Wave 1, Wave 2, Web Client

Working with Collections

Today we will learn three types of collections supported by AL.

A collection is a complex type that contains multiple values in one variable.

You can’t have values with different types in the same collection. For example, you can’t add date values in a collection that only allows integer values.

The three types of collections that AL supports are:

  • Array
  • List
  • Dictionary

We will discuss about each type of collections in this post with examples.

Let’s start with most familiar collection we have used with old versions of Navision too, yes you are right, I am talking about Arrays.

Arrays

Arrays are complex variables that contain a group of values with the same data type.

An array holds multiple values, and these values are stored in the elements of the array. You can access these values by using the index, which can also be a value that is stored in another variable. With this design, you can create a loop where you increment a certain variable to loop through every element in an array.

By using the Dimension property, you can define how many dimensions that the array will hold.

When creating a variable of an array data type, you first need to define how many elements that you’ll have in the array. The most commonly used array is the one-dimensional array, which is a list of elements with the same data type.

You can represent an array as a row of values.

To create an array, use the following code:

SalesAmount: array[10] of Integer;

To access an element in an array, use the array element syntax:

SalesAmount[5] := 0;

Unlike other programming languages array index don’t starts with 0 rather with 1. In above example first element will be 1 and last 10.

Having only one element between the square brackets indicates that you are using a one-dimensional array. If you want to have a multi-dimensional array, use a comma-separated list between the brackets, as follows:

SalesAmount: array[6,9] of Integer;

To access an element in an array, use the array element syntax:

SalesAmount[5,3] := 0;

Lists

The List data type can be compared with an array. The List type can only be used with fundamental types and represents a strongly typed list of values that can be accessed by index.

Therefore, you can have a List type of [Integer], but you cannot have a List type of [Blob].

List data type doesn’t require you to define how many elements you want to store up front (while an Array data type does).

The List data type has some methods that are used frequently. The methods that are available for a List data type will discuss in a later post.

To create a list, use the following code:

CustomerNames: List of [Text];

To access an element in a list, use the following methods:

To store/add values to list

CustomerNames.Add(‘KSD Consultancy’);

CustomerNames.Add(‘Microsoft India’);

CustomerNames.Add(‘Ashwini Tripathi’);

To retrive values from list

CustomerNames.Get(1);

Dictionary

The Dictionary data type represents a collection of keys and values.

Every key that you create in this dictionary must be unique. The main benefit is that you can immediately get the value for a specific key.

The value can be a type, but it can also be a List or another Dictionary data type.

Blow code sequence will give you idea how to use dictionary data type:

//Declaring List

CustomerNamesIN: List of [Text];

CustomerNamesUS: List of [Text];

CustomerNamesCA: List of [Text];

//Declaring Dictionary

CountryWiseCustomer: Dictionary of [Code[20], List of [Text]];

//Assigning values to List

CustomerNamesIN.Add(‘KSD Consultancy’);

CustomerNamesIN.Add(‘Microsoft India’);

CustomerNamesIn.Add(‘Ashwini Tripathi’);

CustomerNamesUS.Add(‘Paul’);

CustomerNamesUS.Add(‘Linda’);

CustomerNamesCA.Add(‘Eddy’);

CustomerNamesCA.Add(‘Mark’);

//Assigning values to Dictionary

CountryWiseCustomer.Add(‘IN’,CustomerNamesIN);

CountryWiseCustomer.Add(‘US’,CustomerNamesUS);

CountryWiseCustomer.Add(‘CA’,CustomerNamesCA);

//Retrieving value from Dictionary

CountryWiseCustomer.Get(‘IN’).Get(1);

Here is the complete code:

Created new codeunit and declared variables & procedures to manipulate values in Collections.

Added Code to call procedures defined in codeunit, to assign and retrieve values from collections.

Now its time to check output of above code.

Hope you get idea how to work with Collections, you may find more posts in coming days where we may discuss about methods available for collection.

How To, PowerBI.com, Tip & Tricks

How to add Video Tile on Power BI Dashboard

Previously, a Video Tile released by BI supports YouTube videos. Video tiles allow you to customize your dashboard and add a bit of personal look.

Now the video tile supports Vimeo videos too.

Add a video tile by selecting Add widget from the top right of the dashboard.

PBI Video - 1

Select Video from Add Tile window.

PBI Video - 2

Press Next Button.

PBI Video - 3

Provide your Title and Subtitle for the Video Tile.

Provide your YouTube or Vimeo video URL.

If required provide custom Link and other properties.

Select Apply to complete your action.

Vimeo URLs come in several different formats this supports most, but not all of these formats. In case your URL doesn’t works try for next format.

I have posted several posts related to Power BI, you can find them using below Links.

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel

Microsoft Power BI – Part – X

Analyse in Excel (preview)

 

 

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add a relationship using Diagram View in Power Pivot

Open the Workbook which we prepared in the last exercise in our previous post.

Add Excel Sheet/Table to the PowerPivot Data Model

  • In the Power Pivot window, in the View section, click Diagram View.
  • Use the slide bar to resize the diagram so that you can see all objects in the diagram. Rearrange the tables by dragging their title bar, so they’re visible and positioned next to one another. Notice that all tables are unrelated to each other, we will create relationship among these tables.

After adding relationship the Diagram will Look as below.

ExcelData-6

  • Let start creating the relationship.
  • Sports->SportID click and drag to Disciplines->SportID and release. You will see the relation is built between these two tables on SportID.

Similarly follow to create other relations too as shown in below image.
ExcelData-5

You notice that both the Medals table and the Events table have a field called DisciplineEvent. Upon further inspection, you determine that the DisciplineEvent field in the Events table consists of unique, non-repeated values.

The DisciplineEvent field represents a unique combination of each Discipline and Event. In the Medals table, however, the DisciplineEvent field repeats many times. That makes sense, because each Discipline+Event combination results in three awarded medals (gold, silver, bronze), which are awarded for each Olympics Edition the Event is held. So the relationship between those tables is one (one unique Discipline+Event entry in the Disciplines table) to many (multiple entries for each Discipline+Event value).

  • Create a relationship between the Medals table and the Events table. While in Diagram View, drag the DisciplineEvent field from the Events table to the DisciplineEvent field in Medals. A line appears between them, indicating a relationship has been established.
  • Click the line that connects Events and Medals. The highlighted fields define the relationship, as shown in the above screen.
  • To connect Hosts to the Data Model, we need a field with values that uniquely identify each row in the Hosts table. Then we can search our Data Model to see if that same data exists in another table. Looking in Diagram View doesn’t allow us to do this. With Hosts selected, switch back to Data View.
  • After examining the columns, we realize that Hosts doesn’t have a column of unique values. (Assume EditionID Field is not available in the Host & Medal Table, in my case I have already added this Field in my workbook I shared). In such case we’ll have to create it using a calculated column, and Data Analysis Expressions (DAX).

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so the next post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

We will see how to work with calculated fields in our next upcoming post.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add Excel Sheet/Table to the PowerPivot Data Model

Download the Excel file from the link provided in previous post.

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Open the Workbook:

ExcelData-3

Select sheet one by one and Add to Data model of PowerPivot.

ExcelData-4

Make sure all Table/Sheet is added to Data Model of PowerPivot.

I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.

You can find the link on the Menu of my blogsite too to access the shared files.

I will be using this file for couple of walkthrough in my upcoming posts.

Development Tips, Excel, How To, PowerPivot, Report

Add Slicers to PivotTables in PowerPivot

Recall from my earlier post Creating My First Report using PowerPivot in which we created a Items Vs Customer Sales matrix report.

I am going to use same report to demonstrate how we can add slicer to this report.

Slicers are one-click filtering controls that narrow the portion of a data set shown in PivotTables and PivotCharts. Slicers can be used in both Microsoft Excel workbooks and PowerPivot workbooks, to interactively filter and analyze data.
PowerPivot-30

Open the report we created in our previous post.

From Insert Tab select Slicer in the ribbon.
PowerPivot-31

Choose the source Connection/Data model and respond Open.

Select the Customer Table.

Select the Country/Region Code field and OK.
PowerPivot-32

A Slicer will be added to the sheet.

Re-size to fit and drag the Slicer to position at desired location on sheet.

You can give desired Caption to your Slicer by editing the Slicer Caption.

Most important is to select the pivot table on which this Slicer will operate.

Select Report Connection, and from preceding window select the PivotTable.
PowerPivot-33
Above is the screenshot with applied filter on Country/Region Code = DE.

Stay tuned to know more options, I will come up with more details in my upcoming posts.

Till then keep practicing.

Development Tips, Excel, How To, Office Integration, PowerPivot, Report

How to add Filter for data retrieval in PowerPivot Data model.

Recall from previous posts we have added Item Ledger Entry Table to our Data Model of PowerPivot.

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

My requirement is to retrieve only records related to Sales.

We will apply filter on Entry Type field to value [1]-[Sales] this way Item Leger Entry Sheet will have only Sales Data.

To do this we will add this filter to the query used for data retrieval.
PowerPivot-15

Select the Sheet for table – Item Ledger Entry from bottom.

Click on Design Tab on Top.

Now From Ribbon choose Table Properties.

PowerPivot-16

Select Query Editor from Switch to.

Add the Filter to your query.

Save your Query.

PowerPivot-17

From Home Tab, Select Refresh from Ribbon.

On Success completion Close the window.

Now your Item Ledger Entry is populated with Fresh data as per the modified Query.

I.e: All Entries which have Entry Type = 1 [Sales].

I will come up with more details in my upcoming post, till then keep practicing and stay tuned for future updates & posts on this topic.

Development Tips, Excel, How To, Office Integration, Report

Add relationships to Data Model in PowerPivot

You can systematically create table relationships for all new tables that you import. If you’re sharing the workbook with colleagues, having predefined relationships will be appreciated if they don’t know the data as well as you do.

When creating relationships manually, you will work with two tables at a time.

For each table, you’ll choose columns that tell Excel how to look up/ join related rows in another table.

Recall from my previous posts I will be continuing the example from using same Data Model

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

With your Data model sheet opened
PowerPivot-14
Click on Diagram View in Upper Right Side as highlighted in above screenshot.

Window will switch to Diagram View as shown in above screenshot, showing all the tables available in the Data model.

Click No_ in Customer Table and Drag to Item Ledger Entry Table and release the mouse button pointing to Source No_ field, you will see the Link is Established between these two tables showing the Link via Arrow line.

Repeat above step again between Item->No_ and Item Ledger Entry -> Item No_ fields.

Now we have Established connection between these three tables Customer, Item & Item Ledger Entry.

I will come up with more option on this topic in my next post, till then start practicing and stay tuned.

How To, Instalation & Configuration

How to: Add Users for ADCS

You can add any user as a user of an Automated Data Capture System (ADCS). When you do this, the user must also provide a password. Optionally, you can also provide a connection that identifies the ADCS user as a warehouse employee. The ADCS user password can be different from the Windows logon password of the user.

To add an ADCS user

In the Search box, enter ADCS Users, and then choose the related link.

On the Home tab, choose New to add a new user.

In the Name field, enter a name for the user. The name cannot contain more than 20 characters, including spaces.

In the Password field, enter a password. The password is masked.
ADCS-5
To indicate that a warehouse employee is an ADCS user

In the Search box, enter Warehouse Employees, and then choose the related link.

If needed, add a new warehouse employee

On the Home tab, in the Manage group, choose Edit List.

Select a warehouse employee from the list. In the ADCS User field, choose the drop-down arrow, and then select the name of an ADCS user from the list.

Note : The default warehouse for the employee should be one that uses ADCS
ADCS-6

Report

Add Fields from a Report Dataset to a Word Report Layout

A report dataset can consist of fields that display labels, data, and images. This topic describes procedure for adding fields of a report dataset to an existing Word layout of a report. You add fields by using the Word custom XML part for the report and adding content controls that map to the fields of the report dataset.

To display the Custom XML part for the Report in Word

  1. Open the Word report layout document in Word for editing.
  2. Show the Developer tab in the ribbon of Word.

By default, the Developer tab is not shown in the ribbon.

  1. On the Developer tab, choose XML Mapping Pane.
  2. In the XML Mapping pane, in the Custom XML Part list, choose the XML part for Microsoft Dynamics NAV report. The name of the XML part has the following format:

urn:microsoft-dynamics-nav/reports/report_name/ID

report_name is the name that is assigned to the report as specified by the report’s Name Property.

ID is the identification number of the report.
XMLDataSet
After you select the XML part, the XML Mapping pane displays the labels and field controls that are available for the report.

To add a label or data field

  1. Place your pointer in the document where you want to add the control.
  2. In the XML Mapping pane, right-click the control that you want to add, choose Insert Content Control, and then choose Plain Text.

AddDataField

Note

You cannot add a field by manually typing the dataset field name in the content control. You must use the XML Mapping pane to map the fields.

To add repeating rows of data fields to create a list

  1. If not already existing, add a table row that includes a column for each field that you want repeated.

This row will act as a placeholder for the repeating fields.

  1. Select the whole row.
  2. In the XML Mapping pane, right-click the control that corresponds to the report data item that contains the fields that you want repeated, choose Insert Content Control, and then choose Repeating.

AddRepeatingField

  1. Add the repeating fields to the row as follows:
    1. Place your pointer in a column.
    2. In the XML Mapping pane, right-click the control that you want to add, choose Insert Content Control, and then choose Plain Text.

InsertContentField

  1. For each field, repeat steps a and b.