RDD for ARForm Public Cloud

Epicor ERP 10
1

Greetings all,

I have spent the last week ( albeit on and off) trying to add the Tracking Number to the AR Form . As stated in the topic, we are on the Public Cloud I have read all ( well maybe 50 ) of the topics regarding this. None of these topics expressly note that that the method described is ‘Cloud Friendly’ . I have added a Relationship to the RDD to the ShipHead from the InvcDtl( Company to Company, PackNum to PackNum) to a copy of the RDD, at first I stayed WAY CLEAR of the Sync Dataset Button in the Report Style Maint Screen till out of desperation, I tried that too, after that failed to find a sub-form( so the error message said), I deleted the RDD and rebuilt up to where it was before desperation set in. I have also pasted in a left outer join into the Dataset Properties in report builder. That flat out threw a rather nondescript error directing me to check the server logs ( again we are public cloud). I have added the Field --TrackingNumber— to the fields section of the Dataset properties.
One thing I just noticed that in the Data SOURCE properties from Report Builder, it refers to our Cloud Server,/reports/ SharedReportDataSource
image .

Am I pretty much out of luck getting to the RDD I created/modified due to being a cloud customer ??
Do I have to drag Epicor Support into this ? (all though they have been pretty good about adding UD fields to tables as I request them)

I am using a stand alone Report Builder installed locally. It has been working for tweaking reports, moving things around and adding fields that are on the RDD but not on the select statement of the DataSet. ( that article was awesome,( thank you Calvin Krusen).

Am I missin somethin obvious ??? :-o

Dean

2

I’m not at all familiar with the restrictions on Cloud based implementations. (BTW - your profile says “On Prem”)

A possible hack of a solution would be to create a subreport with:

  • An embedded data connection pointing to the Shiphead table of your DB.
  • Input parameters (Company and PackNum).
  • The body of the report would be the size of the txtbox that your main report would have used for the tracking number
  • Only one field (for the TrackNum) in the body, and the field takes up the entire body

You could test that by entering a valid Company and a PackNum.

Back in your main report, insert the subreport and link the subreport’s Company an PackNum inputs to the appropriate fields on the main report.

3

Hi Mr Krusen,

Sorry about the profile, just changed. We have been cloud for just 30 days…
I am not quite up to speed on embedded Data connections, will look it up…

Thanks for quick reply

Dean

4

No need for the “Mr.” :slight_smile:

Not being familiar with cloud based implementations, some of what I say might not work, Especially if you’re Multi-Tenant.

  1. Open up Report Builder an make new report, select Blank Report
  2. In the new blank report, remove the default “Click to add Title” textbox, and Remove the Footer
  3. Right click Data Sources and select “Add Data Source…”
  4. Select to the Embed the datasource
  5. Click the “Build…” button
    image
  6. Enter the name of the DB server (it doesn’t always show in the dropdown list)
  7. Select the Authentication method
  8. Select the DB for the App
  9. Test the connection
  10. close the Connection Properties window
    image
    image428×568 13.6 KB
  11. Close the Data Source Properties window.
  12. Add parameters “Company” (type Text), and “PackNum” (type Int)
  13. Right click Datasets, and select “Add…”
  14. Select Use embedded dataset
  15. Select the data source created in step 4

image
image678×603 12.6 KB

  1. Click the Query Designer button

  2. Add the fields ShipHead.Company, .PackNum, .TarckingNumber

  3. Add a filter for Company

  4. Select the Parameter checkbox

  5. Repeat 18 & 19 for PackNum, then OK to close the window

    image
    image824×638 27.8 KB

  6. Your query should look like:
    image

  7. Insert a textbox, and set its value:
    image

  8. Move the text box to 0,0, and reduce the body of the report to the size of the text box.

Test it out.

You can now insert this as a subreport in your main report. Don’t forget to set the Parameters of the sub-report’s properties.

5

Awesome layout, you must have quite the Library…
I have done stuff like this before but that was on premesis, I think there is more to the connection string than windows/server auth on the cloud…

Dean

6

No, you should be able to modify a copy of the RDD.

Your error message had nothing to do with too many columns, right?

You will NOT be able to link to the SQL server like an On-Prem user.

If you go to the System Monitor and download an XML version of your data, do you get any data or all the data except the tracking number? Temporarily change the report to Crystal. Run the report, make sure to use a longer Archive Period like a Day - find the report in the System Monitor, choose to Download Crystal Report Data as an XML file and see if your data is in there.

image

7

Good Morning Mr Wonsil,

I was hoping that you would respond to this. You seem to be one of the leading authorities on ‘Cloud Operations’. The Error Message had nothing to do with to many columns. I was able to download the XML of the report and there was no data for the Tracking Number( as far as I can tell). I am working through some pretty severe latency issues this morning ( Epicor put out the notice they are experiencing " Performance Degradation" ). I will get back to this issue as time permits. I have a form that blew up and need to address that first.

I do want to thank you and this community for the help they are giving and have given in the past. This site is a HUGE resource for the 1 man band that I am.

Dean

1 Like
8

Hi Dean,

Are you using the Linked Table capability to attach the Tracking Number? Here’s the path on the RDD form:

image
image984×683 40.2 KB

The Linked Tables feature is the easiest way to add data to an RDD if the linkage exists. This is how I would add the tracking number (above). The name will appear as Shiphead_TrackingNumber or something like that. Make sure to add it to your SQL statement in the RDL.

Mark W.

9

Hi Mark,

Here is the Relationship I have set, Invoice Detail to Ship Header, Do I need the Calculated cTrackNum defined under the ShipHead Datasource ?

image
image767×526 30.6 KB
.

The only way I can Get the tracking number to show up on the Available listbox is to select the Invoice Dtl Datasource. Is that correct ?

image
image915×761 45.8 KB

Last Question, Do I have to add a Left join statement on the SQL statement for the RDL ? or just the Select T1< fieldname> ?

Thanks

Dean M

10

Yes, you can have multiple shipments on an invoice so the InvcDtl is the correct table. The Relationship entry is not required. The Pick List will do that for you. No Join required either. You’ll find the field under the InvcDtl set with a name like ShipHead_Packnum or something similar. Once you have that name, you add it to the T1.InvcDtl_ShipHead_Packnum … or whatever the name is…I don’t recall the convention at the moment.

Mark W.

11

Your InvcDtl to ShipHead relationship shows as “Definition”. You probably want “Output” instead.

Definition means Inner Join, Output makes a Left Outer Join

12

Thank you Mark and Calvin,
The last piece of the puzzle is the syntax for the RDL select statement. It is T2.PackNum_TrackingNumber. Also worth noting is that you must use PackNum_TrackingNumber on the Field Source of the Dataset Properties I did leave the Relationship in place on the RDD.

image
image650×669 15 KB

Thanks again

1 Like
13

Calvin,
Sorry for bumping this old thread but I just wanted to say thanks for your step by step tutorial on creating a subreport with an embedded data connection!

Our UK office needs a customized AR Invoice for Brexit which requires the Country of Origin be displayed for every part/invoice line. The ISOrigCountry field is available in the Linked tables of InvcDtl but it contains the country number and not the country description. So I created a subreport to connect to the Country table and grab the Description.
Great hack!
Thanks again,
Kelly

1 Like