Refrash of topic.
I would like to create following query:
select
Customer.CustomerID,
Time.DayID,
count(Order.OrderID)
from
Customer LEFT OUTER JOIN
Time LEFT OUTER JOIN
Order
with ReportBuilder I can not obtain such a query.
RB puts table from whitch value is on matrix's data area on top, eg.
select
Customer.CustomerID,
Time.DayID,
count(Order.OrderID)
from
Order LEFT OUTER JOIN
Customer LEFT OUTER JOIN
Time
in this example count(Order.OrderID) is on data area of matrix type
report
Any ideas,
Kamel
[quoted text, click to view] kamel wrote:
> Thank you!
> It doesn't work with 3 entities as in my exaple (1 entity - "time" is
> in a filter)
>
> and relations are as:
> Customer - Order - Time
>
> Any ideas,
> Kamel
>
> larthallor wrote:
> > Have you checked the cardinality of the relationship of the Customers entity
> > to the relationship of the Orders entity? It sounds like it should be
> > OptionalMany, meaning that there may be 0 or more Orders for each Customer.
> > If it is Many, then you are telling the model that each Customer has 1 or
> > more Orders.
> >
> > Setting the cardinality from Many to OptionalMany should change the query
> > from something analogous to:
> >
> > select
> > c.CustomerName,
> > count(o.OrderID)
> >
> > from
> > Customers c
> > inner join
> > Orders o
> > on
> > c.CustId = o.CustId
> >
> > where
> > o.OrderDate > '2005-05-01'
> >
> > group by
> > c.CustomerName
> >
> > to:
> >
> > select
> > c.CustomerName,
> > count(o.OrderID)
> >
> > from
> > Customers c
> > left outer join
> > Orders o
> > on
> > c.CustId = o.CustId
> >
> > where
> > o.OrderDate > '2005-05-01'
> >
> > group by
> > c.CustomerName
> >
> > Having said this, my own experiments with Report Builder and Models show
> > that there may be a bug preventing the outer join from being used, even if
> > the cardinality is OptionalMany. However, the cardinality will have to be
> > set properly for it to work.
