TxtShipToAddress2 is the name of the container text box. I haven’t gotten this error before; I really don’t know what is going on. Any help is appreciated.
That error is coming from the textbox’s data context. Say for example if it’s in a matrix, the matrix will have a dataset selection, and that’s the default data context that objects in the matrix will read from.
If the textbox is in an aggregated part of the data scope, like the customer name if there is a subgroup returning all the customer’s orders, the textbox’s value expression would need to do something like First(dataset.customername) because it may see many rows and a textbox can only display a scalar value.
If the referenced field is in a dataset that the textbox doesn’t have default access to, one of the lookup functions seem like a next step, and consider if it’ll need to do some kind of aggregate handling like first() or sum() etc.
What is the actual expression of the fields you’re adding vs existing? When I get this error it’s because the existing ones have like a FIRST() type syntax and mine does not.
Well, it is a little complicated the way you are trying it but will try to explain myself here.
If you added the fields to an existing table on the Data Definition, you just need to modify the query within the BOL dataset (the one the epicor report is using already), then you add them on the Fields list within that dataset.
You can do what you are trying - adding a dataset specific for OrderHed table, but then you need to use an aggregate function (First, Max, or a good one for these situations, LOOKUP).
With LOOKUP, you can build an expression where you will link the main dataset used on the report table, to get one specific record from the OrderHed dataset.
I would only use LOOKUP when strictly necessary, as even though I know how to make them work, I am unsure if it has any kind of performance impact, as it should be calculating this at runtime.
Hope this helps, and happy to explain in more detail if required.
There should be a log on your SSRS server that shows you the detailed part of this and includes the fields that the query in your RDL is trying to select, that don’t exist in the table that’s being formed from your RDD— which is probably why this is failing.
If you can find the log files you can poke around at them, copy them to a different location to inspect them. There’s like 4 types of logs so you’ll have to find the right one, but open up each one accoridng to the relevant time stamp on them, like generate your error then go search the logs quick.
Ctrl + F “BOL” and that should get you to the error in the log and then you can find the rest of the error abotu a field that doesn’t exist. They are SQL errors more or less.
Run the report
Set the archive to a day (to get a Table guid and for the tables to stick around for a bit)
open the rpt in Report Builder
run the report in Report Builder with the Table guid (this saves time of rerunning from Epicor)
You can even test that sql out as well if you want.
@tmayfield, not saying you have to, but would you care to share what log file you found it in or a little more detail in case someone else stumbles on this thread?
