Skip to main content

Hi. I am attempting to create a Quick Report in IFS CLOUD using type SQL Statement. I successfully created my query and when I use the View Report function to view the report on the client, it successfully displays my report with the necessary parameters I given it. When I export it, the same parameters are kept.

 

Here’s the issue:

When I attempt to schedule the report using Schedule Report, I go through all the necessary steps, schedule the report to run on a given basis and receive an email with the report attached as an Excel file. which is all great. However, the report attached does not follow the WHERE clause in my Query, so old datasets that I do not need are being included, even though I specifically say to not include them (see images).

 

When I use View Report:

Notice how Cancelled and Closed Orders are not appearing (This is what I want)

 

When I Schedule the Report to send an email:

 

In the attached Excel File, closed and cancelled orders appear ( I am using the same Query).

 

The Query being used:

 

 

Also, is there a way I can get rid of the Block Type and Row columns that appear on the attached Excel file when on schedule?

 

Thank you.

 

Hi @arizz,

What is the IFS Cloud Application version that you are using? Since the ‘View Report’ option is working as expected, there is no issue with the SQL query.

Hi @ashen_malaka_ranasinghe,

 

I am not sure what version I am running, however I actually ending up creating a fix to my own issue:

New Query:


SELECT
    po.order_no,
    po.vendor_no,
    Purchase_Order_API.Calc_Order_Total(po.Order_No) as Total_amount,
    supplier_API.get_vendor_name(po.vendor_no) as supplier_name,
    po.State 
FROM
    purchase_order po
WHERE 
    (po.State NOT LIKE nvl('&State_1','po.State')) AND
    (po.State NOT LIKE nvl('&State_2','po.State'))

 

nvl('&State_1','po.State')) - this creates the report parameters, in which I enter the two parameters I do not want returned in the report, and it successfully completes the task.

 

I discovered that that columns objstate and state dissapear from WHERE (in any place in query or subquery) only when they are compared to some string which start from capital letter eg. ‘Cancelled’.
When I replace to upper case everything works.

WHERE objstate <> ‘Cancelled’   is cut off
WHERE upper(objstate) <> ‘CANCELLED’ will work.

 

Reply


Terms & ConditionsCookie settings