Small and medium business | Business Central, N...

Bulk add Dimensions & Values to Customers

(1) Share

Report

Posted on by PC-29042243-0

Hello fine forum people :)

USE CASE:

Client coming from QuickBooks has an Income Statement Report she's called /P&L/.

Said Quickbooks report can be filtered by Customer, resulting in a P&L GL Accounts Report by Customer.

Before looking at the below proposed solution, is there an easy way to accommodate this use case?

PROPOSED SOLUTION: (This needs input as I'm unclear if it's the way to address the use case, and requires help for initial setup and on-going maintenance)

Create Dimension called /Customers/.
Copy Paste Customers Code and Descriptions to create new Dimension Customers Dimension values. (Creating a 1 to 1 Customer ID/Name to Customer Dimension Value list)
Add newly created Dimension Customers to General Ledger Setup Shortcut Dimension X.
Create new Analysis View called CUST VIEW (Customers View) by Account Source: G/L Account and Date Compression: Period and assign new Customers Dimension to Analysis View Dimension Code 1
Create New Income Statement Report and add Analysis View CUST VIEW/ to the row analysis view name column.

1ST SETUP ISSUE: How to efficiently add Dimension Codes and Dimension Values to each Customer Card? (There's over 1000 Customers)

2ND SETUP ISSUE: How to maintain adding new Dimension code/values when creating new Customer Cards via automation vs manual (where setup user may forget this step)?

EG:

Manually create new customer card = /Widgets R Us/
Auto create new Dimension Customer Dimension value called /Widgest R Us/
Auto assign Dimension Code Customers and Dimension called Value /Widgets R Us/ to newly created Customer Card for Widgets R Us

Barring the ISSUES 1 & 2 above that I encountered and foresee needing resolution, the above achieves a BC Income Statement report that can be filtered by Customer.

Is there a better/easier way?

All and any feedback's appreciated.

Cheers~Peter

Categories:

Reports and Business Intelligence

Financial Management

Integration to Power BI, Power Apps, Power Automate and Dataverse

All responses (3)

Answers (0)

Suggested answer

Yi Yong 228 on at

Like (0)

Report

Bulk add Dimensions & Values to Customers

Hello,

I don't see a need to have a P&L GL Accounts Report by Customer because the GL Accounts that are related to customer posting will be just Sales and COGS.

You are better off using 'Sales Analysis Reports' or 'Sales Analysis Views' because you will not need to depend on dimension and work on the extra steps of creating dimension value code and assigning to the customer.

However, if you are determined to use the 'Financial Reports' for this purpose.

The Proposal Solution you wrote is correct.

If you are migrating from QuickBooks to Business Central, you can assign the dimension to the customer using 'Configuration Package'. You will need to create the thousand dimensions first.

On the 'General Ledger Setup', if the customer dimension is added on Global 1 or Global 2 then it will be automatically shown on the 'Configuration Package' otherwise you will need to add them by checking 'Dimensions as Columns'.

You can't auto create nor auto assign the dimension to the customer by standard, this has to be done through customization.
PC-29042243-0 37 on at

Like (0)

Report

Bulk add Dimensions & Values to Customers

Hi Yun Zhu, thanks for your prompt suggestions.

The initial screenshot seems to highlight which table I can leverage to bulk create/load Dimension Codes Values via Config Package processes.

This would result in all Customers also existing as Dimension Code values for the Dimension called Customers. (great, understood, already beyond this step)

The second screenshot suggests I use a "Customer Template" to apply predefined Dimensions Codes/values when creating a customer.

This doesn't work as the requirements call for the new customer to exist as new Dimension value of the same customer name, and it's a 1 to 1 relationship, not a many to 1 as a template would assist with.

I'm likely missing something with your replies as at face value, neither suggestions meet the use case goal.

Help?

Cheers~Peter
Suggested answer

YUN ZHU 62,477 Moderator on at

Like (1)

Report

Bulk add Dimensions & Values to Customers

Hi, for the first question, you can use Configuration Package. Table: Default Dimension (352)

More details: https://yzhums.com/4286/

For second/third question, if it is a fixed dimension value, it can be done using the standard method.

Dynamics 365 Business Central: Set up default dimensions for specific accounts/tables (Account Type Default Dimensions)

https://yzhums.com/45670/

Or, Customer Template

Hope this can give you some hints.

Thanks.

ZHU