Hello,
I’m trying to add the Sales order release contact information to the PORelPart Subreport.
So far I have added the OrderRel and CustCnt to the Report Data definition, but now I’m stuck.
Do I need to add a new Data set & query to the SSRS report?
I’m stuck and trying to understand the relationship between the RDD and the report.
Any support is appreciated! 
The RDD just generates tables in the SSRS database. For each table that is in the RDD, a copy of that table will be created in the SSRS db.
The query phrase in the RDL is then using those tables that the RDD generated to pull the data into the report. So, if you update the RDD, you need to also update the query phrase in the RDL to include the fields/tables you added.
I recommend just adding the fields to the main ‘query’ / dataset in the actual report.
Edit this ‘query’ field in the dataset that the rest of the data that is being displayed is coming from.
(my example is a baq report but same for regular reports)
Copy/paste the current query into a editor that is easier to work with and have a look at how Epicor has done this. You will most likely have to add another table and define the join conditions. This join should be the exact same conditions as what you had defined in your RDD.
For example, if you join part with partbin and partbin was already existing in your ssrs report, then we need to define the Part table as T6 or something else (default naming is T1, T2, etc. but can be anything) and define how we are joining this table such as ON T6.Company = T1.Company, etc.
From there, if our new table was T6, then we can add our fields where the rest of the fields in the query are defined such as T6.Description, T6.Company, etc.
After this, go to the dataset fields tab and add a ‘query field’ for each field that you have manually added to the query expression:
Example - if you named a field T6.Description (Part.Description) - then you would simply type Description.
After this you are ready to use the field!
Also forgot to mention.
Make sure in your RDD you
-
Added the table join condition such as Part.Company = PartBin.Company etc
-
The fields you are looking to query in your report are not excluded
Thanks Conner,
I need to add this to the existing PO report. I’m not using BAQ report designer
Thanks John,
I’ve added the tables to the RDD.
I’m now just having issues adding them to the query and wondering if I need to add a new Data set. The customer contact also sits in sub report PORelPart…
No, you can just add it to the existing data set. Are you proficient with TSQL? That is all the query phrase is. So you would just need to add the fields and the new table to the phrase.
This step is the same across BAQ reports and regular SSRS reports
You can share the current expression and table/fields you would like added if you need some help!
Here is the RDD
I’ve added the SOrel and the CustCnt tables and joined the relationships.
I’ve added both tables because the contact needs to come from the order release.
Included the following fields from OrderRel
And these from the CustCnt
Here is my query.
="SELECT
T1.RptLanguageID,
T1.CurrencyCode,T1.CommentText,
T1.Company,
T1.FOB,
T1.FreightPP,
T1.OrderDate,
T1.PONum,
T1.PrintAs,
T1.ShipToConName,
T1.ShipViaCode,
T1.Calc_AccountRef,
T1.Calc_BillToAddrList,
T1.Calc_Buyer,
T1.Calc_CurDesc,
T1.Calc_CurSymb,
CAST(T1.Calc_ExistingDropShipReleases AS nvarchar) AS Calc_ExistingDropShipReleases,
T1.Calc_FOBDesc,
CAST(T1.Calc_MultiShiptoAddresses AS nvarchar) AS Calc_MultiShiptoAddresses,
T1.Calc_PurTerms,
T1.Calc_ShipToAddrList,
T1.Calc_ShipVia,
T1.Calc_VendorContact,
T1.Calc_VendPhone,
T1.Calc_VendFax,
T1.Calc_EMail,
T1.BuyerID_EMailAddress,
T1.Vendor_EMailAddress,
T1.Vendor_FaxNum,
T1.VendorCnt_EmailAddress,
T1.Calc_TotDocMiscAmt,
T1.VendorCnt_FaxNum,
T2.CommentText AS PODetail_CommentText,
T2.Company AS PODetail_Company,
T2.DocUnitCost,
T2.MfgPartNum,
T2.MfgPartOpts,
T2.PartNum,
T2.POLine,
T2.PONUM AS PODetail_PONum,
T2.PUM,
T2.RevisionNum,
T2.SubPartNum,
T2.SubPartOpts,
T2.SubPartType,
T2.Taxable,
T2.VendorPartOpts,
T2.VenPartNum,
T2.Calc_CostPer,
T2.Calc_DtLineDesc,
T2.Calc_ExtCost,
T2.Calc_UOMDescription,
T2.Calc_MfgID,
T4.RptLiteralsLCur, T4.Calc_AccountRef AS RptLiteralsCalc_AccountRef,
T4.RptLiteralsLFax,
T4.FreightPP AS RptLiteralsFreightPP,
T4.RptLiteralsLAuthBy,
T4.RptLiteralsLChangeOrder,
T4.RptLiteralsLClosed,
T4.RptLiteralsLDueDt,
T4.RptLiteralsLExtPrice,
T4.RptLiteralsLFOB,
T4.RptLiteralsLHdng,
T4.RptLiteralsLJobNum,
T4.RptLiteralsLLine,
T4.RptLiteralsLLineChargeSubtotal,
T4.RptLiteralsLMfgPartNumber,
T4.RptLiteralsLMiscChargeSubtotal,
T4.RptLiteralsLMslshS,
T4.RptLiteralsLOrderDate,
T4.RptLiteralsLOrderQty,
T4.RptLiteralsLOurPartNumber,
T4.RptLiteralsLPg,
T4.RptLiteralsLPhone,
T4.RptLiteralsLPONum,
T4.RptLiteralsLPrtRvDes,
T4.RptLiteralsLQty,
T4.RptLiteralsLResalNo,
T4.RptLiteralsLRevised,
T4.RptLiteralsLSeeBelow,
T4.RptLiteralsLSeq,
T4.RptLiteralsLShipTo,
T4.RptLiteralsLShipVia,
T4.RptLiteralsLShRelReq,
T4.RptLiteralsLSubstitute,
T4.RptLiteralsLSubstitutedFor,
T4.RptLiteralsLSupplierPartNumber,
T4.RptLiteralsLTax,
T4.RptLiteralsLTerms,
T4.RptLiteralsLTotal,
T4.RptLiteralsLUnitPric,
T4.RptLiteralsLVend,
T4.RptLiteralsLLnMsChrg,
T4.RptLiteralsLDesc,
T4.RptLiteralsLNDI_HRB, T4.RptLiteralsLNDI_UstID, T4.RptLiteralsLNDI_Director, T4.RptLiteralsLNDI_BankName, T4.RptLiteralsLNDI_BLZ, T4.RptLiteralsLNDI_Account, T4.RptLiteralsLNDI_IBAN, T4.RptLiteralsLNDI_BIC, T4.RptLiteralsLNDI_TCs,
T4.RptLiteralsLNDI_PlaceOfCompany, T4.PONum AS RptLiteralsPONum,
T1.Calc_VendorID, T4.RptLiteralsLAmt
FROM POHeader_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PONum = T2.PONUM
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID"
Thanks so much for all of your support!