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:
- Open the Data Definition you want to customize and then choose Actions-> Duplicate
- 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)
- Now, go to the Report Table-> List Tab and Select the Table which will be the Parent to you added table.
- Note how the Exclusions Tab lists all the fields from this table and the ones you want in your output are UNCHECKED.
- Next, go to the Linked Tables Tab and review the Picked and Available zLookupLinks.
- Select the Linkable Field then go to the Description Fields Tab and Select it from the drop down.
- Then you can choose the fields you want to include and you don’t have to add a new table or relationship at all.
- Sometime though, the field you want to add isn’t available through the zLookupLink. So you will have to add the table.
- 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)
- 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.
- 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.
- Click Save
- Go to the exclusions tab and you will see it is empty:
- 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!)
- Below is a Template you can use just replace the <XXX> with the appropriate indicated value
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')
- 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)
- Here is an example of the query in SQL Management Studio
- Click Execute and the number of “rows affected” is equal to the number of columns in your table
- Now go back to the Exclusions tab and click the refresh bottom. You will now see the list of fields from your added table.
- 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.