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.

I'm not sure why you weren't able to display fields from Voucher and Payment in the same report. For instance, with the AdventureWorks sample model, you can create a report that shows Sales by Product and Order Year, which leverages a many-to-many relationship between Product and Order (through Sale).

This post on my blog may be helpful in simplifying the user experience for many-to-many relationships:

http://blogs.msdn.com/bobmeyers/archive/2006/03/24/560255.aspx

Hope that helps!

|||

Hi Bob,

I tried implementing the steps in your post but it still doesn't let me drag and drop fields from both Voucher and Payment. I set up a cardinality of Many to Many from roles Voucher to XRef and Many to Many for roles XRef to Payment. After I drag a voucher fields and try to drag a payment field, it just doesn't allow me to drop it. I can see how if I set the cardinality from 1 to M for Voucher to XRef and M to 1 for XRef to Payment would work, because if I do that it lets me drop it, but doesn't bring the data back correctly. I not sure if I'm doing something wrong.

|||Setting the cardinality to 1:* and *:1 is the correct approach. Can you give more detail about the data you are getting back, and why you believe it is incorrect?