# How to Join Data in Report **URL:** \ **Category:** Get Help\ **Created:** [November 1, 2024, 5:01pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723 "2024-11-01T17:01:11Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![jpajot](https://avatars.discourse-cdn.com/v4/letter/j/22d042/32.png) [@jpajot](https://community.fibery.io/u/jpajot)\ **Post date:** [November 1, 2024, 5:01pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/1 "2024-11-01T17:01:11Z")
Hi there, I’m trying to design a report but need to join 2 databases that are not directly join between themselves. In this image I’m trying to show my expected result and the intermediate data join/transformation I need to do in the report. Do you know if this is possible with Fibery reports functions? ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/7/79647820bf5a41031ba64c2b39a3f50a2fda7d20.png) Thank you for your help, Julien ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [November 1, 2024, 5:13pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/2 "2024-11-01T17:13:18Z")
There are 4 possible visualisation types in a report view: chart, table, metric and pie chart. I’m not sure how any of them would end up looking like your expected result, which seems to look more like a whiteboard view to be honest. ---
**Author:** ![jpajot](https://avatars.discourse-cdn.com/v4/letter/j/22d042/32.png) [@jpajot](https://community.fibery.io/u/jpajot)\ **Post date:** [November 1, 2024, 5:19pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/3 "2024-11-01T17:19:20Z")
Sorry my slide is to illustrate the concept. Visually speaking it will be a table. My concern was more about the joining, you see in my the 3rd column of my slide, the BOM Line x article x variant value (e. Smooth LInen Fabric) once it is joined to the project x article x variant, has to be demultiplied for each: 1 line (smooth linen fabric in product A) \> then linked to the the project x article x variant encounters 2 values (spring 25, spring 26) = results in 2 lines of smooth linen fabric in product A If I can solve this aspect in the report then the rest is easy. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [November 1, 2024, 6:44pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/4 "2024-11-01T18:44:17Z")
Can you describe the relevant databases/relations in your workspace. I’m not sure I understand the details from your description/image. ---
**Author:** ![jpajot](https://avatars.discourse-cdn.com/v4/letter/j/22d042/32.png) [@jpajot](https://community.fibery.io/u/jpajot)\ **Post date:** [November 2, 2024, 7:24pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/5 "2024-11-02T19:24:50Z")
Databases in italic: _Article_ 1-N _Article x Variant_ (this DBB is a Helper DBB between _article_ and _variants_) _Article x Variant_ 1-N _Projects x Article x Variant_ (Field “Forecast Pieces”) (this DBB is a Helper DBB between _Projects_ and _Article x Variant_) _Article_ 1-N _BOM_ _BOM_ 1-N _BOM Lines x Article x Variant_ (this DBB is a Helper DBB) _BOM Lines x Article x Variant_ (Field “Unit consumption” here **2.5 MT** for black dress) 1-1 _Parent Article x Variant_ (_Article x Variant_ DBB \> my **dress in Black** ) AND 1-1 _matching Article x Variant_ (_Article x Variant_ DBB \> this BOM line is using the article **Fabric Smooth Linen in Black2** when the dress is Black) So for 1 BOM Lines x Variant, the report should repeat this BOM Line x Variant as many times as there are Project x Article x Variant values. **The join I’d like to build in the report** being _Project x Article x Variant_.Article x Variant field = _BOM Lines x Article x Variant_.Parent Article x Variant field It is important to do it through the report - update frequence could once a week - and not build it in the DBB relations for obvious performance reasons. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [November 3, 2024, 4:03pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/6 "2024-11-03T16:03:20Z")
So, to summarise the db relations (names abbreviated to first letter(s) and arrow head indicates ‘to-many’ end of relation): A→A.V A.V→P.A.V A→B B→BL.A.V BL.A.V–A.V (parent) BL.A.V–A.V (matching) and based on the dbs that are described as ‘helper’ database, presumably the following relations also exist: A.V←V P.A.V←P BL.A.V←A.V Is that correct? Couple of questions: - How could an article have multiple BOMs? Seems a bit suspicious to my mind? - Where do `BOM` entities fit in your initial diagram? - You wrote > [@jpajot](#): > > for 1 BOM Lines x Variant, the report should … but I don’t see any database called `BOM Lines x Variant` in the preceding summary. What’s going on here? If it makes it easier, you could share the space as a template (without any entities) in order for me to fully understand what your setup looks like. ---
**Author:** ![jpajot](https://avatars.discourse-cdn.com/v4/letter/j/22d042/32.png) [@jpajot](https://community.fibery.io/u/jpajot)\ **Post date:** [November 3, 2024, 5:14pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/7 "2024-11-03T17:14:46Z")
Thank you for your help Chris. I was refering to _BOM Lines x Articles x Variant_. Let’s keep it basic to start with before diving into the dozens of tables necessary to manage BOM Lines and all attributes. Maybe back to the initial question to put me on track, can I join data in the report directly, like I would do in PowerBI or Snowflake, or should I think of some kind of workaround by joining the actual Databases? FYI: an article have indeed multiple BOMs in some cases in our industry when exploring multiple options for the product in prototyping phases, some prefer also to separate Design BOM from the Manufacturing BOM, some for limitations of their 3rd Party systems, cannot manage all they want as Variants (BOM variable per suppliers in case of multisourcing for instance…) forcing them to duplicate their BOM per Supplier, again current limitations regarding validity dates of BOM and versioning features forcing a duplicate of BOMs etc. Not a best practice, but have to manoeuver with often sad reality of industrial data in place 🙂 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [November 3, 2024, 8:55pm UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/8 "2024-11-03T20:55:11Z")
Having thought about your particular example, I think it boils down to the following: Project → Project x Article Variant ← Article Variant → BOM Line x Article Variant ← BOM Line where `Project x Article Variant` has a property of `Pieces` and `BOM Line x Article Variant` has a property of `Quantity`. And you want to show, grouped by `Project` and `BOM Line`, the sum of the product of `Pieces` and `Quantity`, for all situations where `Project x Article Variant` and `BOM Line x Article Variant` belong to the same `Article Variant`. I hope I got that right…? > [@jpajot](#): > > can I join data in the report directly, like I would do in PowerBI or Snowflake, or should I think of some kind of workaround by joining the actual Databases? Although reports are capable of supporting multiple databases as sources, they behave as independent data sets, and can’t really be ‘joined’. So basically, I haven’t figured out a way to do this in reports alone. There may be a way to do it by creating a supplementary database in which the entities represent the intersection of `Project` and `BOM Line` for a given `Article Variant`. Let me think it over for a while, and if I have any ideas I’ll get back to you… ---
**Author:** ![jpajot](https://avatars.discourse-cdn.com/v4/letter/j/22d042/32.png) [@jpajot](https://community.fibery.io/u/jpajot)\ **Post date:** [November 4, 2024, 5:54am UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/9 "2024-11-04T05:54:29Z")
Hi Chris, yes you got that right. Indeed so far I came to the same conclusion by creating a supplementary database (with a button to trigger updates insert/delete of lines + a project parameter to avoid crazy requests to generate 300K+ lines). Even better do you think this button could be triggered from the report to make the experience “painless” for the user? - he doesn’t have to care about the machanics really. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [November 4, 2024, 11:34am UTC](https://community.fibery.io/t/how-to-join-data-in-report/7723/10 "2024-11-04T11:34:22Z")
> [@jpajot](#): > > do you think this button could be triggered from the report to make the experience “painless” for the user? Buttons can’t be pressed from a report view I’m afraid ☹