Microsoft Dynamics AX (Archived)

SSRS: Journal > Lines: How Can I Get The Tax Main Account and Tax Account Name For The Line?

(0) Share

Report

Posted on by Momochi

1,044

Hello awesome community! I'm new to Ax and its development still training and I seek your help if you please! :)

(Excuse my noob Ax langauge in describing)

First Location:

General Ledger > Journals > General Journals > Lines

I'm developing a report about a payment voucher, I have multiple lines, some lines have a "Sales Tax Group".

What I need to get is the tax "Main Account" Number and Its "Account Name"

Here is the a screenshot that shows the values I want to get:

7268.Capture-3.PNG

1348.capture-5.PNG

The above picture shows the 2 data I need, the "Main Account" and next to it the "Name"

I have this select statement

        select * from _mainaccount join
        TaxLedgerAccountGroup  join
        DimensionAttributeValuecombination
        where 
        TaxLedgerAccountGroup.TaxOutgoingLedgerDimension == DimensionAttributeValuecombination.RecId &&
        DimensionAttributeValuecombination.MainAccount == _MainAccount.RecId ;

         VatMainAccount = _mainaccount.MainAccountId;
         Vatmainaccountname = _mainaccount.Name;

Because i'm a noob, I don't know what should I change in the above "Select" statement to make it bring the correct main account and name related to the line.

What should I modify in it to make it bring the "Main Account" and "Name" related to the current line in my query?

Thanks in Advance!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Momochi 1,044 on at

Like (0)

Report
Copy link

Link copied!

The select statement is brining me a main account and a name but not the correct ones, not the ones related to the current line. it always brings me the same main account and name on everyline. How can I modify the select statement so it can bring me the related data to the current line?

Was this reply helpful? Yes No
Momochi 1,044 on at

Like (0)

Report
Copy link

Link copied!

I think what should be added is the LedgerJournalTrans.LedgerDimension....so it can help the DimensionAttributeValuecombination.RecId to bring the correct record....but I don't know how...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Momochi,

is the below image that you want to get ? if yes, u may see it in

Table: LedgerJournalTrans

FieldName: TaxGroup

Was this reply helpful? Yes No
Suggested answer

JII SAADUDDIN 1,832 on at

Like (0)

Report
Copy link

Link copied!

Hi Momochi!

Try adding these lines.

dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(generalJournalAccountEntry.LedgerDimension);

mainAccount = MainAccount::find(dimensionAttributeValueCombination.MainAccount);

financialDimension = "";

while select * from dimAttrView

where dimAttrView.ValueCombinationRecId == generalJournalAccountEntry.LedgerDimension

join dimensionAttribute

where dimensionAttribute.RecId == dimAttrView.DimensionAttribute

&& dimensionAttribute.BackingEntityType != tableNum(DimAttributeMainAccount)

{

financialDimension = strLen(financialDimension) > 0 ? financialDimension + "-" + dimAttrView.DisplayValue : dimAttrView.DisplayValue;

}

Was this reply helpful? Yes No
Momochi 1,044 on at

Like (0)

Report
Copy link

Link copied!

Hi Marielle!

No, what I want is shown in the first picture of my question.

The "Sales Tax Payable" account and name

Thanks for your time and help!

Was this reply helpful? Yes No
Momochi 1,044 on at

Like (0)

Report
Copy link

Link copied!

Hello Jii!

First thank you for your time to help!

I believe the code you wrote does not return the values I need, what I need is to bring the data shown in the first picture of my question the "Sales Tax Payable" account and name. if i'm wrong please correct me :)

Thanks again!

Was this reply helpful? Yes No
Verified answer

Momochi 1,044 on at

Like (0)

Report
Copy link

Link copied!
I found a solution, here is the select:

select _mainaccount join DimensionAttributeValueCombination join TaxLedgerAccountGroup join TaxTable where TaxTable.TaxCode == LJtrans.TaxItemGroup && TaxLedgerAccountGroup.TaxAccountGroup == TaxTable.TaxAccountGroup && DimensionAttributeValueCombination.RecId == TaxLedgerAccountGroup.TaxOutgoingLedgerDimension && _mainaccount.RecId == DimensionAttributeValueCombination.MainAccount; reportLine.VatMainAccount = _mainaccount.MainAccountId; reportLine.VatAccountName = _mainaccount.Name;

But Note*: This select will only get correct data IF ONLY the TaxCode and TaxItemGroup have the same name! if the environment has them with different names, we need to add more tables to the above select in order to get the correct result, the tables are I believe: "TaxGroupHeading, TaxGroupData, TaxOnItem and TaxItemGroup"

Hope it's clear, the above select is working in my environment since the TaxCode and TaxItemGroup share the same names.

I was blinded by the LedgerDimension when I didn't actually needed it in this case

Thanks guys for your help!

Was this reply helpful? Yes No