Goal: Print attachments from SSRS report, specifically to print engineering drawings and shipping documentation from SSRS reports.
Background: We are a DT cloud customer that is hosted by Epicor. We have a SharePoint site. We have thousands of engineering drawings. We do not have DocStar or ECM.
There are many posts on the forum about attachments for the Job Traveler and many of them that I have read.
My best attempt to attach the engineering drawings to the job traveler are as follows:
Link SharePoint to Epicor, test connection is successful
Place engineering drawings in SharePoint and has URL
Attach the drawing to the Job, can both view and download drawing from Epicor
Build SSRS report, add image. In the final version of the report, my goal is to pass the attachment data to the report. But for now, I am just working toward proof of concept with one engineering drawing.
I have also considered using the image maintenance to hold the engineering drawings. But we have thousands of drawings. Is image maintenance designed for a few company logos or can it handle thousands of drawings? Printing site logo on forms - #4 by ckrusen
Summary) Can I continue with my current path of using the external image in the SSRS report but then how do I fix my credential issue? Or do I have to abandon this path and try image maintenance?
I can tell you that we have many thousands of drawings stored in image maintenance with no issues- but they aren’t full PDFs. I wrote an iLogic script that from Inventor creates a drawing from a .iam or .idw , generates a SVG of the isometric view, converts it to base64 and uploads it. These vectors are then pulled when our labels print through Bartender to include the part image on the label. This solution wouldn’t work easily for an existing database of many PDFs.
In your BPM, step 1 is to get a list of the images.
I wrote a query to retrieve the list of attachments on the job. The query will return the XFileRef_XFileRefNum and XFileRef_XFileName. The XFileName holds the path to the engineering drawing image files on Sharepoint. The path is a URL.
In your BPM, step 2 is to get the image data and in your statement you are looking inside the database. I must GET my data from SharePoint using a URL address and it is not inside the database.
Is it still possible to retrieve the images when they are in SharePoint and not inside the database?
Your Epicor is connected. Much easier to get it from there.
I’d love to just shove this in the Call Context, but it’s not available in the report.
Can’t find any Report Parameters we can shove it into either
What we need is a place on JobHead to put the image data, then it can be pulled on down into the report.
You could make a UD field on JobHead, and put the data there. IE-> JobHead.ImagesForReport_c and you could populate it either beforehand, or on the submittoagent.
Pulling attachment data from Sharepoint is as easy as this:
CallService<Ice.Contracts.AttachmentSvcContract>(attach=>
{
var bytes = attach.SpDownloadFile(xFileRefNum);
});
I am sorry if these are stupid questions, as I am trying to expand my knowledge and learn. So to learn, I must ask.
Yes. We have established a connection with SharePoint. The test connection says successful. But the image files are not in the table Image in Epicor.
If that is the case, then are you saying that I can use the
//Get the image data for those and put the image data in a Dictionary<string, string> var images = Db.FileStore.Where(x => imageList.Contains(x.FileName)).ToDictionary(key => key.FileName, val => Convert.ToBase64String(val.Content));
Are you saying that the SharePoint file can be found in Db.FileStore?
Perfect so you’re ready for Kevin’s suggestion. Get the image bytes from Sharepoint, then inject them into a JobHead RDD field. Similar to this (swap FileStore for Attachment BO)
Outside of the BPM, in a BAQ, I can get a list of the images. My SQL is:
select
[XFileRef].[XFileName] as [XFileRef_XFileName]
from Erp.JobHead as [JobHead]
inner join Ice.XFileAttch as [XFileAttch] on
XFileAttch.Company = JobHead.Company
and XFileAttch.Key1 = JobHead.JobNum
and ( XFileAttch.RelatedToFile = 'JobHead' )
inner join Ice.XFileRef as [XFileRef] on
XFileAttch.Company = XFileRef.Company
and XFileAttch.XFileRefNum = XFileRef.XFileRefNum
where (JobHead.JobNum = @PickJobNum)
The Job Traveler is a custom report that I built from a BAQ and RDD. It has one parameter and that is PickJobNum.
In the BPM, I need to know the parameter that the user enters. Here is the trace after the user enters the Job Number.
My variable holds all the data circled in red. I just want the job number, highlighted in yellow.
I have also tried dsDynamicCriteriaParamRow.UserCriteria.Select(JobNumber). But I get an error that name ‘JobNumber’ does not exist in the current context.
One more piece of information, in application studio the field is bound to ReportParam.JobNumber
