SSRS - Multi-Company - POForm - BillTo Address

Kinetic ERP
1

I am trying to get the Bill To address from one Company - A to use on the POForm for Company B and C.

We are a multi-site company wanting to use BillTo from the headquarters and each plant having their own company and site/plant address.

When I modify the Query in Dataset Properties for POHeader in SSRS as shown below, SSRS doesn’t complain and the report style seems to upload without errors, but then when I run the POForm report doesn’t return any information.

It never generates the report and open a new tab.

I added/Modified this at end of existing query

C2.Name AS BillToAlt_Name,
C2.Address1 AS BillToAlt_Address1,
C2.Address2 AS BillToAlt_Address2,
C2.City AS BillToAlt_City,
C2.State AS BillToAlt_State,
C2.Zip AS BillToAlt_Zip,
C2.Country AS BillToAlt_Country
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
LEFT OUTER JOIN Erp.Company C2
ON C2.Company = ‘Comp-A’"

I have written a BAQ that can access this information, but the SSRS report itself doesn’t seem to want to display.

Any ideas?

Thanks,
Steve

2

Why not forget about adjusting the dataset and just create a custom PO form with the Bill To address coded in the form?

3

Would it be easier to just use straight up text fields instead of using field values at all? Since they’re all using the same, just hard-code it.

4

I will be using this BillTo Address on multiple forms, so was hoping to get it from Company A address, so that I don’t have to hardcode it.

If the address for Company A ever changes, then I would need to go back to multiple forms and update them again.

5

If the company generating the SSRS report is different than the “Bill To” Company, it’s not gtoing to find the other company record just joining it in the DataSet Query the way you have. The company table linked in the RDD will need to match the join, and I’m not sure you can do a join with a hard-coded value in the RDD.

6

If you really don’t want to hard-code it, and it has to be a separate address from the Company address, there are a couple workarounds you could use.

  • Add Custom Bill To Address fields to the POHeader_UD table, and set the default values to your address (or populate via BPM).
  • Add the Bill To Address as a single, tilde-separated string in Company.AuxMailAddr, then use the split function in SSRS to get the values.