I have created a new dataset for my job picklist. I now want to add that dataset to a new version of the report. I cannot seem to find where the dataset is.
Thanks in advance for your help.
RDD? Is that what you created?
I donât know. I used Report data def, copied job picklist to a new name, added some tables and relationships and saved it as type SQL Server Reporting Services. So now within Report Builder, I cannot browse and find the report.
The next step is to Copy Report Style in Report Style Maintenance. This will create the SSRS report in the Custom Reports folder that you are looking for.
You should have left the RDD as type Base Definition.
Then in Report Style, dup it like @bmgarver said, and change then RDD to be the one you created. The RDL you want to edit is in the custom reports folder
So far so good. But where do I define which fields I want from the linked tables. The new report runs fine without errors, but I cannot find where to choose fields from the new data definition.
You have to add them manually. For real. 
Search the online help for âDeveloping a Modified Version of an Epicor Shipped SSRS Reportâ Itâs a step by step example.
Specifically:
1 Like
GREAT! Exactly what I was looking forâŚ
Well, that part would be easy if it was coming from the same table. Somewhere I have to create a join of the shiphead table in the expression? This is the part that is missing in the basic instructions.
No join needed in the report. Doing it in the RDD makes the ShipHead.TrackingNum a field in the ARForm data set in the report. Think of the RDD as a data view. It combines all those tables in the RDD into one dataset, named ARForm. Actually, several datasets but the others are âoverheadâ (Company and RptParameter).
The fields of the added table should be part of âT1â in the dataset expression. So just do steps 3 - 6 below.
- In the Report Data pane, expand Datasets, right-click the OrderHed dataset and select Dataset Properties.
- 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.
- 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. - Back at the Dataset Properties dialog box, add OrderDate to the dataset:
a. Select Fields on the left.
b. On the right, click Add to create a new row at the bottom of the field list.
c. In the new row, type OrderDate for both Field Name and Field Source.
d. Click OK to add the new field and close the Dataset Properties dialog box.
Yes, I did all of that but added TrackingNumber - T1.TrackingNumber (not working) - is it supposed to be OrderDate?
Query execution failed for dataset âARFormâ. â> System.Data.SqlClient.SqlException: Invalid column name âTrackingNumberâ.
OrderDate is just from the example from the Help.
The name for the field to add can get tricky, especially if the same filed name exists in different tables in the RDD. Sometimes the fieldname needs to have the table name as a âprefixâ. For example, to reference InvcDtl.InvoiceNum in the report, the field is named InvcDtl_InvoiceNum. Because InvoiceNum is already a field from another table.
I added the OrderHed table to the ARForm RDD, to include two UD fields (JE_JobNum_c and JE_CustNum_c) and only had to make the following changes to the report:
- Update the ARForm dataset expression, inserting the following:
I donât recall how I knew to use T2 instead of T1.
- Add the fieldsâŚ
Yes, thatâs what I had already completed and then retraced my steps after your email. Should the Shiphead table be showing? I understand the full dataset concept. But then there is this post
I have a version of the ARForm RDD (mine is ARForm_001a), which has the following 23 tables:
The report that uses that dataset shows just 3 âtablesâ:
Expanding the ARForm âtableâ shows fields from the various tables in the RDD
(some show the âprefixedâ field name I mentioned earlier)
Just to update you - I connected with a report professional - they had the same issue I was having⌠so, I was doing everything correctly.
All set - learned more. Thank you for staying with me trying to resolve my issues. I actually didnât do to bad for learning all myself. Thanks again! Iâm sure Iâll be posting more when I get stuck!
1 Like
âWe gain wisdom from experience, and experience from mistakes. So if you expect to become wise, expect mistakesâ
Ok, new report - a modified packlist. I did not need to add any tables this time. Just unexcluded the field selling quantity. Saved my RDD - the field was there in my report. I added it to my report. I get this error "The group expression for the grouping "PackNumâ refers to the field âPackNumâ Report item expressions can only refer to fields within the current dataset scope. Letters in the names of the fields must use the correct case.
The field was added by the system so I did not have to type any fields. Any ideas on this one? I just need the quanity ordered in my packlist.
Did you use the âSync dataâ button on Report Style? If you did (which you shouldnât) it totally changes the structure of the reports dataset.
So it might have changed the name, or prefixed it with a table name.
Which field(s) from which table did you un-exclude in the Packslip RDD?
Edit
Whoops just saw that you said sellingqty.
Iâm surprised that wasnât already in the data set.
Is there documentation on what is and isnât in each standard dataset?