Error Exporting Quick Report to Excel

I assume you are trying to export SQL quick report data using IFS EE client (NOT Aurena). In that case if the SQL quick report shows you data those should be exported without any issues like this. 

There is one limitation with Export to excel: 

• Export to Excel

SQL expressions which does not start with "SELECT" clause (Eg: - "WITH") are not supported due to a limitation in the PL/SQL access provider. Output channels could be used in such cases to export data to excel file.

Or are you referring to a different scenario here?

First of all, thanks for the reply!

You are correct, I am using IFS EE, not Aurena. In fact, every other SQL quick reports work just fine, I am able to see the information in IFS if I do “View Report” and if I use “Export to Excel”, the same information appears.

For this specific quick report, unfortunately this doesn’t happen. My query starts with “SELECT” clause and I have a few “LEFT JOIN” inside it, but it should work just fine. I am not sure if there exists any kind of limitation with the type of data that I am exporting, in terms of conversion to excel, but it’s basically just sums inside my “LEFT JOIN” clauses, between certain periods of time.

I’ve taken a different approach to this and instead of using “Export To Excel”, I use “View Report” and then use the output channels to export this information to CSV and it works perfectly. Still, it’s not as efficient doing this when you could simply export it to excel directly.

I’ve experienced similar issues when exporting Quick Reports to Excel whenever they aren't connected to any of my Permission Sets.

I’ve already verified the permission sets, but they are the same for every quick report, and since the others work, this should work as well if that were the problem.

I’ve got a niggling feeling that the report if run as an export rather than visually in the application its run as one of IFS system roles…

Does the report use any IAL’s??

No, I only use standard views from IFS, like customer order lines, inventory part and inventory part planning.

Ok. Is there a way that you can attach your SQL query here? Maybe a text file with the SQL query pasted?

select a.part_no, a.description, a.contract, a.planner_buyer, a.part_status, h.planning_method, h.order_point_qty, h.lot_size, Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand(a.contract,a.part_no,NULL),
case when d.qty_past_month is null then 0 else d.qty_past_month end as QTY_LAST_MONTH,
case when e.qty_2_months_ago is null then 0 else e.qty_2_months_ago end as QTY_2_MONTHS_AGO,
case when f.qty_3_months_ago is null then 0 else f.qty_3_months_ago end as QTY_3_MONTHS_AGO,
case when g.qty_last_6_months is null then 0 else g.qty_last_6_months end as QTY_LAST_6_MONTHS
from inventory_part a

left join (

select sum(a.qty_shipped - a.qty_returned) as QTY_PAST_MONTH, a.part_no, a.contract from customer_order_line a
inner join inventory_part b
on a.part_no = b.part_no and a.contract = b.contract
where a.real_ship_date >= sysdate - 30
group by a.part_no, a.contract) d
on a.part_no = d.part_no and a.contract = d.contract

left join (

select sum(a.qty_shipped - a.qty_returned) as QTY_2_MONTHS_AGO, a.part_no, a.contract from customer_order_line a
inner join inventory_part b
on a.part_no = b.part_no and a.contract = b.contract
where a.real_ship_date >= sysdate - 60
and a.real_ship_date < sysdate - 30
group by a.part_no, a.contract) e
on a.part_no = e.part_no and a.contract = e.contract

left join (

select sum(a.qty_shipped - a.qty_returned) as QTY_3_MONTHS_AGO, a.part_no, a.contract from customer_order_line a
inner join inventory_part b
on a.part_no = b.part_no and a.contract = b.contract
where a.real_ship_date >= sysdate - 90
and a.real_ship_date < sysdate - 60
group by a.part_no, a.contract) f
on a.part_no = f.part_no and a.contract = f.contract

left join (

select sum(a.qty_shipped - a.qty_returned) as QTY_LAST_6_MONTHS, a.part_no, a.contract from customer_order_line a
inner join inventory_part b
on a.part_no = b.part_no and a.contract = b.contract
where a.real_ship_date >= sysdate - 180
group by a.part_no, a.contract) g
on a.part_no = g.part_no and a.contract = g.contract

