Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

(0) Share

Report

Posted on by DianaE

155

Hi All,

I hope this finds you well!

I am just wondering if we can split a report per department when opened in Excel (export option from within Dynamics when opening a FR report). We have a report that we are trying to split its costs per region. Each line has different FDs and nominals allocated to it within the Row Definitions.
I have built a tree definition but I am unable to split this by department and each code to match the Row and subsequently for this report to be downloaded with a separation of totals per department and entity.

Report Row Definitions: (Each line has different nominals set up with different cost codes FDs etc)

Wanted result in Excel:

Since the Financial Dimensions columns will not match, this report currently pulls through more data than needed. Do you have any idea on how to achieve this division of costs per regions and FDs with the FR?

I have attempted everything but I believe this sort of calculation may be too granular for the FR? Perhaps one limitation here?

Thanks,
Diana

I have the same question (0)

All responses (8)

Answers (2)

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

Hi Diana,

How are your columns setup and do you make use of a reporting tree for this report?

Best regards,

Ludwig

Was this reply helpful? Yes No
DianaE 155 on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

Hi Ludwig,
[quote user="Ludwig Reinhard"]
Hi Diana,

How are your columns setup and do you make use of a reporting tree for this report?

Best regards,

Ludwig
[/quote]

Thank you for helping!!

Yes we have a Row Definition as shown above, Columns definitions and a tree.

Tree:
I have built an additional tree which should work as we want, i.e separate each entity and dept into different tabs, but this brings through exorbitant numbers and it seems to just be a total of all nominals and costs allocated to that line, which is not what is needed. Please see below:

Column:

I am not sure if perhaps we cannot accomplish such separation? Since the rows each individually has different FD filters?

Thanks a lot,
Diana

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

Many thanks for that.

If you run the report. Can you see and select from your tree elements?

Doesn't that do the filtering for the different FDs setup in the rows?

Best regards,

Ludwig

Was this reply helpful? Yes No
DianaE 155 on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

Hi Ludwig,
[quote user="Ludwig Reinhard"]
Many thanks for that.

If you run the report. Can you see and select from your tree elements?

Doesn't that do the filtering for the different FDs setup in the rows?

Best regards,

Ludwig
[/quote]
My version of the tree does split the report in different tabs for each department and entity, but the amounts are incorrect. This is the report with my tree definition where the amount is way out of balance:

This is the working report instead with the original reporting tree: The following amount is correct, and it is split into entities but not into cost codes so we cannot differentiate the costs from 101 and 102 departments for example.

Was this reply helpful? Yes No
Verified answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

Hi,

Not sure if this helps but can you check your report settings tab. There is a parameter that defines what is calculated first, rows or column calculations ('calculation priority').

Have you tried running the report with different settings?

Best regards,

Ludwig

Was this reply helpful? Yes No
DianaE 155 on at

Like (0)

Report
RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

[quote user="Ludwig Reinhard"]
Hi,

Not sure if this helps but can you check your report settings tab. There is a parameter that defines what is calculated first, rows or column calculations ('calculation priority').

Have you tried running the report with different settings?

Best regards,

Ludwig
[/quote]
Hi Ludwig,

Thank you for the suggestions, I have indeed attempted all settings available but none of the calculations are correct with this reporting tree where I attempt to separate each department.

I am wondering if is only because the system will not know what to calculate when the FD rows in the row definition and tree definition don't match.

Just reading through this document: https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/financial-reporting-tree-definitions it seems to imply that the reporter will not calculate further than when all FDs match with the columns and rows setup.

Reporting units that contain dimensions in the reporting tree correspond to the dimensions that are used in the row and column definitions. The combination of dimensions determines the amounts that are returned for that unit. For example, in example 2 later in this article, lines 6 and 7 return values only for departments 00 and 01, respectively.

Have you had any experience with this at all? Do you think we are just trying to be too granular?

Thanks,
Diana

Was this reply helpful? Yes No
Verified answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

Hi Diana,

Can you try to remove the subdepartment findims from the rows and only include them into the reporting tree?

If you run the report and select a specific reporting tree element then you should see the data only for the subdepartment selected.

I think that is as far and detailed as you can get things in the financial reporter.

Best regards,

Ludwig

Was this reply helpful? Yes No
DianaE 155 on at

Like (0)

Report

RE: Financial Reporter - Reporting Tree configuration - Possibility of splitting reports with a range of cost codes and per cost code when in excel

[quote user="Ludwig Reinhard"]
Hi Diana,

Can you try to remove the subdepartment findims from the rows and only include them into the reporting tree?

If you run the report and select a specific reporting tree element then you should see the data only for the subdepartment selected.

I think that is as far and detailed as you can get things in the financial reporter.

Best regards,

Ludwig
[/quote]
Hi Ludwig,

Thanks a lot, I have tried removing the FDs from the rows as suggested and it has improved a little the situation but the amounts still don't pull through correctly. I think you are right, we will be unable to go much more granular than this.

Thanks a lot for taking the time to help!

Diana

Was this reply helpful? Yes No