Trouble adding table to sub report in Job traveler

Evan_Purdy · November 18, 2020, 1:31pm

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.

Michael_Ramsey · November 18, 2020, 2:11pm

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.

cfinley · November 18, 2020, 2:14pm

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.

cfinley · November 18, 2020, 2:16pm

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

Evan_Purdy · November 18, 2020, 4:06pm

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?

cfinley · November 18, 2020, 4:12pm

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

ckrusen · November 18, 2020, 4:14pm

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.

ckrusen · November 18, 2020, 4:29pm

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

Evan_Purdy · November 18, 2020, 4:30pm

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"

Evan_Purdy · November 18, 2020, 4:33pm

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

Evan_Purdy · November 18, 2020, 4:35pm

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?

ckrusen · November 18, 2020, 4:36pm

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.

cfinley · November 18, 2020, 4:37pm

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

hkeric.wci · November 18, 2020, 4:41pm

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

ckrusen · November 18, 2020, 4:43pm

Evan_Purdy · November 18, 2020, 4:54pm

@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

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

hkeric.wci · November 18, 2020, 4:56pm

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 DELL-T310 or T710 will suffice.

Evan_Purdy · November 18, 2020, 5:02pm

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

Evan_Purdy · November 18, 2020, 5:10pm

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

cfinley · November 18, 2020, 5:17pm

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.