report builder how to

Is it possible to create following table report:
Col1: All Customers
Col2: Count of Orders in Last 12 months
my customers records get filtered when there is no order is last 12
months
but I'd like to show all customers and count of 0 where there is no
order in last 12 months
any idea?
KamelIf your SQL statement returns the two columns:
SELECT CustomerName, count(OrderID) as CountOfOrders
FROM ORDERTable
GROUP BY CustomerName
Then put those values into a table or list data region.
Is this what you are asking for?
"kamel" <kwiciak@.gmail.com> wrote in message
news:1148415235.959944.42070@.i39g2000cwa.googlegroups.com...
> Is it possible to create following table report:
> Col1: All Customers
> Col2: Count of Orders in Last 12 months
> my customers records get filtered when there is no order is last 12
> months
> but I'd like to show all customers and count of 0 where there is no
> order in last 12 months
> any idea?
> Kamel
>|||I talk about Report Builder with Report Model;
notice: Last 12 Months|||SQL which I would like to obtain in Report Builder:
select
CustomerName,
(select count(OrderID) from Orders where Orders.CustId =Customers.CustId and OrderDate > '2005-05-01')
from Customers
group by CustomerName
is it possible?|||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.|||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.|||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
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.