Re: Left function causing Sum function not working??

Hi all,

My query won't add up the quantities after adding LEFT function in SQL statement can anyone see why?

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, SUM(dbo.tblBag_data.bag_quantity) AS qty_on_hand,
dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num,
dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
LEFT(dbo.tblShipping_sched.work_ord_num, 6) = LEFT(dbo.tblBag_data.work_ord_num, 6)
WHERE (dbo.tblShipping_sched.work_ord_num LIKE '343024%')
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num

''this s/b one record w/ summed qty
Work_order line_num qty_on_hand cust_num cust a_p_n a_c_n
343024-01 001 16540 32246 Davol Inc R1082061 R00295-059-70EPF-HM+
343024-01 001 27344 32246 Davol Inc R1082061 R00295-059-70EPF-HM+

Thanks for your input.because your GROUP BY specifies the individual dbo.tblBag_data.bag_quantity values

RB - New table info didn't show up on the Report Builder report

Hi All - I'd like to add some more info to an existing RB report by adding a new table. I am able to add a new table to DSV and build relationship. I am also able to add the table to report model as a new entity and source fields. The report model gets rebuilt and redeployed without problem, and the old report still runs fine. But when I open the report in report builder and want to add more info, the new table/entity/attributes are not showing up at all.

I've had this problem before also, sometimes when I create a report model with multiple tables I can only drag and drop values from one table onto the report builder grid. Once I drag values from the one table there is no way to go back and add values from the other table.

Why is that?

|||

A report that already contains data displays the entities related to the data already in the report. If your new entity is not related to the data in the report, you will not be able to select fields from that entity and add them to your report. If the entities should be related, make sure you have created a Role in the report model that defines that relationship.