Openedge ABL code to call SSRS web service to render a report - Forum - OpenEdge General - Progress Community

Openedge ABL code to call SSRS web service to render a report

 Forum

Openedge ABL code to call SSRS web service to render a report

  • Hello everyone,

    I have a project that I'm working on which requires a web service call to Microsoft SSRS (SQL Server Reporting Servcies)  2008 R2.

    I have been able to sucessfully:

    1. Read the SSRS WSDL using the bprowsdldoc tool

    2. Connect and Authenticate to the SSRS web service

    3. Make calls to the web service methods where I provide values for all the parameters

    However, I need to call one of the methods: LoadReport (see below) in which I need to omit or pass a null value for the HistoryID parameter.

    DEFINE VARIABLE Report AS CHARACTER NO-UNDO.
    DEFINE VARIABLE HistoryID AS CHARACTER NO-UNDO.
    DEFINE VARIABLE executionInfo AS LONGCHAR NO-UNDO.

    RUN LoadReport IN hReportExecutionServiceSoap(INPUT Report, INPUT HistoryID, OUTPUT executionInfo).

    I am new to working with web services and XML in Progress and I have run into an issue with calling this method.

    I am trying to find the proper syntax to send a NULL value for an optional parameter or omit the parameter.  In Visual Basic, I would just pass a NULL for the parameter, but I can't seem to find the Progress syntax for this.

    I have tried sending all kinds of different values including the ? (Unknown) value.  When I use the ? (unknown value).  I receive an error 11790 message: "The SOAP parameter will not accept an UNKNOWN 4GL parameter value".

    Has anyone sucessfully created ABL code to call the SSRS web service to render a report?

    If so, would you be able to share the proper syntax for calling the LoadReport method?

    Thank you,

    Terry

  • I do something like the below to write out null values.

    mhSaxWriter:start-element('CorticonRequest').

    mhSaxWriter:declare-namespace('http://www.w3.org/2001/XMLSchema-instance', 'xsi').

    ...

    mhSaxWriter:start-element(hField:xml-node-name).

    if hField:buffer-value eq ? then

    mhSaxWriter:insert-attribute('xsi:nil', 'true').

    else

    /* write non-null values */

    mhSaxWriter:end-element(hField:xml-node-name).

    mnhSaxWriter is a SAX-WRITER handle.

    HTH

    -- peter

  • Thanks for the reply Peter,

    I will give the SAX-WRITER a try.

    In the past I have already tried to manually create the XML for a null value, but that responded with an error as if I didn't specify a valid HistoryID rather than ignore the parameter.

    ...

    DEFINE VARIABLE v-reportname AS CHARACTER NO-UNDO.
    DEFINE VARIABLE v-historyid AS CHARACTER NO-UNDO.
    DEFINE VARIABLE v-execinfo AS LONGCHAR NO-UNDO.

    ASSIGN v-reportname = "Report Name"
    v-historyid = "".

    RUN LoadReport IN hReportExecutionServiceSoap
    (INPUT v-reportname, INPUT v-historyid, OUTPUT v-execinfo).

    ...

    I may not have the proper XML syntax so I'll try the SAX-WRITER appoach.

    Both inputs for this method are of type="s:string" so I'm not sure I have to pass XML or not.  When I called the method with a valid HistoryID (a snapshot of the report), then the call worked fine and I only passed a regular string value for HistoryID.  Unfortunately, I don't want to use an existing snapshot of the report and need to omit the HistoryID parameter.

    I'll let you know how it goes.

    Thanks again,

    Terry

  • Hi Peter,


    Thanks for the SAX-WRITER suggestion.  I gave it a try to see if it would pass a value that the SSRS web service would recognize as NULL and ignore the parameter, but unfortuately it did not work.


    I received the same result as if I had passed an invalid reference to a HistoryID that didn't exist.


    ...
    CREATE SAX-WRITER hSaxWriter.
    hSAXWriter:FORMATTED = TRUE.
    hSAXWriter:ENCODING = "UTF-8".

    lOK = hSAXWriter:SET-OUTPUT-DESTINATION("longchar", v-historyid).


    lOK = hSAXWriter:START-DOCUMENT().

    lOK = hSAXWriter:START-ELEMENT("HistoryID").
    hSAXWriter:DECLARE-NAMESPACE('http://www.w3.org/2001/XMLSchema-instance', 'xsi').
    lOK = hSAXWriter:INSERT-ATTRIBUTE('xsi:nil', 'true').

    lOK = hSAXWriter:END-ELEMENT("HistoryID").


    lOK = hSAXWriter:END-DOCUMENT().


    RUN LoadReport IN hReportExecutionServiceSoap
    (INPUT v-reportname, INPUT v-historyid, OUTPUT v-execinfo).
    ...


    The XML string that was created looked good - but SSRS didn't recoginze it as a value to ignore the parameter.


    The SSRS WSDL indicates the output from the LoadReport method is a complex type, but the inputs are both just strings.
















    There must be something simple that I'm missing.


    Have you ever written any code to call a method from a SSRS web service?


    Thanks for the help.


    Terry

  • Hello Everyone,

    I have some additional information.  Here is the error message from the SSRS logs when I try to pass an empty string to the LoadReport Method:

    ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ParameterTypeMismatchException: , Microsoft.ReportingServices.Diagnostics.Utilities.ParameterTypeMismatchException: The parameter value provided for 'snapshotID' does not match the parameter type. ---> System.FormatException: String was not recognized as a valid DateTime.
    at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)
    at Microsoft.ReportingServices.Diagnostics.Globals.ParseSnapshotDateParameter(String snapshotDate, Boolean nullIsValid)

    It appears as though the web service is looking for a string that represents a valid datetime parameter.

    Does anyone know how to pass a string value which SSRS would recognize as a NULL datetime?  It does state nullsValild, but I can't seem to invoke that with a proper call from Openedge.

    Thanks,

    Terry

  • Does anyone know how to make a web service call exclude an optional parameter?

    RUN LoadReport IN hReportExecutionServiceSoap
    (INPUT v-reportname, INPUT v-historyid, OUTPUT v-execinfo).

    I need to exclude the optional second INPUT parameter (v-historyid) in this call.

    For this particular SSRS web service, I believe if anything is sent in the SOAP XML message for the HistoryID parameter, it will try to find a report snapshot which is not what I want in this case.  I need the SOAP message to exclude the element altogether?

    The XML should look something like this:



    Report Name

    Not like this - I believe if the HistoryID elemetnt is there at all, I cannot run the web service the way I would like.



    Report Name


    Is this possible with Progress?

    Thanks,

    Terry

  • Hello Everyone,

    I was able to solve my issue with the LoadReport SSRS web service call.

    I had to use the wrapped form for making the RUN call to the web service.

    ...

    CREATE SAX-WRITER hSaxWriter.
    hSAXWriter:FORMATTED = TRUE.
    hSAXWriter:ENCODING = "UTF-8".

    lOK = hSAXWriter:SET-OUTPUT-DESTINATION("longchar", v-param1).
    lOK = hSAXWriter:START-DOCUMENT().

    lOK = hSAXWriter:START-ELEMENT("LoadReport").
    hSAXWriter:DECLARE-NAMESPACE('http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices').

    lOK =  hSAXWriter:WRITE-DATA-ELEMENT("Report",v-reportname).

    lOK = hSAXWriter:END-ELEMENT("LoadReport").
    lOK = hSAXWriter:END-DOCUMENT().

    RUN LoadReport IN hReportExecutionServiceSoap
    (INPUT v-param1, OUTPUT v-execinfo).

    ...

    This allowed me to only pass one INPUT parameter and exclude the element.

    Thanks,

    Terry

  • Hi Terry.

    Today I ran into the same problem. The signature of my function looks like this:

    PROCEDURE authenticate:
     DEFINE INPUT PARAMETER userID1 AS CHARACTER NO-UNDO.
     DEFINE INPUT PARAMETER password AS CHARACTER NO-UNDO.
     DEFINE INPUT-OUTPUT PARAMETER token AS CHARACTER NO-UNDO.
    END PROCEDURE.

    If the token node is passed I do get an error message from the server that the token is not valid...

    So I ended up using your wrapped call leaving out the token node. Thank you for sharing the information, also to Peter Judge.

    Is there really no way in the new style to leave out an optional parameter?

    Regards,
    Oliver