left join inventory_part_planning h
on a.part_no = h.part_no and a.contract = h.contract

where a.contract like UPPER('&Site%')
and a.part_no like '&Part_no%'

Thanks. I couldn’t find any problems in this query. As it returns data when the report is viewed while no data found when export to excel, it is good that you create a support case to IFS. It can be a bug or a new limitation but it will require some investigations to figure that out. 

Thanks. I will do that then.

Can you check this?

If there are views which checks if a user has access to a site or company try adding IFSSYS to that site or company.

I have access to every site and company that I query, so I don’t think that’s the problem.

Hi @jquinteiro ,

select a.part_no,
       a.description,
       a.contract,
       a.planner_buyer,
       a.part_status,
       h.planning_method,
       h.order_point_qty,
       h.lot_size,
       Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand(a.contract,
                                                            a.part_no,
                                                            NULL) deneme,
       case
         when d.qty_past_month is null then
          0
         else
          d.qty_past_month
       end as QTY_LAST_MONTH,
       case
         when e.qty_2_months_ago is null then
          0
         else
          e.qty_2_months_ago
       end as QTY_2_MONTHS_AGO,
       case
         when f.qty_3_months_ago is null then
          0
         else
          f.qty_3_months_ago
       end as QTY_3_MONTHS_AGO,
       case
         when g.qty_last_6_months is null then
          0
         else
          g.qty_last_6_months
       end as QTY_LAST_6_MONTHS
  from inventory_part a
  left join (select sum(a.qty_shipped - a.qty_returned) as QTY_PAST_MONTH,
                    a.part_no,
                    a.contract
               from customer_order_line a
              inner join inventory_part b
                 on a.part_no = b.part_no
                and a.contract = b.contract
              where a.real_ship_date >= sysdate - 30
              group by a.part_no, a.contract) d
    on a.part_no = d.part_no
   and a.contract = d.contract
  left join (select sum(a.qty_shipped - a.qty_returned) as QTY_2_MONTHS_AGO,
                    a.part_no,
                    a.contract
               from customer_order_line a
              inner join inventory_part b
                 on a.part_no = b.part_no
                and a.contract = b.contract
              where a.real_ship_date >= sysdate - 60
                and a.real_ship_date < sysdate - 30
              group by a.part_no, a.contract) e
    on a.part_no = e.part_no
   and a.contract = e.contract
  left join (select sum(a.qty_shipped - a.qty_returned) as QTY_3_MONTHS_AGO,
                    a.part_no,
                    a.contract
               from customer_order_line a
              inner join inventory_part b
                 on a.part_no = b.part_no
                and a.contract = b.contract
              where a.real_ship_date >= sysdate - 90
                and a.real_ship_date < sysdate - 60
              group by a.part_no, a.contract) f
    on a.part_no = f.part_no
   and a.contract = f.contract
  left join (select sum(a.qty_shipped - a.qty_returned) as QTY_LAST_6_MONTHS,
                    a.part_no,
                    a.contract
               from customer_order_line a
              inner join inventory_part b
                 on a.part_no = b.part_no
                and a.contract = b.contract
              where a.real_ship_date >= sysdate - 180
              group by a.part_no, a.contract) g
    on a.part_no = g.part_no
   and a.contract = g.contract
  left join inventory_part_planning h
    on a.part_no = h.part_no
   and a.contract = h.contract

-- Changed Part
 where a.contract like UPPER('&Site')  || '%'
   and a.part_no like '&Part_no'  || '%'
--Changed Part

Try this.

another option would be to create the quick report as an IAL instead, and then change the quick report to fetch data from that IAL - that might resolve the export issue possibly (however, no guarantee - just something I would try).

Thanks @ridvanyazici , this solved it!!