SSRS added field has no data

Kinetic ERP
1

We would like to display the name of person who last changed a quote (QuoteHed.ChangedBy). The table joins seem to work, but the field is empty when the report is previewed. Have done the following:

  1. Created a copy of the base RDD
  2. Linked the custom version of the report to the new RDD
  3. In the new RDD,
    a. Added UserFile as a data source
    b. Unexcluded the necessary fields:

image
image714Ɨ278 52.8 KB

c. Created the relationship between QuoteHed and UserFile

image

  1. In the RDL Quote Dataset Properties,
    a. Added UserFile_Name to the Field List:

image
image979Ɨ374 73.6 KB

b. Added the table to the query FROM statement:

image
image979Ɨ410 108 KB

c. Added the field to the query SELECT statement:

image
image979Ɨ156 31 KB

  1. In the field expression, tried two expressions, neither of which returned a value:
    a. =First(Fields!UserFile_Name.Value, ā€œQuoteā€)
    b. =Fields!UserFile_Name.Value

image

Hope I am missing something easy. I have followed this process with other tables in other RDDs and it seems to workā€¦

Thanks.

1 Like
2

That looks pretty good. Try making up a BAQ to prove that you have a value to display.

3

Did that. Have values in both Name and CurrComp.

One thing that I notices ā€” this is Erp.UserFile. there is also Ice.UserFile. Is there a difference? That is my next thing to try if I havenā€™t missed something in the process.

4

I think that Erp.UserFile is the right one.

5

Change your ā€˜Field Sourceā€™ from ā€˜UserFile_Nameā€™ to ā€˜Nameā€™

2 Likes
6

Update ā€“ not really much of one. I submitted it to Epicor and someone at least looked at it before they did the usual ā€œyou can pay us to tell you whatā€™s wrong.ā€

The only hint I got was that the case was retitled ā€œQM What expression to use to include added table and fields, custom QuotForm, in Report Builder?ā€ so I think the problem is in the expression itself. Will keep looking at that.

They also asked for the .xml version and the correct field IS there. Going to see if I can make sense of that.

Will update this further if I figure it out.

7

As Brandon pointed out, your field source is wrong. In the query youā€™re just calling it ā€œNameā€ from the T4 table, then in the Fields list youā€™re asking it to pull a column called ā€œUserFile_Nameā€ which doesnā€™t exist.

I would also change the join - company is almost always required to ensure that you pull the correct data, and even in single company to ensure you hit the sql index for performance reasons. In this case you donā€™t want to link to CurComp, if it is/was multi company you would only get a name back some of the time depending which company the user is working in. So just join on ChnagedBy and DcdUserID.

1 Like
8

@markdamen, @SpeedOfLight

Thanks so much, both of you. Changing to ā€œNameā€ in all three places (query, data source and expression) put the actual name on the base Quote form.

We have only one company, so I did not change the join on CurComp.

Still not sure why other fields (QuoteDtl_QuoteComment, for example) do include the table name, but that is a task for another day.

Thanks again.

9

Glad I could help out and you got it working.

10

It will include the table name either if itā€™s being pulled through a Linked table, or if the field names clash. Both QuoteHed and QuoteDtl have a QuoteComment field.

1 Like