Report Model Design Question...
The senerio:
Table: Voucher Fields: Voucher Id, Dollars, other fields...
Table: Payment Fields: Payment Id, Dollars, other fields...
Table: VoucherPaymentXRef Fields: Id, Voucher Id, Payment Id
The relationship is Voucher many-to-many VoucherPaymentXRef many-to-many Payment.
I originally brought these 3 tables into the DSV and setup 3 entities in the model (Voucher, Payment, XRef (hidden except for roles)). It allows me to do reports in Report Builder just on Voucher or just on Payment, but it didn't let me create a report to contain fields from both Voucher and Payment. I'm guessing because of the lack of support for many-to-many relationships.
I then went back to the DSV and started from scratch creating 2 named queries; one for Voucher that brings back everything from Voucher as well as the ID field from XRef by way of left outer join. Did the same for Payment so I could eliminate the XRef table from the join between the 2 named queries. This approached help with the reports containing fields from both Voucher and Payment, but causes problems when I just want to do a report just from Voucher or Payment because the left outer join in each named query creates duplicate rows based on the many-to-many relationship with XRef which makes any aggregates wrong.
Is there another way to do this where I can run any of these report types from one model rather than creating 2 different model / perspectives for two different type of reports?
Thanks.
