Setting RDD table criteria

Epicor ERP 10
,
1

I am joining the Memo table to the QuoteHed table so that we can use the Memo function to set individual customer terms and conditions on the quote form. I am joining on QuoteHed.CustNum and Memo.Key1, and would also like to filter by Memo.CategoryID “TERMS”. If I don’t filter by that, then I run into nvarchar/int errors in my test BAQ because Key1 for Customers is an int, but for other memos it might be a nvarchar. However, if I try to filter my dataset in SSRS, I still get “Conversion failed when converting the nvarchar value ‘RMA10001’ to data type int.”. Does this mean that it’s looking at this data before it gets to the SSRS render? I don’t see any option to set table criteria in RDD (there is something there, but it doesn’t give me the correct fields).

2

Try filtering the Memo.RelatedToFile instead.

3

This didn’t work for me, I get the same error.

4

Can you post the SQL using in the SSRS?

5 
="SELECT 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.EntryDate, T2.Company,T2.DiscountPercent,T2.DocDiscount,T2.DisplaySeq,T2.DrawNum,T2.KitParentLine,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.KitQtyPer,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.NeedByDate,T3.DocUnitPrice,T3.PricePerCode,T3.QtyNum,T3.SalesUM,T3.SellingQuantity,T3.UnitPrice,T3.Calc_NetPrice,T3.Calc_UMDescription
, T1.PONum, T1.Terms_Description, T1.Territory_TerritoryDesc,T1.DocTotalPotential,T1.DocTotalMiscAmt,T1.DocTotalDiscount,T1.DocTax,T1.ExpectedClose,T4.Platform_c,T5.MemoText AS CustomerTerms,T5.CategoryID,T6.RelatedToFile          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 LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.PartNum = T4.PartNum LEFT OUTER JOIN Memo_" + Parameters!TableGuid.Value + " T5 ON T1.Company = T5.Company AND T1.CustNum = T5.Key1"
6

I don’t see the where clause for RelatedToFile = ‘Customer’ and I see you put a T6.RelatedToFile in the query, but there isn’t a table for T6…
Try this:

="SELECT 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.EntryDate, T1.PONum, T1.Terms_Description, T1.Territory_TerritoryDesc, T1.DocTotalPotential, T1.DocTotalMiscAmt, T1.DocTotalDiscount, T1.DocTax, T1.ExpectedClose, 
T2.Company, T2.DiscountPercent, T2.DocDiscount, T2.DisplaySeq, T2.DrawNum, T2.KitParentLine, T2.KitFlag, T2.KitPricing, T2.KitPrintCompsInv, T2.KitShipComplete, T2.KitQtyPer, 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.NeedByDate, 
T3.DocUnitPrice, T3.PricePerCode, T3.QtyNum, T3.SalesUM, T3.SellingQuantity, T3.UnitPrice, T3.Calc_NetPrice, T3.Calc_UMDescription, 
T4.Platform_c, 
T5.MemoText AS CustomerTerms, T5.CategoryID
	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 
	LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.PartNum = T4.PartNum 
	LEFT OUTER JOIN Memo_" + Parameters!TableGuid.Value + " T5 ON T1.Company = T5.Company AND T1.CustNum = T5.Key1
	WHERE T5.RelatedToFile = 'Customer'"
7

The T6 was a mistake, should have been T5. As for the Where clause, I am still getting the error; I did try a Where clause in the SQL with CategoryID at an earlier time and it didn’t work.

8

Did you try the code I just sent?

9

Yes, I am still getting the following error: RunTask: Conversion failed when converting the nvarchar value 'RMA10001' to data type int.

10

OK. Did you set the Filter on the RDD? This would be better than the SSRS filter.

11

Its trying to relate a record from T5(Memo).Key1 where that value is an nvarchar, to T1(QuoteHed).CustNum which is an integer.

Filtering Memo in the RDD (like @Jason_Woods said) should limit the relatable records to ones of the same type.

12

I’m struggling with setting a filter on the RDD. I have tried adding table criteria on the Memo table but the fields don’t seem to be from that table. Right now I’m reading the application help and trying to figure it out.

Edit: I got it working by just typing the field name in manually.

1 Like
13

any resolution to this - i have same/similar issue -
it appears as if the RDD/BAQ filters just do not apply to the dataset creation . i don’t see anywhere - where adjusting the SSRS / SQL is required. as it would be much more efficient to NOT pull all records in to the report db, no?

14

You added table Criteria to an RDD, and the output dataset (tablename_GUID) still contains records you thought should be excluded?

Is that table you applied the criteria to, related with an “Output” or “Defifinition” ?

15

its a field in the BAQ … this is a BAQ RDD

16

Ohhh … I’ve not worked with RDD’s containing BAQs as a datasource.

(That is what you mean by “a BAQ RDD”, right?)

17

correcttamundo :slight_smile:

18

it appears the BAQ/RDD sends a string of selected values in the user criteria portion of the resulting report table, but the data is in no way filtered on the application side. in which case a butt-load of data is generated for no particular (good) reason. [Forrest gump - for no particular reason I just kept on going - YouTube]
as such, now code is required in the SSRS to extract the filter value(s) and then filter the dataset from the report tables. more work for no particular reason.

19

I would submit it is a drawback to the BAQ/RDD’s over the BAQRpt

in a BAQ report you can easily include a filter in the BAQR setup which would apply to the EXTRACTION of data out of epicor.
in the BAQ/RDD this is not so. Adding a criteria filter does NOTHING to the EXTRACTION of data out of epicor.

20

Since you can’t mix DB tables and BAQ’s in an RDD, what is the point of making a BAQ based RDD?