topic Re: D365 Trial Balance Report - Alteryx in Alteryx Designer Desktop Discussions

D365 Trial Balance Report - Alteryx

cterrence — Mon, 23 Jan 2023 01:07:29 GMT

HI All

Has anyone managed to extract the data from D365 trial balance report via Alteryx ?

Regards

Re: D365 Trial Balance Report - Alteryx

Raj — Mon, 23 Jan 2023 05:01:46 GMT

Here's a step-by-step guide on how to extract data from a Dynamics 365 (D365) trial balance report using Alteryx:

Step 1: Install and configure the Dynamics 365 Connector in Alteryx Designer. This will allow Alteryx to connect to your Dynamics 365 instance and extract data.

Step 2: Create a new workflow in Alteryx Designer and add the Dynamics 365 Connector to the canvas.

Step 3: Configure the Dynamics 365 Connector by inputting your Dynamics 365 instance URL, username, and password.

Step 4: Use the Select tool to select the fields from the trial balance report that you want to extract. You can also use the Filter tool to filter the data based on specific criteria.

Step 5: Use the Dynamics 365 Connector to retrieve the data from the trial balance report. The connector will retrieve the data from the specified fields and filter criteria.

Step 6: Use the Output tool to write the extracted data to a file or database.

Step 7: Run the workflow and verify that the data has been extracted correctly.

Note: Make sure that you have the appropriate permissions to access the D365 trial balance report, and that the data is being extracted in compliance with the company data governance policies.

Re: D365 Trial Balance Report - Alteryx

cterrence — Mon, 23 Jan 2023 05:18:20 GMT

Thank you Raj for your reply

But I should have given more details

We have connected Alteryx to a copy of the prod DB. And according to Dynamics 365, there is no report as Trial Balance report in the DB. This is written in x++ and following tables are used to get to the final trial balance report

Tables

DimensionAttributeValueCombination table

DimensionFocusBalance table

GeneralJournalAccountEntry table

LedgerTrialBalanceTmp table

I do not have the x++ query with me and I really dont know what goes behind the query once your select your criteria and run the report in D365. Once the query is ran, I am assuming that data is extracted and sits in LedgerTrialBalanceTmp table and this is where the data is retrieved in D365

Have you extracted the report like the way you mentioned in your reply ? Please let me know

Regards

Re: D365 Trial Balance Report - Alteryx

Raj — Mon, 23 Jan 2023 05:25:48 GMT

I have not extracted such files so not sure about all the problems.

Re: D365 Trial Balance Report - Alteryx

cterrence — Mon, 27 Feb 2023 11:23:18 GMT

Apologies for using this thread for another question. My question is not appearing in the discussion forum

I am new to Regex. I need to split the bank into different columns

16,399,162435,,DEPOSIT - CASH 66001,,66001_TRAN EFF DATE 230131
16,399,106035,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,237145,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,285640,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,217210,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,217160,,DEPOSIT - CASH 64001,,64001_TRAN EFF DATE 230131
16,399,840663,,DEPOSIT - CHEQUE(S) 64001 #Chq:1,,64001 #Chq:1_TRAN EFF DATE 230131
16,399,234115,,DEPOSIT - CASH AGENT NO 63201,,AGENT NO 63201_TRAN EFF DATE 230131
16,399,142245,,DEPOSIT - CASH 33901,,33901_TRAN EFF DATE 230131
16,399,186515,,DEPOSIT - CASH 31001,,31001_TRAN EFF DATE 230131
16,399,306765,,DEPOSIT - CASH 040501,,040501_TRAN EFF DATE 230131
16,399,162980,,DEPOSIT - CASH ABCD 43001,,SMGB43001_TRAN EFF DATE 230131
16,399,74910,,DEPOSIT - CASH ABCD 43001,,SMGB 43001_TRAN EFF DATE 230131
16,399,31310,,DEPOSIT - CASH ABCD 43001,,SMGB 43001_TRAN EFF DATE 230131
16,399,137195,,DEPOSIT - CASH ABCD 43001,,SMGB 43001_TRAN EFF DATE 230131

With my limited knowledge, I have entered this - (\d{2}),(\d{3}),(\d+),,(\w+-\w+)(\d+),,(\d+)_(\w+)(\d+)

This is throwing empty rows. See attached

I want the output like this

16 399 162435 DEPOSIT - CASH 66001 66001 TRAN EFF DATE 230131

16 399 137195 DEPOSIT - CASH ABCD 43001 SMGB 43001 TRAN EFF DATE 230131

16 399 840663 DEPOSIT - CHEQUE(S) 64001 Chq:1 64001 Chq:1 TRAN EFF DATE 230131
16 399 234115 DEPOSIT - CASH AGENT NO 63201,, AGENT NO 63201 TRAN EFF DATE 230131

Re: D365 Trial Balance Report - Alteryx

ShankerV — Mon, 27 Feb 2023 11:46:16 GMT

Hi @cterrence

One way of doing this.

Many thanks

Shanker V

Re: D365 Trial Balance Report - Alteryx

Raj — Mon, 27 Feb 2023 12:10:02 GMT

hi @ShankerV I think you just missed the last separation (thanks for sharing)

@cterrence find the updated regex

(\d+),(\d+),(\d+),,(\w+\s\-\s.+)\s(\d+\s{0,1}.+),,(\u{0,}\s{0,1}\u{0,}\s{0,1}\d+\s{0,1}.+)\_(.+)\s(\d+)

This might help

Re: D365 Trial Balance Report - Alteryx

cterrence — Mon, 27 Feb 2023 14:45:25 GMT

Thank you very much Shanker/Raj....much appreciated.

Re: D365 Trial Balance Report - Alteryx

cterrence — Tue, 28 Feb 2023 03:22:54 GMT

I ran this query and it worked for all lines except one -

16,399,162980,,DEPOSIT - CASH SMGB43001,,SMGB43001_TRAN EFF DATE 230131

Please assist