Skip to main content
Solved

IFS Cloud: Quick Report Detail - SQL Statement - Scheduling Issue

A
Do Gooder (Customer)
Forum|alt.badge.img+2
  • arizz
  • Do Gooder (Customer)

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.

 

Best answer by arizz

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.

 

View original

ashen_malaka_ranasinghe
Hero (Employee)
Forum|alt.badge.img+11

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.

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/
A
Do Gooder (Customer)
Forum|alt.badge.img+2
  • arizz
  • Do Gooder (Customer)
  • July 2, 2024

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.

 

R
Do Gooder (Partner)
Forum|alt.badge.img+2
  • RafalO
  • Do Gooder (Partner)
  • December 12, 2024

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

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings