Can't get the details right - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Can't get the details right

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-09 : 07:59:52

I have a query I run to get the information for a report in SSRS;

SELECT detail_record.detail_number, detail_record.ticket_number, detail_record.division,detail_record.pickup_weight, CAST(detail_record.pickup_dt AS datetime) AS PickupDate,
CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, detail_record.customer_delv_to, detail_record.customer_bill_to,
cmBillTo.customer_number AS bill_to_number, cmBillTo.customer_name AS bill_to_name, cmBillTo.city AS bill_to_city,
cmDelvTo.customer_number AS deliver_to_number, cmDelvTo.customer_name AS deliver_to_name, cmDelvTo.city AS deliver_to_city,
detail_record.pickup_date, detail_record.grand_total_wt, hauler_master.hauler_number, hauler_master.hauler_name, hauler_master.city,
detail_record.ddp_weight, customer_master.customer_number
FROM detail_record INNER JOIN
customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN
hauler_master ON detail_record.hauler_number = hauler_master.hauler_number INNER JOIN
customer_master ON cmBillTo.customer_number = customer_master.customer_number AND
cmDelvTo.customer_number = customer_master.customer_number
WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to=@customer_number OR detail_record.customer_delv_to=@customer_number)

ORDER BY detail_record.pickup_date

For the most part on the report all the fields I require are correct, but I am having trouble with the Division field. The report should look like this, it is grouped by Customer Number then Ticket Number then by Pickup Day.


Customer     Customer
Number       Name
12345       XXXXXXX

Ticket     Deliver     Pickup     Hauler     Load   Division   Pickup
Number       Day        Day       Number    Pounds             Pounds
                                                        1      500
                                                        2      500
                                                        3      500
1234      2008/09/01  2008/09/01    123      15000             15000

                                                        4      500
                                                        5      500
5678      2008/09/01  2008/09/01    123      10000            1000  

                      2008/09/01   TOTALS    2500             2500

There can be up to 11 ticket numbers included in the report, and every ticket number can have different Divisions, but when I generate the report, only 1 Division shows on the report. Any suggestions, I would like to do any work in the report not in the query.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 09:36:36

whats the expression giuven for ticket field? also are you grouping by ticket number in report?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-09 : 10:04:04

Expression for TicketNumber is

=Fields!ticket_number.Value

and I am grouping by in this order:
Customer Number (Main Group)
Ticket Number (Detail Group
Pickup Day (Detail Grouo)

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 10:09:26

is this for single day that you've more than one division value?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-09 : 11:23:40

Yes, any day could have more than 1 division included.

Thanks for the help
CoachBarker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources