E10 SSRS - Modifying a Standard Report with a New Report Table

Thank you for the answers!

Scott I will have to see if removing the semi-colon works!

I picked a poor example, I was just trying to get an easy one out there but know that the Ship Via is a linked table.  I have 3 other tables I need to join to the RDD but wanted to make sure it was possible or if I should just re-write the report all together and forget the RDD.

Has anyone been able to successfully update a Report Data Definition, with a new Report Table, and update the Query Expression to bring in the fields from the new Report Table?  I have been able to add excluded fields from the standard tables within the data set but not adding a new table.  Here is the information regarding what I have tried:

Scenario: On the Pack Slip, they would like to see the Ship Via Code Description.  The link is on the Shiphead.ShipViaCode to ShipVia.ShipViaCode and I want to display the ShipVia.Description field.

1. Duplicated the PackSlip Report Data Definition
2. Added the New Report Table of ShipVia
3. Defined my Report Relationship from the ShipHead to ShipVia on the Company and ShipViaCode
4. Created a new folder out on my SSRS portal and copied all of the SSRS rdl files to the new folder
5. Opened the main PackSlip rdl in Report Builder
6. Opened the Dataset and added the left out join in the from clause and added the ShipVia.Description field directly before the FROM clause (Code Below)
6a. You'll notice that I also added the ShipHead.IUM (which was an excluded field previously and it works perfectly)
7. Save and Run the Report from the new Report Style
8. I receive the following error message:
Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: 'column' argument cannot be null.
Parameter name: column

Any idea what is wrong?

Now I know I could create a custom SubReport or an additional dataset which is fed the ShipViaCode and returns the description but figured this has got to be pretty straight forward.

----Custom Query Expression for Report----
="SELECT T1.Company,T1.LegalNumber,T1.PackNum,CAST( T1.CustNum as nvarchar ) as CustNum,T1.ShipComment,T1.ShipDate,T1.Calc_BilContct,T1.Calc_BillToAddress,T1.Calc_SoldToAddress,T1.Calc_Contct,T1.Calc_CustPartOpts,T1.Calc_FOBDescription,T1.Calc_LegalNum,T1.Calc_NumRecordPerPage,T1.Calc_NumTotalParts,T1.Calc_SalesRepName,T1.Calc_ShipToAddress,T1.Calc_ShipViaDescription,T1.Calc_stPhone,T1.Calc_CusPhone,T1.Calc_stFax,T1.Calc_EMailAddress,T1.Calc_FaxNum,T1.Calc_SEmailAddr,T1.Calc_CarrierDesc,T1.Calc_ShipViaSCAC,T1.Calc_CarrierSCAC, T2.PackLine,T2.Discount,T2.ExtPrice,T2.HeaderShipComment,T2.LineDesc,T2.OrderLine,T2.OrderNum,T2.OrderRelNum,T2.PartNum,T2.RevisionNum,T2.ShipComment as ShipDtl_ShipComment,T2.UnitPrice,T2.XPartNum,T2.XRevisionNum,T2.Calc_DspBackOrdQty,T2.Calc_DspBackOrdQtyUom,T2.Calc_DspLabDur,T2.Calc_DspLabMod,T2.Calc_DspLineDesc,T2.Calc_DspLineShpQty,T2.Calc_DspLineShpQtyUom,T2.Calc_DspMatDur,T2.Calc_DspMatMod,T2.Calc_DspMiscDur,T2.Calc_DspMiscMod,T2.Calc_DspPlannedQty,T2.Calc_DspPlannedQtyUom,T2.Calc_DspSerialNumber,T2.Calc_DspShipRouting,T2.Calc_DspSubShipTo,T2.Calc_GetNextLegalNum,T2.Calc_LegalText,T2.Calc_LinChangd,T2.Calc_NextLegalNumID,T2.Calc_NumLineByInv,T2.Calc_OrdRelRef,T2.Calc_POLine,T2.Calc_SalesRepName as ShipDtl_Calc_SalesRepName,T2.Calc_SerialNumLabl,T2.Calc_TotalLineCost,T2.Calc_TotalTax,T2.Calc_HidePackLine,T2.OrderLine_KitFlag,T2.OrderLine_KitParentLine,T2.OrderLine_KitPrintCompsPS,T2.OrderLine_DisplaySeq,T2.OrderLine_KitShipComplete,T2.Calc_MarkForAddress,T2.OrderNum_PONum,T2.PartNum_PartDescription,
T3.RptLiteralsLblDescription,T3.RptLiteralsLblFax, T3.RptLiteralsLblEmail,T3.RptLiteralsLblRel,T3.RptLiteralsLblPh,T3.RptLiteralsLblPO,T3.RptLiteralsLblRev,T3.RptLiteralsHComponents,T3.RptLiteralsLblBckOrQty,T3.RptLiteralsLblCustPart,T3.RptLiteralsLblDiscAmt,T3.RptLiteralsLblExtendedPrice,T3.RptLiteralsLblFOB,T3.RptLiteralsLblHdngPckSlip,T3.RptLiteralsLblLbDu,T3.RptLiteralsLblLine,T3.RptLiteralsLblMiDu,T3.RptLiteralsLblMtDu,T3.RptLiteralsLblOurPrt,T3.RptLiteralsLblPackSlip,T3.RptLiteralsLblPage,T3.RptLiteralsLblPlndQty,T3.RptLiteralsLblPoLine,T3.RptLiteralsLblPrtNum,T3.RptLiteralsLblReference,T3.RptLiteralsLblSalesOrder,T3.RptLiteralsLblSalesperson,T3.RptLiteralsLblShipDt,T3.RptLiteralsLblShipRouting,T3.RptLiteralsLblShipTo,T3.RptLiteralsLblShipVia,T3.RptLiteralsLblShpdQty,T3.RptLiteralsLblSoldTo,T3.RptLiteralsLblSrlNum,T3.RptLiteralsLblSrlNums,T3.RptLiteralsLblSubShipTo,T3.RptLiteralsLblTotalLineCost,T3.RptLiteralsLblTotalTax,T3.RptLiteralsLblUnitPrice,T3.RptLiteralsLblYourPO,T3.RptLiteralsLLegalText,T3.RptLiteralsLLegNum,T3.RptLiteralsLSalesKit,T3.RptLiteralsLCarrierSCAC,T3.RptLiteralsLCarrier,T3.RptLiteralsLblShipViaSCAC,T3.RptLiteralslblInvNumber,T3.RptLiteralslblInvDueDate,T3.RptLiteralsLblOf, T3.RptLiteralsLblContinueOnNext, T3.RptLiteralsLblComeFrom
, T2.IUM, T4.Description FROM ShipHead_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
 LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T3
  ON T1.RptLanguageID = T3.RptLanguageID LEFT OUTER JOIN ShipVia_" + Parameters!TableGuid.Value + " T4 ON T1.Company = T4.Company and T1.ShipViaCode = T4.ShipViaCode"

The Ship Via Description field is already available in the RDD, you just need to include it in the fields you want.

1. Select the ShipHead table from the Report Table -> List sheet
2. Select the ShipViaCode on the Report Table -> Linked Tables -> Pick Links sheet and add it the the Lookup Picked window.
3. Select the ShipViaCode from the drop down on the Report Table -> Linked Tables -> Description Fields and then select the Description field from the Fields Available window and move it to the Fields Picked window.

The xml file will now contain the Ship Via Description that you can add to your report layout.