Syncing Dataset for QuoteForm Report Style, BREAKS the report, Duplicates Qtys

Epicor ERP 10
,
1

2020-10-23 08_22_59-Window
Refer to the image ^
Why is it that when I’m customizing the QuoteForm (I’ve done the whole process of duplicating the DD and ReportStyle) when i Sync the datasets within Report Styles, it starts duplicating the Quantities (As shown in the image)??
Even when I’ve made zero changes to the original Data Definition and i Sync the datasets, it still breaks the report and duplicates the quantities…
I know i’ve read this issue before, but how am i supposed to bring in new fields without using the Sync Dataset tool?
This has been boggling my mind for the past couple weeks.

I only need to bring in two fields: QSalesRp.SalesRepCode and QuoteHed.TermsCode

2

There are MANY posts on here about how bad it is to use the Sync Dataset button. Especially on Epicor supplied reports. Doing it on BAQ Reports works … sometimes.

  1. Open the RDL
  2. Edit the dataset’s query expression
  3. Add the query fields to the dataset
2 Likes
3

My basic rule is to use the sync button on BAQ reports only. The sync button works on RDD reports but generally the exposed fields in the RDD and the fields in the SSRS report are not aligned and it will break the report.

You will either have to clean up the RDD to align with the SSRS or manually add them to the SSRS.

Ross

2 Likes
4

@keatonskills - since it’s your first time here, I’ll ask if you know how to manually update the RDL’s query expression and add the query fields. If not, I’ll post a link to a topic explaining how to do it.

5

Yeah, I’ve figured out that part, now im just having issues saving the RDL image
I know this error has something to do with the various grouping and stuff in the RDL, but when I drag over my new calculated field that i added to the query expression i get that error.
Thank you for the help so far, i appreciate it.

6

What’s your code in the expression for the Hidden property of Tablix1?

7

Thats something i havent changed at all. There isnt any hidden expression!

8

Be careful when editing the Query Expression. It is made up of a bunch of strings that are added together. But if you accidentally put a linefeed (hit the enter key) in the middle of one of the strings, it’ll break the expression.

For example:

As a single line a query expression might look like:

image
image784×112 2.06 KB

The green parts are literal strings and the black is a variable/parameter

If I add a carriage return before the From - to make it more readable - I get:
image

Now this is broken because it thinks the first string ends at T1.DMRect

The correct thing would be to make it a single line. Or break the string literals with proper opening and closing double quotes, and add the addition sign, like:
image

Make sure to have at least one of these spaces in the sting literals, else it equates to
... T1.DMRectFROM Company_ with no spcae.

9

=“SELECT T1.Calc_TaxMethod, T1.DocQuoteAmt,T1.DocTotalPotential, T1.DocTotalGrossValue, T1.Calc_LineMiscTotal, T1.Calc_HeadMiscTotal, T1.Calc_TotalTaxAmt, T1.DateQuoted,T1.ExpirationDate,T1.QuoteComment,T1.QuoteNum,T1.Reference,T1.Calc_CompanyAddr,T1.Calc_CompFax,T1.Calc_CompPhone,T1.Calc_CurSymbol,T1.Calc_CustContact,T1.Calc_CustContactEMail,T1.Calc_CustFax,T1.Calc_CustPartOpts,T1.Calc_CustPhone,T1.Calc_EMail,T1.Calc_Message1,T1.Calc_Message2,T1.Calc_QuoteAddr,T1.Currency_CurrDesc,T1.Customer_Name,T1.Calc_HasHeadMisc,T1.TermsCode,T2.SalesEngineer_c,T2.SellingExpectedUM_UOMSymbol,T2.Company,T2.DiscountPercent,T2.DocDiscount,T2.DisplaySeq,T2.DrawNum,T2.KitParentLine,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.LeadTime,T2.PartNum,T2.QuoteComment as QuoteDtl_QuoteComment,T2.QuoteLine,T2.QuoteNum as QuoteDtl_QuoteNum,T2.RevisionNum,T2.XPartNum,T2.XRevisionNum,T2.Calc_LineDesc,T2.Calc_HasMisc, T2.Calc_Duration, T2.Calc_Modifier, T2.Calc_Mate, T2.Calc_Labor, T2.Calc_Misc, T2.ContractNum, T2.RenewalNbr, T2.SellingExpectedQty, T2.DocExpUnitPrice, T2.DocExtPriceDtl, T3.DocUnitPrice,T3.PricePerCode,T3.QtyNum,T3.SalesUM,T3.SellingQuantity,T3.UnitPrice,T3.Calc_NetPrice,T3.Calc_UMDescription
FROM QuoteHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN QuoteDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.QuoteNum = T2.QuoteNum
LEFT OUTER JOIN QuoteQty_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.QuoteNum = T3.QuoteNum AND T2.QuoteLine = T3.QuoteLine"

So the bold fields are what ive needed to bring over. Am i doing this wrong? When i generate the report it errors out, so i must not be doing it right. Sorry im just so new to all this.

10

You’ve enabled (not-excluded) those two fields in the RDD?
(and they differ from your original post of:QSalesRp.SalesRepCode and QuoteHed.TermsCode

And your SalesEngineer_c UD field is on OrderDtl, and not OrderHed?

11

Correct, the fields i need are QuoteDtl.SalesEngineer_c(I mistakened this at first), and QuoteHed.TermsCode.
Thanks for that suggestion, TermsCode was excluded and i ran it now and its WORKING!
Thanks so much! You’ve helped a ton Calvin

12

Okay one more thing.
So I need ONE more field added to this dataset, and its Customer.DefaultFOB.
Could you help me with how i would do that? I know it would be T4.DefualtFOB and then i would need to add a LEFT OUTER JOIN to link that up too.
I’ve already added Customer table to the Data Definition and created the relationship.

13

Fob and FobDesc already exist in the RDD.

image
image833×452 8.23 KB

You probably just need to the fields to the RDL’s query. Try adding:
T1.Customer_FOBDesc to the qurey, without adding another table.

One thing to keep in mind with Epicor Supplied reports. The “tables” in the RDD aren’t always the same as the tables used by the program. They’re temp tables that hold the records needed for your report. And they aren’t just a subset of the real table. Often they just happen to have the same name.

For example, in the original QuotForm RDD, the query expression only references three “tables”
QuoteHed_GUID, QuoteDtl_GUID, and QuoteQty_GUID

image
image761×226 8.46 KB

But if you look at the fields, there is one T1.Customer_Name

But there’s no field Customer_Name in table QuoteHed. When printing a quote Epicor does some stuff behind the scenes, and makes these temp tables with the GUID suffix. It makes columns in those that you might not expect.

14

@keatonskills - If you’ve made your own RDD, you can have the Customer.DefaultFOB field added by using the Linked Tables - > Pick Links.

  1. Select the QuoteHed table in RDD
  2. Make sure Customer is in the Picked list
    image
    image869×482 9.37 KB
  3. Select the Description Fields tab
  4. Choose Customer on the dropdown
  5. Select the field to add
  6. Click the Add field button.

image
image579×511 10.9 KB

After doing that, you should only need to add , Customer_DefaultFOB to the SELECT phrase in the RDL’s query. (And add the Query field too)

3 Likes
15

GOT IT. You are the MAN! You just fixed a huge headache for me. I appreciate it sir!