Counting with a filter

Using SS2000, VS2003, RS2000.
I want to list all of the individual orders and how they were shipped. Then
I want to count the total number of orders and get a count for each method of
shipment. So, the data might look like this:
Order # Shipped
1 UPS
2 FedEx
3 USPS
4 FedEx
5 UPS
6 FedEx
Total Orders 6
FedEx 3
UPS 2
USPS 1
I tried adding a group and putting a filter in the group (FedEx) but it only
limited the detail part of the report to the rows that were shipped by FedEx.
Now I can't get rid of that filter. I even deleted all of the headers,
footers, groups and detail section and it still only gives me the rows with
FedEx.
Any ideas,
Thanks,
--
Dan D.I'm closer but not there yet. What I have now is this:
Order # Shipped
2 FedEx
4 FedEx
6 FedEx
Total Orders 3
FedEx 3
1 UPS
5 UPS
Total Orders 2
UPS 2
3 USPS
Total Orders 1
USPS 1
But what I need is what was in my original post
--
Dan D.
"Dan D." wrote:
> Using SS2000, VS2003, RS2000.
> I want to list all of the individual orders and how they were shipped. Then
> I want to count the total number of orders and get a count for each method of
> shipment. So, the data might look like this:
> Order # Shipped
> 1 UPS
> 2 FedEx
> 3 USPS
> 4 FedEx
> 5 UPS
> 6 FedEx
> Total Orders 6
> FedEx 3
> UPS 2
> USPS 1
> I tried adding a group and putting a filter in the group (FedEx) but it only
> limited the detail part of the report to the rows that were shipped by FedEx.
> Now I can't get rid of that filter. I even deleted all of the headers,
> footers, groups and detail section and it still only gives me the rows with
> FedEx.
> Any ideas,
> Thanks,
> --
> Dan D.|||Dan,
Can you do this with 2 datasets and 2 tables?
Dataset1:
SELECT orderID, carrier FROM orders ORDER BY orderID
Dataset2:
SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
carrier ORDER BY COUNT(orderID) DESC
You can use a header/footer row for the grand total.
-Josh
Dan D. wrote:
> I'm closer but not there yet. What I have now is this:
> Order # Shipped
> 2 FedEx
> 4 FedEx
> 6 FedEx
> Total Orders 3
> FedEx 3
> 1 UPS
> 5 UPS
> Total Orders 2
> UPS 2
> 3 USPS
> Total Orders 1
> USPS 1
> But what I need is what was in my original post
> --
> Dan D.
>
> "Dan D." wrote:
> > Using SS2000, VS2003, RS2000.
> > I want to list all of the individual orders and how they were shipped. Then
> > I want to count the total number of orders and get a count for each method of
> > shipment. So, the data might look like this:
> >
> > Order # Shipped
> > 1 UPS
> > 2 FedEx
> > 3 USPS
> > 4 FedEx
> > 5 UPS
> > 6 FedEx
> >
> > Total Orders 6
> > FedEx 3
> > UPS 2
> > USPS 1
> >
> > I tried adding a group and putting a filter in the group (FedEx) but it only
> > limited the detail part of the report to the rows that were shipped by FedEx.
> > Now I can't get rid of that filter. I even deleted all of the headers,
> > footers, groups and detail section and it still only gives me the rows with
> > FedEx.
> >
> > Any ideas,
> >
> > Thanks,
> > --
> > Dan D.|||It's worth a try. I'm wondering RS will keep the two datasets in sync. BTW, I
also posted a different example this morning under the subject "is this
possible in RS".
For the time being, I've created a another group on the carrier. Even though
it's not in the format the client wanted, it will give the counts they want.
I'll keep experimenting, though and try your idea.
Thanks,
--
Dan D.
"Josh" wrote:
> Dan,
> Can you do this with 2 datasets and 2 tables?
> Dataset1:
> SELECT orderID, carrier FROM orders ORDER BY orderID
> Dataset2:
> SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
> carrier ORDER BY COUNT(orderID) DESC
> You can use a header/footer row for the grand total.
> -Josh
>
> Dan D. wrote:
> > I'm closer but not there yet. What I have now is this:
> > Order # Shipped
> >
> > 2 FedEx
> > 4 FedEx
> > 6 FedEx
> > Total Orders 3
> > FedEx 3
> >
> > 1 UPS
> > 5 UPS
> > Total Orders 2
> > UPS 2
> >
> > 3 USPS
> > Total Orders 1
> > USPS 1
> >
> > But what I need is what was in my original post
> > --
> > Dan D.
> >
> >
> > "Dan D." wrote:
> >
> > > Using SS2000, VS2003, RS2000.
> > > I want to list all of the individual orders and how they were shipped. Then
> > > I want to count the total number of orders and get a count for each method of
> > > shipment. So, the data might look like this:
> > >
> > > Order # Shipped
> > > 1 UPS
> > > 2 FedEx
> > > 3 USPS
> > > 4 FedEx
> > > 5 UPS
> > > 6 FedEx
> > >
> > > Total Orders 6
> > > FedEx 3
> > > UPS 2
> > > USPS 1
> > >
> > > I tried adding a group and putting a filter in the group (FedEx) but it only
> > > limited the detail part of the report to the rows that were shipped by FedEx.
> > > Now I can't get rid of that filter. I even deleted all of the headers,
> > > footers, groups and detail section and it still only gives me the rows with
> > > FedEx.
> > >
> > > Any ideas,
> > >
> > > Thanks,
> > > --
> > > Dan D.
>