Trying to add a new data table to Scheduled Shipments report. Added a new Data Definition and added a report style. When I open the downloaded SSRS copy my data table is not present. Trying to add JobHead to my Scheduled Shipments report.
Yes, when a table is added to a custom RDD and used on a report style…
the table/fields are not automatically added to the SSRS dataset(s).
I usually add them manually - 2 steps
- edit the SSRS dataset properties - query expression to include the table, joins and fields
- add the fields to the SSRS dataset propterties - fields
Ref this topic Add custom CustXPart Field to AR Form SSRS
Or try searching this site for keywords “SSRS dataset”, there should be a lot of other examples.
2 Likes
I can follow what you are suggesting I do all the way to editing the expression. Not sure what to do here. My RDD looks like this…where I’m joining the job number and company.
And my expression on Dataset OMR50 looks like this… What am I to enter to edit?
Here is an example of SchedShip (E10.1.6) where I added JobHead JOINED to JobProd in the RDD.
Then in SSRS edited the query - where T3 = JobHead. Note it can take a couple tries that first time. I usually copy the query statement to an external editor too (as the built in editor in kind of sucks…like just about everything in SSRS). Also - remember to keep track of your comma’s and quote marks - that’s where mistakes typically creep in.
="SELECT
T1.Company,
T1.CustID,
T1.CustName,
T1.FirmRelease,
T1.IUM,
T1.JobNum,
T1.OrderHeld,
T1.OrderLine,
T1.OrderNum,
T1.OrderRelNum,
T1.PartDesc,
T1.PartNum,
T1.Qty,
T1.ReqDate as ReqDate,
T1.ShipToName,
T1.ShipToNum,
T1.ShipVia,
T1.StagedQty,
T1.WhseCode,
T2.JobNum as JobProd_JobNum,
T2.ProdQty,
T3.ReqDueDate,
T3.ProjectID
FROM OMR50_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company
AND T1.OrderLine = T2.OrderLine
AND T1.OrderNum = T2.OrderNum
AND T1.OrderRelNum = T2.OrderRelNum
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company
AND T2.JobNum = T3.JobNum
"
and finally… remember to add the new fields.
I guess I didn’t do very well.
I’m trying to add the field you see in the Job Header called Due. It’s based on when it estimates you can ship product after the final op is done. I see that in the report that same field title is listed as a parameter. Is this were the error message is coming from?
I tried starting all over from scratch and trying to duplicate what you have. When I look at the dataset expression to edit the query, mine looks different than what you’ve attached here. I have a T4 in my query. So does that automatically make the JobHead T5? Should I see my added fields in the query?
Yes, the query I posted was just an example were the unmodified expression ended at T2 - so the table I added then had to use T3.
So… if your unmodified expression ends with T4
- and you added JobHead to your RDD
- then you would add JobHead starting at T5 in your modified expression
- which will include any JobHead fields - e.g. T5.ProjectID
- AND then the “T5” table joins at the bottom
- also, once your expression is working, you still have to any field to the DataSet Properties - Fields sheet.
Definitely not intuitive
And if you feel adventurous…
As another user mentioned, I always resist the urge to to click “Sync DataSet” because it is likely to trash things.
BUT - sometimes I’ll make a Temp copy of a report and purposely sync the dataset - as the resulting query expression in SSRS can sometimes be VERY INTERESTING to see.
I think I’m definitely getting closer. Now I’m getting this message when I upload the modified SSRS.
I believe I added the fields THEN modified the query expression. Did that mess things up?
I’m guessing it’s something in the modified query expression
(I don’t think the order matters when you modifying the dataset properties - expression/fields or fields/expression)
I had multiple,similar errors the first time I tried modifying an RDD and the SSRS dataset. Burned a couple hours but once I got that first one working… it’s pretty routine now.
1 Like
Hey @bordway,
I came across this looking for answers. I’m currently editing the BOM Listing report and am wondering if you might know if there is any difference in the process when editing a report that has two .rdl files. I am trying to include our Vendor Part Numbers in under the RefDes file on the BOM Listing report.
I’ve received help here before on these reports and was finally able to get my first working. Now I’ve done everything the exact same way and I’m not receiving any errors but the report doesn’t contain any value where I’ve tried to add it. Very frustrating messing with SSRS on Epicor cloud.
Also, I saw you mention a data sync button in some of your replies. I have been unable to find it in the report style application and haven’t found anything in the documentation regarding it. Could you maybe point me to where that is located?
DO NOT PRESS IT!
Noted, as far as I know it doesn’t exist on our instance. I looked everywhere. 
Similar spot as the OP. Cannot for the life of me understand how this statement is incorrect.
="SELECT CAST(T1.BOLNum AS NVARCHAR) AS BOLNum
,T1.Carrier
,T1.CODAmount
,T1.Company
,T1.CommentText
,T1.FreightCharges
,T1.ShipDate
,T1.Plant
,T1.Calc_CustomerAddrList
,T1.Calc_PlantAddrList
,T1.Calc_ShipToAddrList
,T1.Plant_Name
,T1.ToPlant_Name
,CAST(T2.BOLLine AS NVARCHAR) AS BOLLine
,CAST(T2.BOLNum AS NVARCHAR) AS BOLDetail_BOLNum
,T2.Company AS BOLDetail_Company
,CAST(T2.Packages AS NVARCHAR) AS Packages
,T2.Rate
,T2.Weight
,T2.WeightUOM
,T2.Calc_ClassDesc
,T2.Calc_PkgDesc
,T2.Calc_WeightDflt
,T2.Calc_WeightUomDflt
,T3.PackNum
FROM BOLHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN BOLDetail_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company
AND T1.BOLNum = T2.BOLNum"
LEFT OUTER JOIN Shiphead_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company
AND T2.BOLNum = T3.BOLNum"
The Shiphead table is already in the BOL RDD, and I’m just trying to access the PackNum to show each packer sent on a BOL. All I’ve done is set up a relationship between BOLDetail and Shiphead joining on company and BOLNum, then activated the PackNum column in the RDD, and added T3 to this SQL expression. 
Disregard, there was absolutely an extra quotation mark in that statement 
Turns out I was joining on the wrong table to accomplish what I wanted. I joined on VendPart instead of PartXRefVend. I am now getting the expected results.
