Create PDF From SSRS Report And Get File

[ShawnLeenerts]

Good morning Mega-Minds! I’ve created a method in a Dashboard button click event that creates a new quote with a selected ProjectID, and then it adds parts to the quote. I also want it to automatically run the SSRS report for the quote as a PDF, then copy that PDF to a folder. I’ve got the quote creation and part adding parts working perfectly, but the running of the report and PDF creation is where I’m running into issues. The image below shows all the code I’ve put together from various website articles regarding this topic…

The first part runs the report, and it even shows up in the Epicor System Monitor as having been run…

But the PDF portion gives this error…

So, I think I’ve got the code working for running the report, but now I need a way to get the PDF so I can move it to a network folder. Any suggestions will be appreciated. Thank you for your time and have a great day!

[josecgomez]

Please don’t ever connect to the database from the client. That is a supremely bad idea particularly for security, upgradability , maintenance, and everything in-between.

You can accomplish the same by running a BAQ.

[dr_dan (Dan Ramirez)]

How would you run a BAQ from that code block?

[josecgomez]

There are a ton of examples of this in the forum.

Grid Property Changed

That sounds amazing…and a little over my head…So I am already populating the grid with a dynamic query and BAQ. How would I say BAQ Data View instead? DynamicQueryAdapter dqa1 = new DynamicQueryAdapter(oTrans); dqa1.BOConnect(); QueryExecutionDataSet qeds1 = dqa1.GetQueryExecutionParametersByID("GS_UD09_Movement_All"); qeds1.ExecutionParameter.Clear(); qeds1.ExecutionParameter.AddExecutionParameterRow("OrderNum", epiNumericOrderNumRef.Value.ToString() , "nvarchar", false, Guid.NewGuid(), "…

[dr_dan (Dan Ramirez)]

Thank you very much.

[ShawnLeenerts]

Thanks Jose! I get that I can create a BAQ to get the info I need from the database table. But how do I get the PDF of the SSRS report? Ultimately, all I’m trying to accomplish is running the report with the new quote number that’s created and then generate a PDF file that can be placed in the quote’s folder.

[josecgomez]

You need to the the bytes out of the RptData field and write them to a file like you are doing.

[Mark_Wonsil]

Jose answered that here: Trigger ARInvoice Generation from API - not totally getting it

[dr_dan (Dan Ramirez)]

As I am following along, I am able to get the RptData and convert it to a base64 string and then when I plug that into the base64 guru converter from Jose’s video, it shows me the PDF file that I was trying to get. So far so good.

Is there an easy/preferred way to take those bytes and attach them to an email in a BPM? Do I have to save it to a file? For some reason, I was not having any luck actually getting the file to write to the C:\ path I specified… no error. just didn’t work. But when I show the string in a message, I can copy/paste it and that part works. Ideally, I would just take the bytes and attach it to the email. But the attachment in SmtpMail seems to want to attach from a file path.

[Mark_Wonsil]

@dr_dan

For some reason, I was not having any luck actually getting the file to write to the C:\ path I specified

The BPM is running at the server, so you’re trying to write to a server file location that the app server does not (and should not) have rights to. The only safe place to write to on the server is the EpicorData folder.

[ShawnLeenerts]

There’s a lot of steps in that video that just won’t work for what I’m trying to do. I need it done all programmatically because this will be for Project Managers who don’t have enough tech experience to copy, paste, convert, etc.

I just need code that will run the report, which I’ve got, and then create a PDF to a folder and open it for viewing.

[josecgomez]

@ShawnLeenerts

I need it done all programmatically

The code to do it programmatically is in that same post. The video is just a demo of what you can do.

[ShawnLeenerts]

I created a BAQ to retrieve the report data like @josecgomez described, but when the code runs I get this error…

The image below shows my code so far:

At the end, I convert the object “SysRptLst_RptData” to string and then convert that for the byte[]. What should I convert the object into?

[josecgomez]

So you don’t have to convert from base64 because you aren’t making the call I’ve rREST

You should.be abke to just write the bytes as they come out of the BAQ

[Michael_Ramsey]

@ShawnLeenerts

Mega-Minds!

[ShawnLeenerts]

I tried just writing the bytes with the BAQ object, but I get this compile failed error…

[josecgomez]

Cast it to to a byte array first.

[Mark_Wonsil]

Cast like Jose did in the previous post:

Trigger ARInvoice Generation from API - not totally getting it - ERP 10 - Epicor User Help Forum (epiusers.help)

[josecgomez]

Because you are using the adapter that byte array is not a base64 encoded string, so you don’t need to do toString or anything else just cast to a byte array.

File.WriteAllBytes(@serverFile,(byte[]) rptRow["SysRptList_RptData"])

[ShawnLeenerts]

I got it working! Thank you @josecgomez and all the others! You are the Mega-Minds that help the rest of us navigate Epicor.

Here’s the BAQ I created to get the report data…

And here is my final working code, in case anyone else might benefit from it…

string serverfile = serverpath +  @"\" + quoteNum + ".pdf";

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Generate the PDF for the Quote
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Erp.Proxy.Rpt.QuotFormImpl quoteform = WCFServiceSupport.CreateImpl<Erp.Proxy.Rpt.QuotFormImpl>
	((Ice.Core.Session)oTrans.Session, Epicor.ServiceModel.Channels.ImplBase<Erp.Contracts.QuotFormSvcContract>.UriPath);
Guid MyReportGuid = Guid.NewGuid();
Erp.Rpt.QuotFormDataSet QuoteFts = null;
QuoteFts = quoteform.GetNewParameters();
QuoteFts.QuoteFormParam[0].AgentID = "SystemTaskAgent";
QuoteFts.QuoteFormParam[0].AutoAction = "SSRSGenerate";
QuoteFts.QuoteFormParam[0].DateFormat = "dd/MM/yy";
QuoteFts.QuoteFormParam[0].ArchiveCode = 1;
QuoteFts.QuoteFormParam[0].SSRSRenderFormat = "PDF";	
QuoteFts.QuoteFormParam[0].QuoteNum = quoteNum;
QuoteFts.QuoteFormParam[0].ReportStyleNum = 1001;
QuoteFts.QuoteFormParam[0].TaskNote = String.Format("{0}", MyReportGuid);
quoteform.RunDirect(QuoteFts); // Runs the report - waits until finished/hangs.
	
// Assgin the generated report GUID
string strMyReportGuid = String.Format("{0}", MyReportGuid);

// Connect to the "Get_Report_Bytes" BAQ using the report GUID
DynamicQueryAdapter baqAdapter = new DynamicQueryAdapter(oTrans);
baqAdapter.BOConnect();

QueryExecutionDataSet qeds = new QueryExecutionDataSet();
qeds.ExecutionParameter.AddExecutionParameterRow("ReportGUID", strMyReportGuid, "nvarchar", false, Guid.Empty, "A");

baqAdapter.ExecuteByID("Get_Report_Bytes", qeds);

if(baqAdapter.QueryResults.Tables["Results"].Rows.Count == 0){
	MessageBox.Show("Unable to find report data, please contact IT for support.");
	return;
}

DataRow rptRow = baqAdapter.QueryResults.Tables["Results"].Rows[0];

// Write the PDF to a server path
if(Directory.Exists(@serverpath))
	File.WriteAllBytes(@serverfile,(byte[]) rptRow["SysRptLst_RptData"]);