The problem is, most of the stock SSRS reports are designed to take the GUID of a report dataset as their only parameter, not domain values like an OrderNum or InvoiceNum. I’d like to know how you
Ask Epicor to Generate an invoice for that invoice number
If the report style is set to output to database rather than XML, running the report should result in a GUID being generated and a set of tables with that GUID in their names being created in the EpicorEnvironmentName.Reports database (e.g., Live.Reports). But I’m not sure how you’d obtain that GUID to pass it to SSRS.
select UserID, RptDescription, CreatedOn, PurgeDate, right(FileName, 32) as ReportGuid
from Test.Ice.SysRptLst
where Archived = 1
and UserId = 'joeblow'
order by CreatedOn desc
It’s easy enough for a human to recognize the report they just ran. Maybe you could put some kind of ID in RptDescription for an integration to recognize specific runs of a report.
So going back to the original request - ERP 10 stores the completed report in the Database in the SysRptLst Table, RptData Field. The ReportMonitor Service has a Method GetReportBytes, that takes a single parameter of the SysRowID value for the SysRptLst record and it returns the completed report stored in the RptData field for that record.
I believe that the GetRows Method of the SysMonitorTasks Service will return the information you need to select the report that you want to return (use client tracing to see the filter parameters to send in) and if you set the User Description field when you submit the report, that value will be in the UserDescription field returned in the GetRows call.
I sound like a broken record but I use a UBAQ. UBAQ UBAQ UBAQ UBAQ why fiddle with all this stuff when you don’t have to. In 10.2.500 change UBAQ to Func but same concept
Simply write a BAQ which takes in a Parameter (Invoice Number?)
Then in the UBAQ Post Processing on GetList() you can do this
foreach(var x in ttResults)
{
using (var ar = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.ARInvFormSvcContract>(Db))
{
ARInvFormTableset ARts = null;
ARts =ar.GetNewParameters();
ar.PackNumDefaults(ref ARts);
ARts.ARInvFormParam[0].AgentID = "SystemTaskAgent";
ARts.ARInvFormParam[0].AutoAction = "SSRSGenerate";
ARts.ARInvFormParam[0].CalledFrom = "Erp.UI.ARInvoiceTracker";
ARts.ARInvFormParam[0].DateFormat = "m/d/yyyy";
ARts.ARInvFormParam[0].InvoiceNum = x.InvcHead_InvoiceNum;
ARts.ARInvFormParam[0].NumericFormat = ",.";
ARts.ARInvFormParam[0].ReportCultureCode = "en-US";
ARts.ARInvFormParam[0].ReportCurrencyCode = "USD";
ARts.ARInvFormParam[0].SSRSRenderFormat = "PDF";
ARts.ARInvFormParam[0].TaskNote = taskNote.ToString();
x.Calculated_ReportGUID = taskNote.ToString();
ar.RunDirect(ARts); //This runs the report right away but it hangs until it finishes...
//ar.SubmitToAgent( ARts, "SystemTaskAgent",0,0,"Erp.UIRpt.ARInvForm"); this uses the system agent and returns right away but you'll have to POLL until your Report is Ready
}
}
Note that I put a GUID (my own GUID) in TaskNotes and return that back with my UBAQ Response Data in Calculated_ReportGUID Field
Now the BAQ will return back (VIA REST) the GUID of the Report that I just ran.
Then Simply you can use another UBAQ to get the Report Data out of the table once the Task is Finished.
The ReportBytes (after it has run) are stored in SysRptList (SSRS Only)
So your other BAQ should be pretty simple (see below) is just a query to SysRptList where the Data is Filtered by the TaskNote of my generated GUID (as a Parameter)
So in other words,
You make a simple
GET Request to your first UBAQ with InvoiceNum as a Paramter
It returns a GUID in Calculated_ReportGuid field
Which you then use to make another
GET Request to BAQ #2 which will return the Report Bytes
In the future, and I’m sure that @josecgomez would agree, when you get to 10.2.500 the UBAQ can be replaced with an Epicor Function. Same idea, just a little cleaner.
As with all my weird issues, Jose has the exactly correct answer. Thank you I will watch this video after lunch and the code you posted looks like exactly what I needed.
I can access the correct RptData field- Not sure how it’s encoded however. I’d like to just snag that hex string and convert it to bytes in C# and File.WriteAllBytes(“myPDF.pdf”, ); Not sure how to convert that data though…
string exampleGUID = "9f0bdbf60e774aca8bed4846fdeedf08";
string strConnect = $"server={s};user id={u};password={p};database={d}";
SqlConnection conn = new SqlConnection(strConnect);
conn.Open();
SqlCommand cmd = new SqlCommand($"SELECT rptdata from Ice.SysRptLst WHERE RIGHT(filename,32)='{exampleGUID}';", conn);
byte[] results = (byte[])cmd.ExecuteScalar();
System.IO.File.WriteAllBytes(@"C:\PDF\test3.pdf", results);
However I am just realizing I don’t know what references to make, this is my first E10 project. I used dotPEEK to seach for Ice.Assemblies but this computer is so slow it’s been searching for two hours.
Next project - too many people waiting for this on my back so a new new lines of code and it’s back in business (just an external .net 472 exe file) My ambition was to rework it using the new REST stuff but too much other stuff to carry at the moment.
I just need to reach into Epicor ,trigger the invoice generation for invoice x, add my own guid to the UserDescription field, then find the Epicor report GUID in the filename field.
Here’s my 9.05 code - code explains better than me:
foreach (WorkLoadClass w in wl)
{
// Submit for processing.
try
{
ds.Tables["ARInvFormParam"].Rows[0]["InvoiceNum"] = w.getInvoiceNumber();
ds.Tables["ARInvFormParam"].Rows[0]["PrinterName"] = "";
ds.Tables["ARInvFormParam"].Rows[0]["ReportStyleNum"] = vars.epReportStyle;
ds.Tables["ARInvFormParam"].Rows[0]["AgentID"] = "SystemTaskAgent";
epiARInvForm.SubmitToAgent(ds, "SystemTaskAgent", runnow, newtask, "Epicor.Mfg.UI.Rpt.ARInvForm");
mf.at($"Submited Invoice {w.getInvoiceNumber()} for processing.", colorinfo, true); // Populates textbox on main thread with whats going on.
}
catch (Exception ex)
{
Debug.WriteLine($"ds.tabled error: {ex.Message}");
}
WaitForXMLFileName(w); // wait for Epicor to populate field, error out after a few seconds.
}
No problem – I found the correct DLL file so that’s ok… I’m plowing ahead with my old way and trying to get REST going at the same time… Lots to learn lol