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?
Thank you for your help,
Julien
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.
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.
Can you describe the relevant databases/relations in your workspace.
I’m not sure I understand the details from your description/image.
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.
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:
BOM entities fit in your initial diagram?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.
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 
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…?
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…
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.
Buttons can’t be pressed from a report view I’m afraid 