Finance | Project Operations, Human Resources, ...

Electronic reporting - how to join tables

(0) Share

Report

Posted on by EvelynFoerster

180

Dear experts,

I have a task to get the negative phys. available items per store into excelsheets per store via. electronic reporting.

How can I connect the retailstoretable to the inventsum to see only the data for one store in one excelsheet ?

I already get excelsheets per store. I use FILTER to get the negative data, but I don't get it managed to see only the data for the right store, I get always the data for the first store.

Do you have any ideas for me please?

7776.pastedimage1628860301114v1.png

Thanks for your help in advance!

Kind regards

Evelyn

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

Hi,

Had a quick look at those tables and it seems that there is a direct relationship between the retailstoretable and the inventlocationtable available in ER.

For that reason, you can grab those data and design your report.

Is it this relationship that you are looking for or are you looking for some guidance in the design of the report?

Best regards,

Ludwig

Was this reply helpful? Yes No
EvelynFoerster 180 on at

Like (0)

Report

Hi Ludwig,

thanks so much fo your reply. My problem is the model mapping. For testing purposes I limit the retailstoretable on run time to 3 stores. Then I expect to get 3 excelsheets in a zip with the data of these stores - in the header the store data and the corresponding positions.

I need the retailstore table to get the excels per store. I think the format configuration is correct, the excels even looks good

But which function do I have to use to connect retailtable and itemsum (I know the relation) ? FILTER will only filter Inventsum to negative values. I had it under $inventsum but this caused a loop and didn't work correctly

Do I or how do I have to use ALLITEMSQUERY in combination with FILTER? And where do I have to place the calculated field? Under Table records or under calculated fields?

Your answer is much appreciated, thanks in advance, I'm just getting a bit crazy... :-)

Thank you

Evelyn

Was this reply helpful? Yes No
EvelynFoerster 180 on at

Like (0)

Report

Meanwhile I could help myself. The model mapping in the above screenshot is much too complicated, the solution is much simpler.

I only need the RetailstoreTable table records and UNDER the Retailstoretable I have created a calculated field:

Here I can get via the relations to the inventsum table and filter it to the negative values I'd like to see. Thats it :-)

The corresponding Format configuration create one excel file per store - Header show store information, lines show negative on-hand inventory.

Was this reply helpful? Yes No