Order Counts based on Group Totals

[mwake]

I'm using CR 2011 with a SQL Server database as my data source. I'm working for a startup that is developing a mobile app to hail cabs, and I need to develop a report that counts the number of orders based on the number of hails associated to it. In other words, I want to chart the order count for orders with 1 hail, 2 hails, 3 hails, and no hails. I am using 2 tables, an order table and a hail table, linking the 2 by hail id. In order to get hail counts, I am grouping by order ID, then doing a count of hail id's per order. I was able to count the orders with no hails by creating a formula called Hail/No Hail(if IsNUll(HailID)=True then yes else no). How do I group the orders based on orderID hail counts to get total orders by order count??

EX: No Hails 50 orders
1 Hails 75 orders
2 Hails 85 orders
3 Hails 40 orders

Data
Order ID Hail ID
12345 54321
12345 54322
12345 54323

12346

123467 54324

 

[Charliy]

The only way I can think of is to make each of the total numbers a subreport.

 

[pmax9999]

Hi mwake

The best way to achieve this, is going to be a Command that returns a single row for each OrderID with a Count of the HailIDs for each.

The syntax varies depending on database. If you create a basic report that includes these 2 fields and post a copy of the SQL code generated by it, we can probable help you build the Command.

The other alternative would be a database View that does the same thing if you have a friendly DBA to help you.

Cheers
Pete.