I’m creating a Lobby to display a quick report over open custom orders. I have the quick report created and running. Then i tried creating a Lobby Data Source Designer using the quick report- I selected SQL but there’s no where to select my quick report’s SQL, or paste in m SQL statement. How/where do I do that? I then want to use this DataSource in the Lobby Element Designer.
I have a Quick Report that Groups By dollar columns, then does a UNION to total those $$$ columns. It has a join column from another table (btl.amount), keying on primary keys/columns.The challenge I’m running into is that, on the UNION SQL, I’m joining to a table to get that single $$$ column’s total (SUM(btl.amount)). Since the join is not at the detail level of each main table’s rows, it’s overstating the SUM() total $$$. (kind of hard to explain). SELECT t.reporting_date "Reporting Date", t.district_code "BU Code", t.district_desc "BU Desc", t.contract "Site", to_char( SUM(t.Daily_Order_Entry), '99,999,999.99') "Daily Order Entry", to_char( SUM(t.MTD_Order_Entry), '99,999,999.99') "MTD Order Entry", to_char( SUM(t.Daily_Billings), '99,999,999.99') "Daily Billings", to_char( SUM(t.MTD_Billings), '99,999,999.99') "MTD Billings", to_char( MAX(btl.amount), '99,999,999.99') "Forecast", to_char( SUM(t.Past_Open_Orders), '99,999,999.99') "Past Open Orders", to_char( SUM(t.Curr_
Does anyone know how to make a parameter mandatory? By default they are all optional. Also, is there a way to set a default value for a parameter? Lastly, is there IFS documentation or a manual on details like this? I’ve seen IFS pages that have general info, but nothing on technical details like the above or what else is doable/possible. As we start to develop our company’s custom reports I’m getting ideas and would like to know the capabilities and what’s possible with QuickReports.
We have an inventory history quick report where we want to pull 4 columns from the latest PO Receipt for a contract/part.I’ve coded a solution however it hangs when I run the report. I’m guessing there’s a better way to code a subquery that pulls 4 columns of the latest date PO receipt.( SELECT max(voucher_date_ref) FROM INVOICE_LEDGER_ITEM_SU_QRY where po_ref_number = ivpp.order_no group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'MM/DD/YYYY') order by invoice_date DESC FETCH NEXT 1 ROWS ONLY) as voucher_date,( SELECT max(voucher_no_ref) FROM INVOICE_LEDGER_ITEM_SU_QRY where po_ref_number = ivpp.order_no group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'MM/DD/YYYY') order by invoice_date DESC FETCH NEXT 1 ROWS ONLY) as voucher_#,( SELECT max(invoice_date) FROM INVOICE_LEDGER_ITEM_SU_QRY where po_ref_number = ivpp.order_no group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'M
We have a lobby by week that is not sorting the MM/DD date correctly since it treats it as text.Can we:1.) change it to a Date (didn’t work in the DataSource) so it sorts/orders correctly.2.) Add a leading zero to single-digit months and days.
We’re getting this error when updating a custom field in Customer_Info_Cfv created/added at data migration.It’s a simple update with just the CustomerNo and the new custom field, MS Set.Any ideas what to correct?
Quick question, on other platforms there’s an EXCEPTION JOIN that is exclude rows from Table A that aren’t in TABLE B. What is the equivalent in Orace/IFS?We created a custom table of customers to exclude. So when we report on CustomerOrders we only want orders that don’t have a Customer# in Table B.select order_no,part_no,base_sale_unit_pricefrom CustomerOrderLine colEXCEPTION JOIN Exclude_Customers_Cfv ec on ec.cf$_customer_id = col.customer_idWhere col.contract = 'W'
We have a quick report where we’d like to add 2 business days (exclude the weekends) to the CustomerOrder DateEntered.We usually do this but this includes weekends- is there a date function that skips Saturday and Sunday?I’m familiar with work_time_calendar_api.get_work_days_between() but don’t see how that can do the above.
We have quick report that takes excessively long to execute and return results. Any ideas on increasing performance?There’s only 5 rows in the result set (see below).select pm.Part_Main_Group as Trademark,l1p.contract,l1p.part_no,ip.description,l1f.ms_set as MS_Set,trunc(l1f.ms_date) as Forecast_Date,extract( MONTH from to_date(l1f.ms_date)) as Month,to_char(l1f.ms_date,'yyyymm') as YearMonth,l1f.forecast_lev0 as Forecast_Qty,NVL((select sum(rega1app.Customer_Order_API.Get_Ord_Gross_Amount(ORDER_NO))from rega1app.customer_info_cfv cicleft join rega1app.cust_ord_invo_stat col on l1p.contract = col.contract and l1p.part_no = col.part_no and cic.customer_id = col.customer_no and ( extract( YEAR from to_date(col.invoice_date)) = extract( YEAR from to_date(l1f.ms_date)) ) and ( extract( MONTH from to_date(col.invoice_date)) = extract( MONTH from to_date(l1f.ms_date)) ) where ( l1p.contract = 'W' and l1f.ms_set = cic.cf$_ms_set or l1p.
This quick report’s subquery that has FETCH FIRST 1 ROWS ONLY to pull the first PO is only returning one row for the entire report; not for each part#:SA-807 has a qualifying row as well; we should see a Next Supply Qty and Next Supply Date as we ll as SA-811. selectip.contract,ip.part_no,ip.type_code_db,ip.part_product_code as Product_Code,ip.type_designation,sp.catalog_group as Sales_Group,ipp.safety_stock,ipcp.qty_available as Available_Inventory,ipp.safety_stock - ipcp.qty_available as Over_Short,( SELECTosd.qty_supply as Next_Supply_Qtyfrom ORDER_SUPP_DEM_EXT_TMP_VIEW osd where osd.contract = ip.contract and osd.part_no = ip.part_no and ( osd.order_supply_demand_type = 'Shop Order' and osd.status_Desc in ('Planned', 'Started', 'Released')or osd.order_supply_demand_type = 'Purch Order' and osd.status_Desc in ('Planned', 'Released', 'Received') ) order by osd.date_requiredFETCH FIRST 1 ROWS ONLY ) as Next_Supply_Qty,( SELECTosd.date_required as Next_Supply_Datefrom ORDER_SUPP_
We currently have a quick report that has 12 monthly columns, one for each month: I’ve hard-coded each of the 12 months as noted below. Now they would like the months to dynamically start based on the selected Starting Date, so if they enter 5/1/2020 it’ll load the 12 months starting with June-JUN JUL AUG SEPT NOV DEC JAN FEB MAR APR MAYis that possible with quick report SQL?(the RW_FORECAST_BY_MONTH is an IAL that contains the extracted Month column and is ordered by Date; the quick report’s SQL groups it into the months)
We have a question about performance in quick reports that use one or more IAL’s.Let’s say we have 3 IALs over CustomerOrderLines (and other tables but this is the main one), each building upon the previous one. In the 3rd IAL that the quick report uses, we want to pull the DistrictCode that is in CUST_ORD_CUSTOMER_ADDRESS_ENT.Will it affect performance if we join CUST_ORD_CUSTOMER_ADDRESS_ENT in the 1st IAL or the quick report?Any best practice to follow on something like this?
Are we able to add specific dates as Holidays to a calendar?Our use would be quick reports that would use the work_time_calendar_api.Get_Work_Minutes API.
We have need to calculate the number of hours between 2 dates, minus weekends. We’re using the OrderHistory tab’s rows.I have it working for just the number of days between 2 dates, and then did it for the number of hours (I simply removed TRUNC() around the 2 dates).But that included weekends and now the team wants just business days. I was able find a solution for 2 dates, but it doesn’t include the hours.I’m thinking the solution is to add HH24:MM:SS to the to_date() function but am receiving errors.How do I format to_date() to include hours in the calculation? SELECTSite,OrderNo,status,lag_status,dt,lag_dt,TO_CHAR(to_date(dt,'YYYY/MM/DD'),'DY','nls_date_language=english') as DT_DAY,TO_CHAR(to_date(lag_dt,'YYYY/MM/DD'),'DY','nls_date_language=english') as LAG_DT_DAY, (to_date(dt,'YYYY/MM/DD') - to_date(lag_dt,'YYYY/MM/DD') ) - ((((TRUNC(to_date(dt,'YYYY/MM/DD'),'D'))-(TRUNC(to_date(lag_dt,'YYYY/MM/DD'),'D')))/7)*2) - (CASE WHEN TO_CHAR(to_date(lag_dt,'YYYY/MM/DD'),'DY','nls_date_
We were wondering if the WITH clause works in IFS. I tried one using Oracle SQL Developer and it hangs up when I run it; it doesn’t return any results. We haven’t put it in a Quick Report yet.We’re reading InventoryPartsPerPeriod and trying to retrieve recent PO Parts until the ReceiptQty matches on OnHandQty.Could it be because the tables in the WITH clause are too large?WITH total_invoices ( contract, part_no, stat_period_no, quantity, i_total ) AS ( SELECT ivpse.contract, ivpse.part_no, stat_period_no, ivpse.quantity, SUM( quantity ) OVER ( ORDER BY stat_year_no, stat_period_no, contract, part_no ) FROM REGA1APP.INVENTORY_VALUE_PART_SUM_EXT ivpse),total_payments ( p_contract, p_part_no, planned_received_date, buy_qty_due, p_total ) AS ( SELECT poi.order_no, poi.part_no, planned_receipt_date, poi.buy_qty_due, SUM( buy_qty_due ) OVER ( ORDER BY poi.planned_receipt_date, contract, part_no ) FROM REGA1APP.PURCHASE_ORDER_LINE_PART poi)SELECT contract,part_no,
We have a lobby that works with the default Site# of W. When we try to run it for other Sites, the first execution throws an error, but then if we run it again it works.Am I formatting the parameter correctly as where contract LIKE NVL('$SITE$','%')?
We have a Counter lobby element that when we drill-down it is loading all CustomerOrders- not just the ones for the SQL behind this element.The SQL uses an IAL - RW_ORDERS_SHIPPED_TODAY2, which is built on another IAL (RW_ORDERS_SHIPPED_TODAY).I’ve done other lobbies where the drill-down works, so I don’t see what’s different with this one, unless somehow IALs aren’t recognized? select count(*) from ifsinfo.RW_ORDERS_SHIPPED_TODAY2where contract = 'W'
Trying to sort a column by order process and not alphabetically for a Lobby and it doesn’t like the as Status format.The similar quick report works fine.Is this not possible in a Lobby? case when state = 'Blocked' then '1 - Blocked' when state = 'Planned' then '2 - Planned' when state = 'Released' then '3 - Released' when state = 'Reserved' then '4 - Reserved' when state = 'Picked' then '5 - Picked' when state = 'Partially Delivered' then '6 - Partially Delivered' when state = 'Delivered' then '7 - Delivered' end as Status Quick report has desired seqeunce: Lobby is currently alphabetically:
How can I place multiple elements in one green box in Lobby design-mode?This is for visual layout. I noticed this Lobby has it, but can’t figure out in design mode how they did it. I’m not able to drag n drop an element inside the green box. In my lobbies it’s always a one-to-one; each element has its own green box.
We’re getting run-time errors due to date logic/formatting/selections. Is there any standardized set of date formatting rules?I have numerous Oracle SQL websites detailing date functions, but we run into issues every now and then, with each case having slightly different requirements/logic.Scenarios:1.) SQL in an IAL runs fine in the quick report using it, but fails when run in the SQL Query Tool.2.) A quick report runs fine (and also runs fine with RMB Output>Save As...) but if you take Export to Excel it fails.For instance, our latest situation is this error when running an IAL’s logic from the SQL Query Tool that doesn’t occur when running a quick report over the same IAL: IAL logic:selectsoi.sub_program_id as PGM,cois.district_code as Class,omi.commission_receiver as Receiver,max(cin2.name) as Receiver_Name,max(cia.name) as Receiver_Company,omi.customer_id as Dealer,max(cin.name) as Dealer_Name,max(cois.country_code) || ' - ' || max(cia.state) as CN_ST,sum(CASE WHEN trunc(cois.i
I have a List Element and was wondering how do I format the numbers as currency? You’re able to do it for Lists and Pie Charts numbers but not Lists.
I’d like to format a column heading with all caps as it is an acronym COGS, but haven’t figured out how to do it; it seems IFS always translates it to camel case. The closest I got was with a space between each char, but I’d like it to be w/o spaces.
We have a Lobby Element that summarizes orders by status for 4 date periods. Now we want drill-down capabilities so when they click on any of the Status rows they see the detail behind it- Order#, Status, Date, Quantity, Price. I couldn’t find any documentation on this- does some exist? I didn’t see an Element that has this; Link looks like it just drills down into a screen, not a report. Would it/could it drill down into the Customer Order screen with parms to restrict results?
I am editing an existing Lobby we created and added a Description element. It’s on the page but when I view the Lobby Page in another session as a User, it doesn’t appear. If I then leave the Lobby Element Designer and reenter my Element is gone. How do I “save it” so it shows to the user? (plus I get an error- perhaps that’s why it doesn’t show?)
Does anyone know of any IFS Documentation on how to map IAL columns to the Designer ? Here’s what we’re trying and we get an error: IAL: select col.COMPANY as Company, col.CONTRACT as Site, col.customer_no || ' - ' || max(rega1app.CUST_ORD_CUSTOMER_API.Get_Name(col.CUSTOMER_NO)) as Customer_Name, pm.Part_Main_Group "Trademark", ROUND(SUM( col.revised_qty_due * col.sale_unit_price), 2) "Total_Revenue", ROUND(SUM( col.revised_qty_due * col.cost ), 2) "Total_Sales_Cost", ROUND( SUM( ((col.revised_qty_due * col.sale_unit_price) - (col. revised_qty_due * col.cost))), 2) "Margin", ROUND(AVG( case when col.sale_unit_price <> 0 then (((col.revised_qty_due * col.sale_unit_price) - (col.revised_qty_due * col.cost)) / (col.revised_qty_due * col.sale_unit_price)) * 100 else 0 end ) , 2) "Margin_%" from REGA1APP.CUSTOMER_ORDER_LINE col left join REGA1APP.CUSTOMER_ORDER co on col.ORDER_NO = co.ORDER_NO left join REGA1APP.PART_CATALOG pm on col.part_no = pm.part_no left j
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
