exceloraclesoapbi-publisher

Oracle BI Report based on Excel template has no data when called through ReportService?


Oracle BI Publisher 12.2.1.4.0
I have a set of reports that I want to run using runReport Report Service. All the reports are working well when started within browser. The ones based on excel template when run through Report Service has just a header row with column labels. No data at all. Those based on BI template when asked for "xlsx format" through Report Service are all ok.
Those whose templates are created in excel (2019) using Oracle BI Publisher Desktop 12.2.1.4.0 add-on work ok unless called with SOAP envelope runReport action.
I'm doing something wrong, but could not find what. Not sure if the problem is in passing/formating parameters (dates especialy), or something else is missing or wrong in my SOAP:

<?xml version="1.0" encoding="utf-8"?>
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soap-env:Body>
        <ns0:RunReport xmlns:ns0="http://xmlns.oracle.com/oxp/service/v2">
                <ns0:reportRequest>
                    <ns0:attributeCalendar>Gregorian</ns0:attributeCalendar>
                    <ns0:attributeFormat>excel</ns0:attributeFormat>
                    <ns0:attributeLocale>hr-Hr</ns0:attributeLocale>
                    <ns0:attributeTemplate>_TO_EXCEL_WEEKS_Cards.xls</ns0:attributeTemplate>
                    <ns0:flattenXML>true</ns0:flattenXML>
                    <ns0:parameterNameValues>
                        <ns0:listOfParamNameValues>
                            <ns0:item>
                                <ns0:name>DateFrom</ns0:name>
                                <ns0:values>
                                    <ns0:item>2022-05-01T00:00:00.000+02:00</ns0:item>
                                </ns0:values>
                            </ns0:item>
                            <ns0:item>
                                <ns0:name>DateTo</ns0:name>
                                <ns0:values>
                                    <ns0:item>2022-05-28T00:00:00.000+02:00</ns0:item>
                                </ns0:values>
                            </ns0:item>
                            <ns0:item>
                                <ns0:name>DateDeliveredTo</ns0:name>
                                <ns0:values>
                                    <ns0:item>2022-05-28T00:00:00.000+02:00</ns0:item>
                                </ns0:values>
                            </ns0:item>
                            <ns0:item>
                                <ns0:name>Marcet</ns0:name>
                                <ns0:values>
                                    <ns0:item>*</ns0:item>
                                </ns0:values>
                            </ns0:item>
                        </ns0:listOfParamNameValues>
                    </ns0:parameterNameValues>
                    <ns0:reportAbsolutePath>/MKTP/06-Sell/70_Offer/Reports/70_20_WEEKS_TO_EXCEL_TODAY_2022_REPORT.xdo</ns0:reportAbsolutePath>
                    <ns0:sizeOfDataChunkDownload>-1</ns0:sizeOfDataChunkDownload>
                    <ns0:byPassCache>True</ns0:byPassCache>
                </ns0:reportRequest>
            <ns0:userID>username</ns0:userID>
            <ns0:password>password</ns0:password>
        </ns0:RunReport>
    </soap-env:Body>
</soap-env:Envelope>

I did try different attributeFormat tags (it works only when "excel", otherwise error), tryed to change the flattenXML tag (no data with true or false). Template is very simple with one group of repeating records and the header is always there, so there is not the problem with template name/location.
XDO_METADATA:

Data Constraints:       
XDO_?XDOFIELD1? <?DOCTYPE?> 
XDO_?XDOFIELD2? <?DOC_ID?>  
XDO_?XDOFIELD3? <?STATUS?>  
XDO_?XDOFIELD4? <?ORDER_ID?>    
XDO_?XDOFIELD5? <?DOC_YEAR?>    
XDO_?XDOFIELD6? <?DOC_NUM?> 
XDO_?XDOFIELD7? <?DOC_DATE?>    
...
... 

XDO_GROUP_?XDOG1?   <xsl:for-each select=".//G_1">  </xsl:for-each>

All the other reports (pdf-s) working ok whatever way they are run.

Thanks for your time...


Solution

  • Found it. The problem is parameter in BI defined as DATE. Changed it to text, adjusted the WHERE clause in dataset to something like

     column Between To_Date(date_param1, 'dd.mm.yyyy') And To_Date(date_param2, 'dd.mm.yyyy')
    

    and shortend the date parameter values in SOAP Envelope. It works now.
    Anyway, I would like to know if there is a solution where I could keep my parameters in BI to stay of date type. This is a lot of boring things to do.