Adding a table to a SSRS dataset

Epicor ERP 10
1

Hopefully someone here can help me resolve this mystery.
We are trying to add a table and field to the standard ARForm data definition in Epicor 10.1.600.13. Our issue is that the table does not exist in the SSRS Dataset.

Here are the steps we took. Has anyone run into this issue, and do you have a solution. Thank you in advance.

  1. Open System Management > Reporting > Report Style
  2. In the Report ID field enter ARForm and press the tab key
  3. Select the Standard – SSRS report and from the main menu select Actions > Copy Report Style
  4. When prompted for the report ID and path, change the report ID to ARInvoiceFormErgoTrackNum and ARPBFormErgoTrackNum (I guess the ARB is a sub-report)
  5. Right-click the Data Definition and select Open With > Report Data Definition
  6. From the main menu select Actions > Duplicate Report
  7. Change the report code from ARForm to ARSSRSTRACK
  8. Enter a description
  9. Set the report type to SQL Server Reporting
  10. From the main menu click the drop-down arrow next to the new record icon and select New Table
  11. Add the ShipHead table
  12. From the main menu click the drop-down arrow next to the new record icon and select New Relationship
  13. Add the following relationship:
  • Relation = InvcHead2ShipHead
  • Description = InvcHead2ShipHead
  • Parent = InvcHead
  • Key = PK_InvcHead
  • Child = ShipHead
  • Relation Type = Definition Only
  • Relationship Parent Field = Company to Child Field Company
  • Relationship Parent Field = Calc_PackNum to PackNum
  1. Click the Ship Head table and select the Exclusions tab.
  2. Exclude all fields and labels except for Company, PackNum and TrackingNumber
  3. Click the save button and close Report Data Definition after making note of the new DD ID
  4. Open Report Style Maintenance again, click the new report and enter the new DD ID in the Data Definition field
  5. Click save then click the Sync Dataset button
  6. Open SQL Server Reporting Services Manager and navigate to Epicor > Reports > Custom Reports > ARInvoiceFormErgoTrackNum
  7. Click the drop-down arrow next to ARForm and select Edit with Report Builder
  8. When Report Builder 2014 opens, expand the Datasets object and look for the ShipHead Dataset, which does not exist.
1 Like
2

You have to manually add this join to the SSRS DataSet. So your table is in the RDD but you need to bring it into the SQL Dataset by modifying the dynamic dataset in SSRS (SQL statement)

https://epicweb.epicor.com/Education/OnlineHelpFeatureSummary/Epicor%20ERP%2010/10.1.500/Help/enu/Standard/CoursesToolsReport/ssrs.epicorshippedreports.html

2 Likes
3

Also make sure that Relation Type is Output. Click Sync again.

Another thing I have noticed is that sometimes if you don’t see the fields in a separate dataset is because the fields where added inside the main dataset of the report.

It drove me crazy more than one time haha

Hope this helps!

1 Like
4

Hi Jose, thank you for the reply. I’ll give that a try. I was hoping for a more simple solution and functionality like you would find in Crystal Reports. SSRS is new for us and we are struggling through the learning curve.

5

Hi Mario, thanks for the advice. I tried setting the relation type to output but it did not produce the desired results. Also checked if the tracking number field may have been included in the original dataset but it was not. We are adding the ShipHead table to pull in the tracking number for audit reasons.

6

Hi Jose, I read the article you posted but it does not include instructions for adding and linking a table. We have successfully added fields that are included in the RDD but have been suppressed. That part of the sync seems to work.

7

I did the same thing, we needed to add the tracking number. Let me check my settings.

8

This is my Report Style

image
image.png822×404 18.5 KB

This is my RDD

image
image.png795×790 26.9 KB

Also make sure that the columns are not excluded

image
image.png906×810 41.4 KB

These are my datasets
image

What I had to do, once I got it to show up was to do a look up

image