Trouble adding table to sub report in Job traveler

The end goal is to add the bill to customer name to the shipping schedule in the job traveler. I’ve added OrderHed as a data source, and given it a relationship to JobShip:

Then I also chose the Customer in linked tables and added their name:

Finally, I went into the report builder and added the table and fields to the query:


When I test the report, it runs with no errors, but the sub report does not render:

I can’t see any issues. I’m at a loss as to what to try next. This seemed like a bunch of simple steps so I’m not sure what I screwed up.

Just a wild guess, is there a space between T1 and LEFT in the expression?

PS: I think you would want to maintain spacing near the quotes too as you are building the SQL statement and the T1 or T2 need to be the alias to the tablename_guid - not tablename_guidT1. If that makes sense.

Two suggestions.

  • Are you able to access SSMS and look at the temp tables? if so, do that and look at the tables for your latest GUID. This way you can ensure that the field actually made it into there.
  • Are you able to run the Subreport on its own with the GUID? sometimes subreports are just finicky and the true error is unclear until you look at it by itself.

I think you spotted it. Because there should also be a space before the T1 as well. And you are missing a space between the second table"+Parameters!TableGuid.Value+" and T2

1 Like

Okay, my query is now this:

="SELECT T1.Company,T1.OrderLine,T1.OrderNum,T1.OrderRelNum,T1.ReqDate,T1.SellingJobQty,T1.SellingReqQty,T1.SellingStockQty,T1.Calc_AssemblySeq,T1.Calc_DispStatus,T1.Calc_JobNum,T1.Calc_ShipTo,T1.Calc_ShipVia,T1.Calc_UOM,T1.Calc_WhseCode
, T1.[Calc_PartNum], T1.[Calc_POLineNum], T1.[Calc_PONum], T1.[Calc_RPTUSERID], T1.[Calc_ShipAddr1], T1.[Calc_ShipAddr2], T1.[Calc_ShipAddr3], T1.[Calc_ShipCity], T1.[Calc_ShipCountry], T1.[Calc_ShipState], T1.[Calc_ShipToNum], T1.[Calc_ShipZip], T1.[Calc_SortReqDte], T1.[Calc_XPartNum], T2.[CustNum], T2.[Customer_BTName], T2.[Customer_CustID], T2.[Customer_Name]
, T2.[CommercialInvoice]
 FROM JobShip_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.CustNum = T2.[CustNum]"

Nothing seems to have changed.

Being on Epicors Cloud I have zero access to the server.

Is there a way to do that through Epicor?

Just noticed this.

In your RDD you link from JobShip to OrderHed by Company = Company & OrderNum = OrderNum. When in your SSRS report you link with Company = Company & CustNum = CustNum

1 Like

The GUID created for the main report (which can be found in the SysMonitor), will be the same for the subreports. So open the Sub report in Report Builder, run it and enter the parameters.

You’re not adding the Customer table to the RDD. And BTName is not a native field in OrderHed. So the actual fieldname in the temp dataset will probably be something like Calc_BTName, or OrderHed_BTName

Darn, I was so excited when you pointed that out, but it didn’t seem to help. Query now:

Summary
="SELECT T1.Company,T1.OrderLine,T1.OrderNum,T1.OrderRelNum,T1.ReqDate,T1.SellingJobQty,T1.SellingReqQty,T1.SellingStockQty,T1.Calc_AssemblySeq,T1.Calc_DispStatus,T1.Calc_JobNum,T1.Calc_ShipTo,T1.Calc_ShipVia,T1.Calc_UOM,T1.Calc_WhseCode
, T1.[Calc_PartNum], T1.[Calc_POLineNum], T1.[Calc_PONum], T1.[Calc_RPTUSERID], T1.[Calc_ShipAddr1], T1.[Calc_ShipAddr2], T1.[Calc_ShipAddr3], T1.[Calc_ShipCity], T1.[Calc_ShipCountry], T1.[Calc_ShipState], T1.[Calc_ShipToNum], T1.[Calc_ShipZip], T1.[Calc_SortReqDte], T1.[Calc_XPartNum], T2.[CustNum], T2.[Customer_BTName], T2.[Customer_CustID], T2.[Customer_Name], T2.OrderNum,
, T2.[CommercialInvoice]
 FROM JobShip_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum"

Not sure what you mean by this? Does it involve using this disabled button?

Thats why I named it Customer_BTName – I saw elsewhere on these forums that was the right way, that you do T#.LinkedTable_Field

Is that not right?

Look for column named File Name. If it’s not visible, you may have to personalize the SysMon form to show it.

The GUID is what’s in the box.

Also, set the archive period for at least a day, so that temp table isn’t purged while you’re using it for debugging.

The GUID is over on the right end of all the columns. column heading File Name

Does SaaS MT give you access to the SSRS Server Logs folder? It has all the info you seek :slight_smile:

image

:laughing:

1 Like

@ckrusen Where do I put this in the report builder?

Does SaaS MT give you access to the SSRS Server Logs folder? It has all the info you seek

@hasokeric No, but if I begged Epicor they might look at it for me and give me the error message…

You do realize I can get you Epicor On-Prem running as good as MT for 1000$ server. Whats the hold up, switch today and live happy :slight_smile: DELL-T310 or T710 will suffice.

People. The source of all life’s miseries… and joys, I suppose.

I don’t understand how to do this – don’t I need a connection to the report server to do that?

When you go to preview the report in report builder you will be prompted with a parameter asking for the GUID. This is where you can supply the value you found in the System Monitor for when you ran your report in Epicor.