Using single excel sheet with rows of multiple entities

(0) Share

Report

Posted on by real_ashwin

1,221

Hi Experts,

Is it possible to import into management report an excel file with multiple columns (which shows last 12 months adjustment value) and has rows 1 for each entity. Note the columns will likely span multiple years.

like below:

https://imgur.com/a/TQVByde

I want the required column cell depending on the month, year and entity selected. I dont want a summary for all entities.

I have the following issues when i tried:

the first line gets taken for every entity.
the excel if it has june 2019 it comes in the report under june 2020 ?

row def:

https://imgur.com/a/9TqPEvN

col def:

https://imgur.com/a/xtUFVuX

Note i was able to solve issue#1 by having different sheets in the excel file for each entity. But this seems a bit difficult for the users to maintain.

All responses (8)

Answers (1)

Verified answer

real_ashwin 1,221 on at

Like (1)

Report

RE: Using single excel sheet with rows of multiple entities

Hi Ludwig,

Thanks for your help but i found it easier to tweak the row for my requirement.

I was able to solve my requirement by not adding the excel as a column and instead added it in the row definition and since i could not find a way to put all entities in one sheet i split each entity in a different sheet and changed its name for each entity in the tree definition.

I hard coded each cell to the row def so only thing the user needs to do is to keep adding previous month as first column and delete the last period-11 from the excel file.

By doing this and forcing each row to display for a given column in the row def it will work. My column def does not reference the excel (@WKS) at all.

My tree has each excel sheet name mentioned against each entity.

Hopefully this helps someone else.
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

Hi Ashwin P,

The only way I know that would differentiate years based on the 'BASE-...' function is to enter columns for the different months, such as BASE-1 gives July 2020

BASE-2 gives June 2020

etc.

BASE-24 gives the Aug 2018

If you don't like to see the detailed months then you can hide them and add some total columns that summarize the columns.

Maybe that's an option?!

Best regards,

Ludwig
real_ashwin 1,221 on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

Powerbi is not an option at the moment since the AX data is not in powerbi as a cube.

I did not know that in D365FO there is no Excel - Financial / Management Reporter integration available thanks for that bit of info.

Upgrade to D365 might take a while so i hoped i could do a work around in MR.

As i see it, i can make it work with different sheets one for each entity, i understand that MR is able to detect a month entered in the header to the respective period (BASE-?) however it doesn't recognize when two different years are in the same example if i had 24 months 2019 JUN and 2020 JUN then MR picks up the first JUN is there any way to fix this ?
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

Hi Ashwin P,

Wouldn't it be easier to design this Excel integration with PowerBi instead of Management Reporter?

I am asking this because your integration seems already complex and in D365FO there is no Excel - Financial / Management Reporter integration available, meaning that you have to redesign your report in another tool anyway.

Not sure when you plan to upgrade to D365FO, when you do, you will certainly have to find an alternative to the Management Reporter and then the question is why not use a different tool right from the beginning that you can easily upgrade.

Best regards,

Ludwig
real_ashwin 1,221 on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

As far as i understand you cannot add a dimension filter to the column of type WKS (excel) in the column definition. Since all the data was in one sheet i made the reporting tree like below:

https://imgur.com/a/J7f980k

Please note i was able to make it work if i put the separate entity lines in separate sheets and just change the reporting tree (for each entity i will change the sheet name to the correct sheet). But im hoping there is another way to make it work if it was all in one sheet ?
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

I see.

The different legal entities are then separated by a financial dimension in the column definition or by a reference to a reporting tree element?

Best regards,

Ludwig
real_ashwin 1,221 on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

Hi Ludwig, I have read all your posts on excel with MR. However they all seem to use an excel file with a single legal entities or a total i want to have an excel file for 1 sheet having data for multiple legal entities as shown in my first link.
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Using single excel sheet with rows of multiple entities

Hi Ashwin P,

Please have a look at this site:

dynamicsax-fico.com/.../

Seems to fit your requirement.

Best regards,

Ludwig