Microsoft Dynamics AX/365

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. DimensionAttributeValueCombination DimensionAttributeLevelValue DimensionAttributeValueSetItem GeneralJournalAccountEntry DirPartyTable We executed following query to achieve this. Update DIMENSIONATTRIBUTELEVELVALUE Set    DISPLAYVALUE = replace(DISPLAYVALUE, '&', 'And') WHERE DISPLAYVALUE LIKE '%&%' Update DIMENSIONATTRIBUTEVALUESETITEM Set    DISPLAYVALUE = replace(DISPLAYVALU...

As we all know that we don't have the access to the database and there are times where we need to create update or delete a record directly from database. So, In order to get this thing done. I created a form from which I can directly execute the query. It can be enhanced but just to give you the idea how to do it.  Here is the form design. Set QueryControl's allow declaration property to YES. Override ExecuteBtn's click event and write the following code. List            queryList = new List(Types::String); ListIterator    iterator; str             sqlQuery; DialogButton    diagBut; str             strMessage = "Are you sure you want to execute this query?"; str             strTitle = "Execute" ; super(); diagBut = Box::yesNo(strMessage, DialogButton::No,  strTitle); if (diagBut == DialogButton::...

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...