Hi,
I am trying to add two calculated fields (Calc_ShipToEmail and Calc_ShipToPhone both from the OrderHed dataset) to an RDL file. I saw these two fields being used in a different report and they are being pulled from a different data source than the one being used in my report. Is there any way I can access these two fields given that I am using a different data source but the OrderHed dataset is one of the datasets used in my report ? If yes, how can I do that ?
Thank you,
Yes, you can make your own RDDs by copying the existing ones, or starting from scratch. Then use that RDD to create your SSRS. What report are you trying to edit?
Thanks for the answer Nate. I am working on a customized version of the Pro-Forma Invoice report and want to include fields from the order sales ack report
I already tried adding these calculated to the existing RDD but it did not do anything. Creating a new RDD with these two calculated fields will give me access to their data then ?
You cannot add calculated fields to an RDD without the SDK.
Mr. Eric: I do not know of a way to add a true “Calculated” field to an RDD.
For something like what you are doing - can’t you just concatenate in the SSRS report.
DaveO
Thank you John. I do not have the SDK… So my only option is to use the RDDs that were already provided by Epicor then. Not great.
The fields you want are not native to the OrderHed table. That is why you can’t just add them in. They are native to the ShipTo table. You have to link OrderHed to ShipTo. then pull EmailAddress and PhoneNumber from ShipTo. I would copy the RDD you are using, then modify that copy. Then you have to copy the report style, and change the RDD for the new style to the one you just created. Finally download the RDL and make and edits you need (like adding the fields in). It can be tricky to get this to work, but it can work. Good luck!
thank you, I am going to give it a shot
Hi Dave, how can I concatenate data coming from these two calculated fields in my report ?
ok thank you
Before doing all of that work, I would look at the Linked Tables and see if the information that you want already exists in an easier way to get at.
I just looked at the linked tables and sadly I do not see the info I need.
Sorry to bug you with this. So my new RDD is done and I modified my RDL. The report is printing but not populating these two fields I was looking to add. I wonder if this comes from with the Datasets part of the RDL file:
at the moment I have added my two fields of interest (in the orderhed dataset property expression and as two seperate fileds under the dataset) and my ShipTo table through the Dataset Properties of my OrderHed dataset using the following sql code (table of interest is T9):
=“SELECT T1.CustNum,T1.RptLanguageID,T1.Company,T1.DocDepositBal,T1.DocOrderAmt,T1.DocRounding,T1.FOB,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_SoldToAddressList,T1.Calc_BillToAddressList,T1.Calc_CompanyAddressList,T1.Calc_CurrDocDesc,T1.Calc_CurrSymbol,T1.Calc_ProFormaComment,T1.Calc_SalesPerson,T1.Calc_SalesTerms,T1.Calc_ShipToAddressList,T1.Calc_ShipVia,T1.Calc_CustPartOpts,T1.Calc_TotMiscCharges,T1.Calc_Subtotal,T1.Calc_TotalTaxAmt, T1.Calc_TaxMethod,T1.Calc_OrderMiscAmt,T1.Calc_LineMiscAmt,T1.PayFlag, T1.PayAccount, T1.Calc_ShipToEmail, T1.Calc_ShipToPhone,
T2.CustNum as OrderDtl_CustNum,T2.Calc_DocTaxAmt,T2.DocExtPriceDtl,T2.DocUnitPrice,T2.KitFlag,T2.KitParentLine,T2.KitPrintCompsInv,T2.KitQtyPer,T2.Calc_LineDesc,T2.OrderLine,T2.OrderNum as OrderDtl_OrderNum,T2.OrderQty,T2.SellingQuantity,T2.PartNum,T2.PricePerCode,T2.SalesUM,T2.PartNum_PartDescription,T2.Calc_ProFormaComment as Calc_DtlProFormaCmt,T2.XPartNum,T2.DocDiscount,T2.Calc_TotalAmountLine,
T3.[Description],T3.DocMiscAmt,T3.SeqNum,T3.OrderLine as OrderMsc_OrderLine,T3.Calc_Description,T3.Calc_NumRels,T3.FreqCode,
T4.PartNum as Part_PartNum,T4.HTS,T4.ISOrigCountry,T4.SchedBcode,
T5.FOB,T5.Description as FOB_Description,
T6.CountryNum,T6.Description as Country_Description,T6.ISOCode,T6.ISOrigCountry as Country_ISOrigCountry,T6.Company as Country_Company,
T7.CustNum as Customer_CustNum, T7.CustID,
T8.Name as CustCnt_Name,
T9.PhoneNum as PhoneNum, T9.EMailAddress as EMailAddress
FROM OrderHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
LEFT OUTER JOIN OrderMsc_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T4
ON T1.Company = T4.Company AND T2.PartNum = T4.PartNum
LEFT OUTER JOIN FOB_" + Parameters!TableGuid.Value + " T5
ON T1.Company = T5.Company AND T1.FOB = T5.FOB
LEFT OUTER JOIN Country_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T4.ISOrigCountry = T6.CountryNum
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T7
ON T1.Company = T7.Company AND T1.CustNum = T7.CustNum
LEFT OUTER JOIN CustCnt_" + Parameters!TableGuid.Value + " T8
ON T1.Company = T8.Company AND T1.CustNum = T8.CustNum AND T1.ShipToNum = T8.ShipToNum AND T1.ShpConNum = T8.ConNum
LEFT OUTER JOIN ShipTo_" + Parameters!TableGuid.Value + " T9
ON T1.Company = T9.Company AND T1.CustNum = T9.CustNum AND T1.ShipToNum = T9.ShipToNum"
I also tried two other options where I created a dataset called “ShipTo” with the following expression:
=“SELECT T1.PhoneNum,T1.EMailAddress
FROM ShipTo_” + Parameters!TableGuid.Value + " T1"
=“SELECT T2.PhoneNum, T2.EMailAddress
FROM OrderHed_” + Parameters!TableGuid.Value + " T1
LEFT JOIN ShipTo_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.CustNum = T2.CustNum AND T1.ShipToNum = T2.ShipToNum AND T1.ShpConNum = T2.ConNum"
but none of this fixes has allowed me to populate these two fields in my report.
Any idea what I am doing wrong ?
No worries! Adding fields to the RDL is always a tricky task. It looks like you did it right.
A few things to check: In the RDL right click on your dataset in the left menu. Click on Dataset Properties. In there go to Query and review the query expression. It looks like you already did this part. And the expression you posted looks ok. Now go to Fields (see left menu) make sure to add your new fields to this list. Use the field names without the table.
It is also worth whipping up a BAQ to prove to yourself that you have the data you are looking for in the fields you expect them to be in. You don’t have to use the BAQ for anything other than to know for sure that the data you want is available in the first place. This can help find issue with your joins, or places where data simply is not available.
Thanks for your help. I have run a BAQ and I can see the Phone Numbers an Email Adresses I am looking to populate in my Report in this BAQ. It just doesn’t show up on my report for some reason. Back to the drawing board
Post up your RDL if you can.
For some reason I can’t open that file. I am not in finance, so I have no idea about pro forma invoices. I was going to recreate it on my end. Sorry, I can’t help you here!
No worries, you helped a lot already.