SSRS Production Detail Report - missing group?

Something interesting that I’m seeing with the production detail report. I make a copy of the default SSRS style, make a copy of the default definition. Change the data definition to include a field from the JobHead table that was previously excluded (SchedCode in my case). Sync the data set. Open the report via job tracker for a single job.

Data starts getting duplicated in the Operations section: everything in Group 8, which is grouped by OprSeq. And of course it’s only some jobs - I haven’t found what data is causing the duplication.

I’m at a loss of how or why tweaking the dataset like this can have this effect and I’m not sure what to try. Any ideas?

You shouldn’t ever click the “Sync Dataset” button in Report Style Maintenance for RDD-based reports. Most reports don’t use the full RDD dataset in the same way it’s built (in terms of relationships between tables), and you should add any tables and fields you added in RDD maintenance using the SQL query phrase in the RDL dataset.

I’m willing to bet the original RDL doesn’t include something like JobOprDtl records and now that the dataset was synced, it’s being included and causing the duplicates.

1 Like

Interesting. I guess I don’t understand the RDD vs RDL stuff. I actually cannot add my new field to the report until I sync the dataset – it gives an error.

The Value expression for the text box ‘Textbox1’ refers to the field ‘SchedCode’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

If I close Report Builder, Sync the DataSet for the report style, reopen Report Builder – I can add my field and save. However, that’s when I get the report with duplicated data – it’s now 14 pages.

Steps to duplicate:

· In Report Data Definition maintenance, make a copy of ProdDtl, change field JobHead.SchedCode from excluded to included

· In Report Style, make a copy of Production Detail Report, change Data Definition to match the one created in previous step

o Generate this report from Job Tracker for a given job – it should render just like the default report

· Open ProdDtl created from previous step in Report Builder.

· Attempt to add textbox with Fields!SchedCode.Value and save – you should see the error

o The Value expression for the text box ‘Textbox1’ refers to the field ‘SchedCode’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

· Close Report Builder, click Sync Dataset in Report Style Maintenance, open ProdDtl in Report Builder and reattempt to add textbox with Fields!SchedCode.Value. No error upon saving

o Generate this report from Job Tracker for a given job – for some jobs it duplicates Scheduling Resources multiple times

- Steve

See below for a more complete step-by-step. After you make the copy of the style and change the Data Definition to your customized version, you need to manually edit the SQL statement the dataset inside the RDL uses. Then you’ll be able to add your textbox. The Sync Dataset button tries to do this automatically, but a lot of system reports don’t follow the RDD schema to the T, so Epicor ends up clobbering the RDL datasets.

2 Likes

You nailed it Tyler - that fixed it. Not intuitive at all though - I would’ve thought the Epicor interface was the answer to all of life’s problems, but alas, I’ve been let down.

Seriously - thank you. I spent more time on this than I’d care to admit. :smiley: