Currently the query builder (in the Report Designer) cannot automatically build queries if there are multiple separate relationships connecting two tables.
Here's an example:
Table A: Orders
- OrderID
- ShippingAddressID (FK => AddressID)
- BillingAddressID (FK => AddressID)
- ...
Table B: Addresses
- AddressID
- AddressLine1
- ...
If both tables are selected in the query builder, you will get the following columns:
- OrderID
- ShippingAddressID
- BillingAddressID
- AddressID
- AddressLine1
- ...
In other words, only one copy of the address data is pulled in, despite the two separate FK relationships. I would expect something like the following:
- OrderID
- ShippingAddressID
- BillingAddressID
- ShippingAddressID_AddressID
- ShippingAddressID_AddressLine1
- BillingAddressID_AddressID
- BillingAddressID_AddressLine1
- ...
We ran into this issue because we are using Telerik Reports to provide reporting for a data warehouse, and we have multiple relationships in our "table A" pointing to a date dimension (our "table B"). The current workaround for this is to write your own SQL queries instead of relying on the query builder, but we would like for our business users to be able to create reports on their own, and they don't know SQL.
