PO Box 1128 Yorktown Heights, NY 10598
+1 914-407-2442
+1 914-407-2432

Epicor 10 | Adding a Table to a RDD

There is a nice little “Feature” in Epicor 10.0/10.1 that can really hose up your modified Report Data Definitions. When you add a table to the definition and then create the table relationships, you will notice that the Exclusions list doesn’t have any of the fields listed.  On the surface, this is great because that means you get ALL of the fields in your output and you can add whatever you want to your report.

The bad news is, that there is a limit to the number of fields that can be output by the RDD.

But don’t fret! We have the answer – follow the steps below:

  1. Open the Data Definition you want to customize and then choose Actions-> Duplicate
    1. Create Duplicate RDD
  2. Change the Report Type to “SQL Server Reporting” – while this isn’t required, it helps separate your custom RDDs from the system deployed versions (those all say Base)
    1. Ensure the report type is SSRS
  3. Now, go to the Report Table-> List Tab and Select the Table which will be the Parent to you added table.
    1. Find your parent table
  4. Note how the Exclusions Tab lists all the fields from this table and the ones you want in your output are UNCHECKED.
    1. Review exclusions tab
  5. Next, go to the Linked Tables Tab and review the Picked and Available zLookupLinks.
    1. Review linked tables
  6. Select the Linkable Field then go to the Description Fields Tab and Select it from the drop down.
    1. Picking available links
  7. Then you can choose the fields you want to include and you don’t have to add a new table or relationship at all.
    1. Evaluate not adding tables but links
  8. Sometime though, the field you want to add isn’t available through the zLookupLink. So you will have to add the table.
  9. Click On New -> Table then in the Report Table field, type the Name of the Table you want to add (*In 10.1, if you right click it will give you a search option which will make the rest of this instruction unnecessary – but if you forget and key in the table name, you will have to do the rest of this stuff)
  10. After getting the Report Table field filled in, click the Schema Table button and search for the table name again – don’t ask why I’ll make you put money in the swear jar.
  11. Add a SeqControl Number – I always start my added tables at 900. This makes it obvious that it was added and not part of the base.
  12. Click Save
    1. Adding a custom table and sequence
  13. Go to the exclusions tab and you will see it is empty:
    1. Wait where are the field exclusions
  14. Now – the fun part.  Open SQL Server Manager and start a new query using your Test ERP Database. (Always use Test first I’m only going to say it once!)
  15. Below is a Template you can use just replace the <XXX> with the appropriate indicated value
    1. INSERT INTO ICE.RptExclude
      (RptDefID, RptTableID,SystemCode,ZDataTableID,ZFieldName,SystemFlag,SysRowID,ExcludeColumn,ExcludeLabel)</li>
      SELECT  '<YOUR RDD NAME>','<TABLE ID>','<ERP or ICE>','<TABLE NAME>',COLUMN_NAME,0, NEWID(),1,1</li>
      FROM INFORMATION_SCHEMA.COLUMNS</li>
      WHERE TABLE_NAME = '<TABLE NAME>;' AND TABLE_SCHEMA='<ERP or ICE>' AND COLUMN_NAME NOT IN ('Company','<PRIMARY KEY>','<PRIMARY KEY 2>','SysRowID','SysRevID')
      
  16. Add additional Primary Keys as needed depending on the table, you do NOT want to Exclude those columns as they will be used for Linking. The Data Dictionary Viewer will tell you the Primary Keys (it’s the first index in the list)
    1. Find primary keys from new parent child relationship
  17. Here is an example of the query in SQL Management Studio
    1. The query to add exclusions automatically
  18. Click Execute and the number of “rows affected” is equal to the number of columns in your table
    1. Review the affected rows from the query
  19. Now go back to the Exclusions tab and click the refresh bottom. You will now see the list of fields from your added table.
    1. All of the exclusions are now there
  20. All that’s left to do is uncheck the Exclude box on the fields you want to add to your report and add the table relationship.
    1. Pick the fields you would now like to include explicitly

Related Posts

Leave a reply