Sub-Summary Grouping with Many-To-Many Relationship

[GisMo]

I'm creating a billing report; pdf from layout.

The typical Database Relationship was Client->Entity->Billable Thing. The report/layout is based on Billable Thing table and then grouped by Client and then Entity with sub-summaries.

Now, wrench in the mix, multiple Clients can share an Entity hence the Many-To-Many relationship. When it comes to billing, for example, if two client's share an Entity 50/50, when creating a billing report, I still want Group By Client, then Entity, but show the billable things listed twice, once under each Client/Entity. This way it can be seen discretely what each Client is being billed for.

I'm not sure this is possible without creating another table with a One-To-Many to represent the actual billing of the billable thing. I'm trying to avoid creating another table if I don't have to.

Any ideas/work-arounds?

Thanks!

[comment]

First, if the relationship between Client and Entity is many-to-many, then you probably want to have a join table between them - certainly If the sharing can be other that equal. However, that's not going to solve the problem of listing the same record more than once in a report. That's just not possible.

You could work around this limitation by producing an individual report for each client and appending it to the previous ones. The net result would be similar to having a layout with a page break before each occurrence of a sub-summary by client part.

 

Edited November 6, 2021 by comment

[GisMo]

@comment Thanks! Yes, the join table is there. Making multiple reports, one for each client, is compelling. I didn't think to try that... But the users like to see it all in a single scrollable list on the screen before printing/PDFing, so I would need to create some scripted preview/walkthrough of the report for each Client. Might be a deal breaker. 

I was hoping there was some kind of "hack" though the database diagrams/relationships.
 

The more I think about it, I should just add another table for billing so each client could have their billing for the portion of the Billable Thing. Then my report's table is based on the billing record so I get the result I want.

[comment]

11 minutes ago, GisMo said:

the users like to see it all in a single scrollable list on the screen before printing/PDFing

To create such preview, you would need a reporting table where each billing would be replicated for each client. This is often done using the "virtual list" technique. Or, instead of replicating each billing ad hoc for the report, you could replicate them permanently by adding the table you wanted to avoid.