How to create a report to Excel with header/lines pattern with Electronic reporting

(0) Share

Report

Posted on by Sidick

168

Hello,

I am trying to create a report with output to Excel using electronic reporting (for learning purpose) where I am trying to retrieve for each sales order header, the corresponding sales ordre lines. Actually, the best I could get was only one header with all the lines.

Below is the pattern I'm targeting to:

Sales order header A

Sales order line A1

Sales ordre line A2

Sales order header B

Sales order line B1

Sales order line B2

Sales order line B3

Is that possible to do or am I just dreaming?

Can someone help me please?

Thanks in advance.

I have the same question (0)

All responses (16)

Answers (1)

Suggested answer

Rahul Mohta 21,032 on at

Like (0)

Report

pls try this https://dynamicsax-fico.com/2019/02/10/electronic-reporting-import-of-gl-excel-journals-part-3/

Was this reply helpful? Yes No
Sidick 168 on at

Like (0)

Report

Thanks Rahul for your prompt reply, however Ludwig's blog you pointed out is dealing with import and not export from FinOps.

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Good Morning Sidick,

This should be possible.

What you have to note is that you can only link a single excel table to a record list in D365FO.

Now you actually have two record lists - your headers and your lines.

So, what you can do is the following: In the lines record list, add calculated fields that hold the header information. Thereby, you get a single record list element that you can bind to your Excel table. Might be a bit tricky if you do it the first time but if you follow the link that Rahul shared then you can see an example how this can be done for GL journal header and GL journal lines, which is kind of similar.

Please have a look at this document/video and let us know if you can apply the same technique to your issue.

Best regards,

Ludwig

Was this reply helpful? Yes No
Sidick 168 on at

Like (0)

Report

Hi Ludwig,

Thanks, I'll give a try and let you know.

Best regards

Sidick

Was this reply helpful? Yes No
Sidick 168 on at

Like (0)

Report

Well, I have tried dozens of possible combinations and the best I could get is this one or at least what I am expecting to get, but without the salesID repeating on every single line.

Any idea?

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

Hi,

Can you import the attached xml files in a demo/test environment and give it a try?

(Import the file with XLS included as the last one)

Best regards,

Ludwig

LRE_5F00_SOHEADLINES_5F00_XLS.xml

LRE_5F00_SOHEADLINES-_2800_1_2900_.xml

Was this reply helpful? Yes No
Sidick 168 on at

Like (0)

Report

Thanks Ludwig for your precious time. Well it did work, but that's not what I'm actually looking for, may be it's not possible and GER has reached it's limit.

What I am trying to achieve, is really to have the first line of every sales order linked to the header where I can show information that is not available on line level.

Thanks

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Sidick,

Can you provide some information on what you mentioned before and possibly provide a small draft based on two sales orders that you have in D365FO?

I think you can get the data in the way you need but I don't fully understand it yet ;-)

An example based on some 'USMF Contoso' sales order data would help a lot.

Many thanks,

Ludwig

Was this reply helpful? Yes No
Sidick 168 on at

Like (0)

Report

Ideally it would be something like this. It's actually based on Contoso sales orders in USMF company. Line 1, 7 and 18 are header information and below each header you have line details.

Was this reply helpful? Yes No
Verified answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Sidick,

This kind of grouping is not directly possible because the sales orders are one record list and the lines a second one.

In a single excel table you can include only a single record list.

There is, however, a trick/workaround.

In the excel template that I used you will get all elements listed and see the sales id in each line.

Extend this template and add a second sheet.

In this sheet you add a pivot table that summarizes the data that is included in the table that holds the record; that is, the table that is filled by the electronic report.

When this is done you should get the summarized data in the pivot table as you like.

Here is my example.

Would be great if you could check this and let us know if this workaround is ok for you.

Best regards,

Ludwig

Was this reply helpful? Yes No