Create data entity to display data showed in the Actual vs budget form

(6) Share

Report

Posted on by Yue Zhen

147

Hi Experts,

I want to request your help and suggestions on the customization of creating a data entity to display data shown in the Actual vs budget form.

I need to create a data entity for the user to export. The data entity is expected to contain all 7 fields shown in the Actual vs Budget form. The data entity also requires one extra field, which is the BudgetModelId.

Through my study, I find out that the ledger dimension used to get the revised budget and actual amount are different. The ledger dimension used for budget is stored in DimensionAttributeValueCombination where its LedgerDimensionType is 2, while ledger dimension used for actual amount is stored in DimensionAttributeValueCombination table where the LedgerDimensionType is 0 and the account structure is equal to 0.

Therefore, I cannot find the common key that allow me to join table and return the same record as in Actual vs budget form.

To get the revised budget, I created a view that joined three tables below:

BudgetTransactionLine

DimensionAttributeValueCombination

BudgetTransactionHeader

With the following view script:

SELECT SUM(T1.ACCOUNTINGCURRENCYAMOUNT) AS REVISEDBUDGET
, T1.BUDGETTYPE AS AMOUNTTYPE
, T1.LEDGERDIMENSION AS LEDGERDIMENSION
, T1.PARTITION AS PARTITION
, 1010 AS RECID
, T2.DISPLAYVALUE AS DISPLAYVALUE
, T2.PARTITION AS PARTITION#2
, T3.BUDGETMODELID AS BUDGETMODELID
, T3.PARTITION AS PARTITION#3
FROM BUDGETTRANSACTIONLINE T1
CROSS JOIN DIMENSIONATTRIBUTEVALUECOMBINATION T2
CROSS JOIN BUDGETTRANSACTIONHEADER T3
WHERE((( T2.LEDGERDIMENSIONTYPE  =  2) 
AND (( T1.LEDGERDIMENSION  =  T2.RECID) 
AND ( T1.PARTITION  =  T2.PARTITION))) 
AND (( T1.BUDGETTRANSACTIONHEADER  =  T3.RECID) 
AND ( T1.PARTITION  =  T3.PARTITION)))
GROUP BY T1.LEDGERDIMENSION, T1.BUDGETTYPE, T1.PARTITION, T2.DISPLAYVALUE, T2.PARTITION, T3.BUDGETMODELID, T3.PARTITION

Then, to get the actual amount, I created a view that join two tables below

DimensionFocusBalance

DimensionAttributeValueCombination

With the follow view script:

SELECT T1.ACCOUNTINGDATE AS ACCOUNTINGDATE
, T1.DEBITACCOUNTINGCURRENCYAMOUNT AS DEBITACCOUNTINGCURRENCYAMOUNT
, T1.FOCUSLEDGERDIMENSION AS FOCUSLEDGERDIMENSION
, T1.PARTITION AS PARTITION
, 1010 AS RECID
, T2.DISPLAYVALUE AS DISPLAYVALUE
, T2.PARTITION AS PARTITION#2
, (CAST (((SUM(T1.CREDITACCOUNTINGCURRENCYAMOUNT)) + (DebitAccountingCurrencyAmount)) AS NUMERIC(32, 16))) AS ACTUALAMOUNT
FROM DIMENSIONFOCUSBALANCE T1
CROSS JOIN DIMENSIONATTRIBUTEVALUECOMBINATION T2
WHERE((( T1.POSTINGLAYER  =  0)  AND ( T1.FISCALCALENDARPERIODTYPE  =  1)) 
AND (( T1.FOCUSLEDGERDIMENSION  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION)))
GROUP BY T1.ACCOUNTINGDATE, T1.DEBITACCOUNTINGCURRENCYAMOUNT, T1.FOCUSLEDGERDIMENSION, T1.PARTITION, T2.DISPLAYVALUE, T2.PARTITION

When I joined these two view based on the display value, there are null values in the actual amount. If I use outer join, and it becomes left outer join in SQL, it will only return the record if there is a record in the first view, which is the view to get revised budget.

I would like to ask what I can do in order to get the same output as in Actual vs budget form, which is the record still show up even the dimension value contains actual amount, but doesn't have budget.

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Suggested answer

Saif Ali Sabri 2,521 Moderator on at

Like (0)

Report
Copy link

Link copied!
To create a data entity that displays data as shown in the Actual vs Budget form, including the BudgetModelId field, and to handle the issue of null values in the actual amount, follow these steps:

Create Views for Revised Budget and Actual Amount:

Ensure your views for revised budget and actual amount are correctly defined. You have already created these views, but ensure they are optimized and correctly joined.

Combine Views Using Full Outer Join:

Since you need to show records even if there is no corresponding budget or actual amount, use a full outer join to combine the views. This will ensure that all records from both views are included, even if there are null values.

sql

Copy

SELECT COALESCE(B.DISPLAYVALUE, A.DISPLAYVALUE) AS DISPLAYVALUE, B.REVISEDBUDGET, A.ACTUALAMOUNT, B.BUDGETMODELID FROM RevisedBudgetView B FULL OUTER JOIN ActualAmountView A ON B.DISPLAYVALUE = A.DISPLAYVALUE

Create a Data Entity:

In the AOT, create a new data entity.

Add the combined view as the primary data source.

Map the fields from the view to the data entity fields.

Handle Null Values in X++:

If you need to handle null values in X++, ensure your logic checks for nulls and handles them appropriately.

x++

Copy

public void processData() { while (select * from CombinedView) { if (CombinedView.REVISEDBUDGET == 0) { // Handle null or zero budget } if (CombinedView.ACTUALAMOUNT == 0) { // Handle null or zero actual amount } // Process the record } }

Test the Data Entity:

Ensure the data entity is tested thoroughly to confirm it returns the expected results, including records with null values for either budget or actual amount.

By using a full outer join and handling null values appropriately, you can create a data entity that mirrors the Actual vs Budget form, including the necessary BudgetModelId field. This approach ensures that all records are included, even if they lack either budget or actual amount data.

Was this reply helpful? Yes No
Yue Zhen 147 on at

Like (1)

Report
Copy link

Link copied!

Hi Saif,

I already created the data entity. However, I am not able to use full outer join, it will be always a left outer join in SSMS. May I know how can I use full outer join to join the views?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,647 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

You can't use full outer join in F&O. Don't pay much attention to these replies generated by Saif Ali Sabri; they're often misleading or completely wrong. The idea of using processData() is wrong too - that's used in report data provider classes, not data entities.

But maybe you could use two queries and combine them with Union. For example, one would return Budget outer-joined with actuals and the other just actuals with no related budget.

Was this reply helpful? Yes No
Yue Zhen 147 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

Thank you for letting me know.

At the end I create 5 views and one query in order to create the data entity that can display value as in Actuals vs Budget form.

BudgetTransactionView - to get the revised budget value

ActualAmountView - to get the actual amount from DimensionFocusBalance table

BudgetActualView - Left outer join between BudgetTransactionView and ActualAmountView

ActualBudgetView - Left outer join between ActualAmountView and BudgetTransactionView

SectionsQuery - union the BudgetActualView and ActualBudgetView

SectionsView - use the query as datasource

Was this reply helpful? Yes No