Image Retrieval at SSRS Report(SSRS Report Builder)

[CimyThomas]

while trying to take quotation print with part image by joining part, image and file store table in Rdd and in dataset query, I am getting below error at Epicor system monitor and print not coming.Plz suggest on this.
Error :
Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: String or binary data would be truncated.
The statement has been terminated.

Dataset Query

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,T2.Company,
T2.OrderQty,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,T6.Content
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 Image_" + Parameters!TableGuid.Value + " T5 ON T4.Company = T5.Company AND T4.ImageID=T5.ImageID LEFT OUTER JOIN FileStore_" + Parameters!TableGuid.Value + " T6 ON T5.Company = T6.Company AND T5.ImageSysRowID=T6.SysRowID" ```

[hkeric.wci (Haso Keric)]

Printing site logo on forms

@Hogardy had a post that describes this. Basically, the table that stores the images is called Ice.FileStore. [image] Notice the index to the table is the SysRowID and not a file name. So there’s another table called Erp.Image. [image] This table contains information about the image file in the image store. The ImageSysRowID in Image links to the FileStore record. So in your RDD, you need to have an ImageID to get to your Image record. (Make sure to include the necessary fields below in t…

[CimyThomas]

tried the same as provided in the solution, everything work fine, till I join image table at report dataset query. Can anyone suggest where I am going wrong.

For information I am attaching screen shots and dataset Query

=“SELECT T1.Calc_TaxMethod,T1.TermsCode,T1.Brand_c,T1.RequestDate,T1.ProjectName_c,T1.DocTotalDiscount,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,T2.Company,T2.OrderQty,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 LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.PartNum = T4.PartNum LEFT OUTER JOIN Image_" + Parameters!TableGuid.Value + " T5 ON T4.Company = T5.Company AND T4.ImageID=T5.ImageID"

[Mark_Wonsil]

@CimyThomas

everything work fine, till I join image table at report dataset query.

Not clear what you mean here. Do you get an error uploading the report? Are you getting a report and a broken image? Are you seeing data in your SSRS database? Is there an error message some other place?

[CimyThomas]

print is not displaying after joining image table. I tried step by step. Added part, image and file store table in RDD,define relations and took a quote print and it displayed.Then at SSRS Report Dataset joined part and image table,and tried to take a print and it shows data truncated error at system mointor.

[CimyThomas]

hasokeric , Mark_Wonsil Any suggestion?

[Mark_Wonsil]

Do you get this error if you just click Submit report or only on Preview?

Which fields did you include/exclude in the Image and FileStore tables?

[CimyThomas]

I am getting error while taking preview
below shown fields are excluded from Image and Filestore tables

[Mark_Wonsil]

@CimyThomas

I am getting error while taking preview

Can you try just the generate and use the standard SSRS Report Style?

[CimyThomas]

@hkeric.wci ,@Mark_Wonsil :- print preview and generate only with Standard SSRS Report style using standard rdd there is no error.
I just duplicated the quote rdd, add new tables(Filestore, image), excludeded required fields, defined relations and saved. Then created new style,(i.e. custom style).Took a print without any modification in report dataset and I got the print(After joining image table with part table in dataset query ,error is occurring.)While taking print preview as well as trying generate only, I am getting error in my custom style. Main Error line showing is ‘String or binary data would be truncated.This Statement has been terminated.’

Have you tried bringing part image ,in any of your ssrs reports?

I think, something I am missing, may be something extra needed to hold image binary data. I don’t know. When I checked the error ,its says The "String or binary data would be truncated " error indicates that it is attempting to store something in the table that is larger than the column allows.

[Mark_Wonsil]

@CimyThomas

Have you tried bringing part image ,in any of your ssrs reports?

Every day on our Job Travelers.

What I like to do is to take each step at a time and make sure it works. You copied the RDD and added the tables. Now, try running your new RDD with the standard SSRS report. The unused fields should not affect the standard report. If you don’t get the error there, then we know the RDD is good and it’s something with the custom SSRS report.

If it’s the custom report, then just make the Data source changes but do not add any fields and see if it errors out there. Finally, add the image to display to see if that errors out.

[CimyThomas]

Thanks @Mark_Wonsil. I figured out the issue. While unchecking content field in Filestore table at RDD Exclusions tab -error occurring. As of my knowledge ,content field holds the image data. am I right?
So, now what should I need to do, in order to bring part images in my quote print.

[Mark_Wonsil]

Yes, Content is the image contents. We still don’t know where the error is occurring. Have you tried the steps above to narrow down where the error is happening?

Without knowing that, what does your image look like in your SSRS report? You’re assigning a content type? Are you assigning content to any other fields in the report? How big is the image in bytes?