Dynamics - Change custom financial dimension ID in system

While we were fetching data for reporting purpose in power BI, we were facing an issue because one of the custom dimension contained & symbol in its name. So in order to get the report in power BI we had to change the dimension name and replace '&' with 'And'.

For applying this change directly in database [Because we cannot change it from front end] we went through each table which have or may have records relavent to financial dimensions.

In doing so, I found below table in which I had to change the name of the dimension.

  1. DimensionAttributeValueCombination
  2. DimensionAttributeLevelValue
  3. DimensionAttributeValueSetItem
  4. GeneralJournalAccountEntry
  5. DirPartyTable

We executed following query to achieve this.


Update DIMENSIONATTRIBUTELEVELVALUE
Set    DISPLAYVALUE = replace(DISPLAYVALUE, '&', 'And')
WHERE DISPLAYVALUE LIKE '%&%'

Update DIMENSIONATTRIBUTEVALUESETITEM
Set    DISPLAYVALUE = replace(DISPLAYVALUE, '&', 'And')
WHERE DISPLAYVALUE LIKE '%&%'

Update GENERALJOURNALACCOUNTENTRY
Set    LEDGERACCOUNT = replace(LEDGERACCOUNT, '&', 'And')
WHERE LEDGERACCOUNT LIKE '%&%'

Update DIRPARTYTABLE
Set    OMOPERATINGUNITNUMBER = replace(OMOPERATINGUNITNUMBER, '&', 'And')
where OMOPERATINGUNITNUMBER like '%&%'

In case you've used the same dimension in account structure configuration, manually change the dimension there otherwise it'll not be available in look up of segmented value in any form.

To know more on how ERP can help business book a free consultation session at Dynamics Stream Dubai at www.dynamicsstream.com
Follow us on LinkedIn for latest updates on Microsoft ecosystem - https://www.linkedin.com/company/dynamicsstream.com

Comments

Post a Comment

Popular posts from this blog

🚀 Export All D365 F&O Custom Models with a Single Batch File

  In Microsoft Dynamics 365 Finance and Operations (D365 F&O), managing and exporting models can be a repetitive task, especially when you're dealing with a large number of custom models. To streamline this process, you can automate model exports using a simple batch file. In this blog, I’ll walk you through how to create a .bat file that exports all your custom models in one go—without needing to execute each command manually. This is especially helpful during regular backups or before code deployments. 🧠 Why Use a Batch File? A batch file allows you to: Export multiple models in one go Create backups with date-based folders automatically Reduce human error and save time Reuse and modify easily 📂 Folder Structure Assumption In this example, we're assuming the following: D365 metadata is located at K:\AosService\PackagesLocalDirectory ModelUtil.exe is located in K:\AosService\PackagesLocalDirectory\bin You want to store exported models in  ...
Read more

SQL stored procedure to get Item's on hand on specified date

One of my client needed to have an inventory data text file generated directly from SQL query, In which there was a need have on hand inventory of items on specified date but not on inventory dimensions. So I went through system's class InventOnhand which is responsible for getting item's on hand on provided date and replicate the same logic to get item's on hand without considering inventory dimensions but a few tweaking will be required to get on hand on different dimensions. Here is the SP which require item number and date to get its on hand. USE [MicrosoftDynamicsAX] GO /****** Object:  StoredProcedure [dbo].[FG_ItemOnHand]    Script Date: 2/5/2018 10:56:49 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[FG_ItemOnHand] @ItemId nvarchar(20), @AsOfDate DATE, @OnHand DECIMAL(16, 2) OUTPUT AS DECLARE @Posted DECIMAL(16, 2), @Received DECIMAL(16, 2), @Deducted DECIMAL(16, 2), @Picked DECIMAL(16, 2), @Regi...
Read more