I was asked to add a field to our packing slip report that shows total quantity shipped on this line and remaining quantity. In sales order tracker on the lines/detail tab, there is a field for shipped and it displays the total shipped, however when i look at the field details this is not a field in a database table. Any suggestion for adding this?
You’ll want to check the release for this information.
OrderRel.OurInventoryShippedQty and OrderRel.OurJobShippedQty
If you want values across all releases for a given line, then you’ll need to join just on OrderNum and OrderLine, but then be careful to aggregate the value (perhaps using a subquery/subreport) since you just created a new One-To-Many relationship.
There may also be some calc fields in the RDD that already provide some of this info, like calc_OrderQty and calc_BackorderQty. Check for that first before adding data to the RDD.
Thanks i will give these a try
Followup question, I created the query in the BAQ designer to give me the information I want, what is necessary to get this field onto my report now? This may be above my skill set
Create a Packslip and archive the report, then use SQL Server Management Studio and look up the Report table that you generated, check if the data you are looking for can be calculated using the existing field.
If they don’t exist, then
Open up Report Data Definition
(System Setup -> System Management -> Reporting)
and look up PackSlip
If fields are not there, you have to create a duplicate of the Report Data Definition (RDD) and create a new report style.
Thanks, is there a way to use the query I wrote in BAQ designer in the field i want added to the report?
I am not sure of a way to add a BAQ to a built in report. Depending on the field you added, you can do what @TobyLai suggested. It is not very hard to add fields for output. However, after adding a new custom definition you will need to update the Report Style and connect it to your definition. Then, you can edit the report in SSRS report builder and add the field to the report. You can add calculations if needed there as well.