SSRS Job Trav - RawMaterialComponents

Epicor ERP 10
1

Hello everyone. I want to add the PlantWhse.PrimBin in the Job Trav Raw Material Components sub report. I have the RDD setup with the relationship. I don’t know what the next steps are to go further. How do I get this table to show up in the subreport? Or do I need to modify the sql query and add a new query field? I am not certain of the syntax for that either. Any ideas or direction would be greatly appreciated!

Thanks,
Koecher Vue
M-B Companies, Inc.

2

Here is a post of mine on another forum that helped me modifying the dataset for existing SSRS reports.

http://erp.ittoolbox.com/groups/technical-functional/epicor-l/e10-rdd-with-adding-new-tables-to-the-report-definition-and-syncing-the-style-tables-do-not-display-in-dataset-6024238#M6027067

3

I know it sounds painful as well, but I ended up recreating the whole job traveler report and its subreports as the time wasted on modifying the existing one and the changes my end users wanted to see where far to difficult with the existing reports. Just an FYI if you have future changes to this report.

4

Thanks for the tips.

5

Simply put yes, you will need to add this to query inside the sub report. I would do the following if I was adding a field to the sub report:

  1. Duplicate the RDD and add the Table and Field there ( also add the relationship)

  2. Create a duplicate sub report for the job traveler

  3. create a new report style that points to the report with the new sub report and new RDD

  4. Run the report with the new RDD to get a data set with the new field in it

  5. Go to the report and modify the data set
    when I modify the data set I’m first adding the table to the data set down in the linking portion at the bottom. this part can look confusing as it is using alias tables but basically you are going to go to the end and put T# LEFT OUTER JOIN NameOfYourTable_" + Parameters!TableGuid.Value + " T# ON T#.FieldYourLinking = T#.FieldYourLinking AND T#.FieldYourLinking = T#.FieldYourLinking . - now the T# is just generic and there are two different T# in that example so that is kind of confusing so one is your new T# which is just a T and which ever number is the next in line, you just using this as an alias so just make this the next in line, if the query has Part in it and it says PART is T1 then make yours T2 and so on. then when you do your linking use the T numbered aliases to do your linking so if you have part as T1 and PartWhse as T2 for example and you trying to link those it would be T1.Partnum = T2PartNum and T1.Company = T2.Company.

  6. Then you’ll also have to add your Field into the they query as well, you’ll add this into the same place using the same T# alias, you can add this right on the end, just don’t put a comma after it if you put it as the last value before the linking FYI. T#.PrimBin

  7. Then you would add the field still inside the Query properties Fields portions.

hope this helps.