Open SSRS Report from External Application using REST

Epicor ERP 10
1

Hello everyone,

I’m building a customer portal using Angular, and my next step is allowing customers to view SSRS reports in a PDF viewer like you normally would. On the detail page for sales orders (in our site, not in Epicor), there will be a button to print the selected order. This action triggers the following REST call:

https://e102/ERP102200/api/v1/Erp.Rpt.SalesOrderAckSvc/SubmitToAgent

I include the required JSON in the body of the POST request for the particular sales order. When this request is sent directly to Epicor, the SSRS report pops up in a PDF viewer. However, like all other REST calls we use, the request should be sent to the NodeJS server first and then called from there - but this doesn’t work. The report is being generated, as seen in the System Monitor, but the PDF doesn’t show up.

How can I let our Node server know to direct the SSRS report to the browser, if that’s even a thing?

This is the Node call to Epicor:

router.post('/portal/print', function(req, res){

let idNum = req.query.idNum;
let queryStr = "https://e102/ERP102200/api/v1/Erp.Rpt.SalesOrderAckSvc/SubmitToAgent";

options = {
    url: queryStr,
    json: {
            "ds": {
              "SalesOrderAckParam": [
                {
                  "OrderNum": idNum,
                  "SysRowID": "00000000-0000-0000-0000-000000000000",
                  "AutoAction": "SSRSPREVIEW",
                  "AgentSchedNum": "0",
                  "AgentID": "SystemTaskAgent",
                  "AgentTaskNum": 0,
                  "RecurringTask": false,
                  "ReportStyleNum": 1005,
                  "WorkstationID": computerName,
                  "ArchiveCode": 0,
                  "NumericFormat": ",.",
                  "ProcessTaskNum": 0,
                  "DecimalsGeneral": 0,
                  "DecimalsCost": 0,
                  "DecimalsPrice": 0,
                  "GlbDecimalsGeneral": 0,
                  "GlbDecimalsCost": 0,
                  "GlbDecimalsPrice": 0,
                  "SSRSRenderFormat": "PDF",
                  "PrintReportParameters": true,
                  "SSRSEnableRouting": true,
                  "RowMod": "A"
                }
              ],
            },
            "agentID": "SystemTaskAgent",
            "agentSchedNum": 0,
            "agentTaskNum": 0,
            "maintProgram": "string",
        },
    rejectUnauthorized: false,
    auth: {
        user: config.epicorCredentials.user,
        pass: config.epicorCredentials.pass
    }
};

// request is a library to simplify making http requests in Node
request.post(options, (error, response) => {
    if (error) {
        console.log("Error! Details: ", error);
        res.send(error);
    }
    else if (response.statusCode === 200){
        res.send({});
    }
})
})
1 Like
2

I’ve learned some things about how the report flow works. This image is from the Epicor10_techrefSystemAdministration_102200.pdf document on EpicWeb.

image

As mentioned in the document, Microsoft Report Server will create the report and send it back to the database as binary, though it seems to me it’s encoded binary, and soon thereafter it gets sent to the client to open the PDF viewer.

The key is that the Epicor 10 Database will have the binary stored in the Ice.SysRptLst table, in the RptData column.

3

Which version of SSRS are you using? I know they added some additional REST functionality into SSRS 2017.

Previous version use the RS SOAP api. There is a package mssql-ssrs that uses this but since you are using REST that probably isn’t the way you want to go.

These links might help shed some light:

https://docs.microsoft.com/en-us/sql/reporting-services/developer/rest-api?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/reporting-services/application-integration/integrating-reporting-services-using-url-access-web-application?view=sql-server-2017

If you are not on 2017 this might be of some use:

https://docs.microsoft.com/en-us/sql/reporting-services/application-integration/integrating-reporting-services-into-applications?view=sql-server-2016

4

We are on 2016, so without REST it wouldn’t be very feasible to delve into. I’ve got it working by creating the report with SubmitToAgent, then calling a BAQ to fetch the RptData column from the Ice.SysRptLst table based on certain criteria. I send that pdf binary to the client, where I open the PDF in a new tab.

let pdfWindow = window.open("");
pdfWindow.document.write("<iframe width='100%' height='100%' src='data:application/pdf;base64, " + encodeURI(data[0]['SysRptLst_RptData']) + "'></iframe>" );
5 Likes
5

Awesome. Glad you figured it out.

I’d be interested to see your portal and how it functions. It sounds pretty cool.

Is this something that you supply to people outside your organization?

6

It’s still internal while in development, and then will be available to our customers. The only cool part about it is using REST to grab data from Epicor, other than that it’s just normal web dev.

7

Got it. I had to create a custom REST API using express, mssql, and Azure functions for an app we were working on since our company has yet to move beyond 10.1.400.

Can’t wait to be able to use the builtin REST services once we finally upgrade.

8

Hey Mason_Zeimet,

I know this post was answered awhile ago, but I would like to know which criteria you used to pull the correct row for the RptData column?