Add a field to SSRS RDL report dataset

I was asked to add the Tracking number from a Shipment to the printed Packer.

First I opened up the packer RDL to see if maybe that field was already in the data set, and just not displayed on the form. It was not.

Next I copied the “PackSlip” RDD to make an RDD with the ShipHead.TrackingNumber field un-exclude . Only to find that field is already not excluded.

Also, I noticed that the packslip RDL dataset refers to “ShipHeadShipDtl” - which is the name of a relationship in the RDD. I’m okay with the RDD creating a single view in the dataset (vs a table for each table making up the RDD).

How to add the ShipHead.TrackingNumber field in the RDL file?
I tried the “Add calculated field and editing the dataset query” method, but no luck.

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

3 Likes

The help for 10.1.400 goes into detail about adding the field to the RDL based report by:

  1. In the Report Data pane, expand Datasets, right-click the OrderHed dataset and select Dataset Properties.

  2. In the Dataset Properties dialog box, verify that Query is selected on the left, and then click the fx button under Query on the right. The Expression dialog box opens.

  3. Add the OrderDate field to the expression:
    a. Resize the dialog box to make the expression (SQL query) in Set expression for: Query.Command.Text easier for you to read.

    b. In the expression, find T1.Calc_HeadComment, and immediately following that field type T1.OrderDate, (be sure to include the ending comma).

    In this expression, T1 is the alias for OrderHed table.

    c. Click OK to save your change and close the Expression dialog box.

Pressing the “Synch Dataset” in Report Style Maint always gives me the he-be-jee-jeebs.

Add T1.TrackingNumber to the report Query…

Then add Query Field to DataSet…

1 Like

That is exactly what I did.

Hmmmm … I might have been too quick with my click and selected “Add Calculated field…” by mistake.

Thanks much better way than this youtube clip!

Good Morning All,

Hope you are keeping well. - I found this trend and it had helps a lot with what I need to do… The main part of this is adding a Field to an SSRS report…

I have followed this and have come to a problem, when I when to Sync the Dataset, I got the below error.

On there Server in the Custom Reports folder there are two SSRS Report.

SSRS - Display

If anyone can help it would be great…

When uploading a report, you need to point to where the “reports” folder is on your local system.

Don’t browse to “…/reports/CustomReports/PackingSlip”, just point to “…/reports”

And it looks like you downloaded a report and made a local copy. I’m guessing this because the upload process thinks the appropriate file is named “PackSlipInvoice”, and you want it to be “PackSlip_test_updated”

Thank you for getting back to me - I will look a this and see what can be done…

The Report Data Definition was changed and when I when to “Sync Dataset” is when I getting the error.

All of the Reports are Server based, none are local.

The “Sync Dataset” function really shouldn’t be used for customized versions of built-in reports. Only for BAQ Reports (and sparingly there).

The Sync Dataset basically re-writes your RDL, updating all the data sources and queries, often renaming a lot of fields. It breaks reports more often than it fixes them.

Thank you for that… Got it to work now… :slight_smile: