Groups
Hi Experts, Fairly new to RS and am trying to create a freight tracking report. My issue is (i believe) around groups. For each consignment (shipment), there is one of each of the following dates: - Consignment Date Added - Goods Ready Date - Manifest Date - Delivery Date (this may not be populated) There are multiple 'Event' dates, provided by the transport provider systems in varying granularity e.g. some will provide a pick-up event, in-depot event, delivery event, linehaul event etc. Some will provide the Delivery event only. My specification requires the Shipment number, and all single dates (as described above) to be displayed in table group- with a drill-down to the multiple carrier events. Note, the delivery date will be displayed twice, once in the header and once in the detail. To accomodate this, i extract the delivery date twice in my query - with null's in the delivery column where it is not a delivery date: , CASE R095_DELIVERY_EVENT WHEN 1 THEN R275_EVENT_DATE_TIME ELSE null END AS DeliveryDate Therefore if include Delivery Date in the Group it returns two shipment records for each shipment that has two values in the Delivery Event field i.e. Null and Date. I would think this achievable without going to a subreport...? Any assistance greatly appreciated. ConsignmentAddedDate GoodsReadyDate ManifestDate DeliveryDate EventDate Event ConsignmentNumber 15/11/2010 11:57 16/11/2010 15:00 16/11/2010 10:10 NULL 33:00.0 PICK-UP DEM00000004 15/11/2010 11:57 16/11/2010 15:00 16/11/2010 10:10 NULL 10:00.0 IN-DEPOT DEM00000004 15/11/2010 11:57 16/11/2010 15:00 16/11/2010 10:10 16/11/2010 22:00 00:00.0 DELIVERED DEM00000004 Cheers, Clay
November 16th, 2010 6:25pm

Hi Clay, Besides subreport, you could also utilize drill-down feature to achieve this similar requirement, this is the same issue, you could click this link http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/3795fad5-a540-40bd-9215-10f33e72395e to get a reference from my reply. Thanks, Challen Fu
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2010 1:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics