Broken OrderAck Report

jorel · July 7, 2020, 2:19pm

I attempted to change the Sales Order Acknowledgement report in Report Builder in our test environment. I attempted to add a new logo (png). I then was going to add the Entry Person instead of the Sales Person to the form. To do this, I created a new Report Data Definition and unexcluded the Entry Person column. Now when I test the report I get “Query execution failed for dataset ‘OrderHed’. —> System.Data.SqlClient.SqlException: Invalid object name ‘Customer_572c7ef8847243bdaa839847a2ff7911’.”

To try to fix this, I switched back to the unchanged RDD that works for the default Report Style. I also removed the image that I added and any changes I made in report builder and I am still receiving the error. Why is it still occurring?

ckrusen · July 7, 2020, 2:37pm

Did you alter the dataset’s Query expression, adding the new field(EntryPerson), and add the field to the dataset too?

One other thing…

The query expression is a string. And while most C# expressions can span multiple lines (have line breaks in them), string literals cannot.

For example, the following will not work:

= "SELECT Col1.T1, Col2.T1
FROM Customer_"+GUID+" as T1"

(note there’s a line break after Col2.T1)

Because you cannot have a string literal have a line break in it. Either of the following will work:

= "SELECT Col1.T1, Col2.T1"
+ " FROM Customer_"+GUID+" as T1"

(note the need for a space either after Col2.T1 or before FROM. so that it doesn’t make Col2.T1FROM
or

= "SELECT Col1.T1, Col2.T1 FROM Customer_" + GUID + " as T1"

(that’s all on one line)

jorel · July 7, 2020, 2:42pm

I have not touched the Query expression for this report which is what I find odd. I only added the image. Now, even when I use the unchanged system data definition, it no longer works. I want to get it working without the changes again as I am unsure what has changed before attempting to modify the report further.

When I go to add the Entry Person field I will definitely keep this in mind.

ckrusen · July 7, 2020, 2:56pm

The OrderAck report has a notoriously long Query Expression. So long that it can sometimes exceed the max length allowed by the Report Builder’s editor. So just opening the Query Expression to view it, may not load the entire expression. Then if you use the Ok button to close it, it might get updated with the truncated query. This probably didn’t happen to you. But has to other people (after they’ve made earlier edits to it).

The steps to modify the built-in report are:

Duplicate the report style in Report Style Maint.
Duplicate the RDD (ex: OrderAck_001)
Edit RDD OrderAck_001, un-hiding the desired field
Open the copy of the OrderAck RDL created in step 1. It should be in the reports/CustomReports/SalesOrderAcknoledgment “folder”.
Edit the query expression for the appropriate dataset (probably OrderHed). Adding the the new field to the expression. Usually you find the last item in the SELECT section for the table the field is in. Typically, the table is T1. So change
....T1.SomeFieldFromOrderHed, T2.SomeOrdDtlField, ... to:
....T1.SomeFieldFromOrderHed, T1.EntryPerson, T2.SomeOrdDtlField, ...
Add the query field to the dataset. This is different than adding it to the expression. In the data set properties window, there should be a tab for Fields, Add a Query Field. Give it the same name and reference that was added in step 5. Ex:[EntryPerson]
back in the Report Style Maint, set the source of the RDD on the new style you made to OrderAck_001

jorel · July 7, 2020, 3:09pm

The truncating of the original Query expression could very well be a possibility, but if I expand the Query expression window it seems to be the full query. How do I see the original query? I have already uploaded this broken report to the Custom reports, so now when I make a copy of the original system report in copies to the same place as the other broken custom reports. Therefore, I can only see this broken edited version.

jorel · July 7, 2020, 3:28pm

I have attempted to increase the Archive period greater than 0 days and checked to make sure that the TableGUID values are not set to default in the Report Server settings for SOForm or any of the subreports.

I went back into live to test the same report that I was attempting to modify. It still worked fine, so I downloaded the SOForm with all of the subreports from the live report server. Then, I uploaded them into the test environment for the same custom report and it still gives me the same error. This has to mean that it is not the RDL causing the problem then.

ckrusen · July 7, 2020, 3:28pm

In the Query Editor window (with the cursor in the editor box), hit CTRL+END.

When I do this in the original SOForm RDL, I see:

Make sure yours ends with T4.RptLanguageID"

Another way is to hit CTRL+A to select all, then Copy and paste that into Notepad.

Hmmm… I did the copy and paste (of the original Query expression) into Notepad, and see that it already has line breaks in it.

That should be one single line. Or the breaks should be after the string literal is closed, like:

jorel · July 7, 2020, 3:47pm

This is a custom report that I believe a consultant had done so it is a little different so it does not end in T4.RptLanguageID.

This is what the query appears as in Notepad++. Like yours it appears there are already line breaks:

Since the last LEFT OUTER JOIN is not on its own line, I added a line break to make it similar to the others. I then copied and pasted it into the Query expression and I am still getting errors. It is odd to me that this rdl works in live and not in test. Surely something else has to be the issue. What could that be?

In normal Notepad, it appears as one line with no breaks.

ckrusen · July 7, 2020, 3:51pm

Don’t get too far involved in the part about line breaks just yet.

That report appears to be use a customized RDD. The RDD specified for that report style is the one you need to copy for your new style (the one that will have the Entry person and images added).

jorel · July 7, 2020, 3:57pm

Wow. Turns out this whole time I had selected the wrong RDD. I could have sworn I knew which definition this report was using. It turns out I was dead wrong. That took me way too long to figure out, but thank you very much for helping me along the way like always Calvin!