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.
Hi
As far as i know there isn’t a way you can directly use the Quick logic in a data source. One way you can do this would be by creating an IAL object using the same SQL you are using for your quick report and using the IAL view as the view to use in your data source. If your IAL object name is “my_co”, by using “ifsinfo.my_co” as the view name in the lobby designer you can expose the same logic as you do in your quick report. Please note that you would need to manually key in the column names as you will not be able to look up the column list in an IAL like you would do with a standard view.
This is of course assuming that you are trying to create a list element. If you just need to add a link to the quick report from the lobby you can do that using a link element.
Cheers.
Thx!
It’s a rather complex GROUPNG SQL with 2 subqueries, so if the fo the former I don’t think I’ll be able to cut n paste my “columns.”
I’ll try the latter approach- will I still get a chart that I can drill down nto? My gaol is to use htis in a Lobby for management.
Hi @Devbob,
As long as you alias the column names in your IAL SQL you can refer to them using the alias in your data source. Might need to tinker around a bit with the sub queries specially is you have input parameters defined against them but the additional work will pay off when you get it in to an IAL specially if you are looking to get both a list and a chart out of it.
you can potentially create a chart with drill down by setting up custom navigation depending on the complexity of the data source.
Cheers.
thx, i will try that.
thx for your reply.
I tried matching the columns from my IAL and received this error:
IAL:
select
state as Status,
count(*) as "All Orders",
to_char( SUM (( SELECT
SUM( BASE_SALE_UNIT_PRICE * (BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) )
FROM &AO..CUSTOMER_ORDER_LINE col
WHERE col.order_no = co.order_no
and ( BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) > 0
and LINE_ITEM_NO <= 0
and OBJSTATE in ('Delivered', 'PartiallyDelivered', 'Picked', 'Released', 'Reserved')
)) , '$99,999,999.99') "All Orders Gross Amount",
sum( case when TRUNC(wanted_delivery_date) between TRUNC(sysdate - INTERVAL '2' DAY) and TRUNC(sysdate - INTERVAL '1' DAY) then
1 else 0 end ) as "1-2 Days Past Due",
sum( case when TRUNC(wanted_delivery_date) between TRUNC(sysdate - INTERVAL '5' DAY) and TRUNC(sysdate - INTERVAL '3' DAY) then
1 else 0 end ) as "3-5 Days Past Due",
sum( case when TRUNC(wanted_delivery_date) between TRUNC(sysdate - INTERVAL '10' DAY) and TRUNC(sysdate - INTERVAL '6' DAY) then
1 else 0 end ) as "6-10 Days Past Due",
sum( case when TRUNC(wanted_delivery_date) <= TRUNC(sysdate - INTERVAL '11' DAY) then
1 else 0 end ) as "+10 Days Past Due",
to_char( SUM (
case when TRUNC(wanted_delivery_date) <= TRUNC(sysdate - INTERVAL '1' DAY) then
( SELECT
SUM( BASE_SALE_UNIT_PRICE * (BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) )
FROM &AO..CUSTOMER_ORDER_LINE col
WHERE col.order_no = co.order_no
and ( BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) > 0
and LINE_ITEM_NO <= 0
and OBJSTATE in ('Delivered', 'PartiallyDelivered', 'Picked', 'Released', 'Reserved')
)
else 0 end
) , '$99,999,999.99') "Past Due Order Gross Amount"
from &AO..CUSTOMER_ORDER co
where state not in ('Invoiced/Closed', 'Cancelled') and contract in ('W','E22')
and order_no not like 'AD%'
and order_id <> 'RO'
group by ROLLUP(state)
If there were detailed IFS Instructions on how to map/split out your IAL to the Designer that would be great.
I’ve looked at pre-existing SQLs and they aren’t advanced like this one. The example they give are basic and really don’t help since they simple.
If IFS can’t handle complex SQLs like this they should list limitations in the documentation so customers don’t struggle.
Hi
in the data-source colums, instead of giving SUM(...) you can define the alias field name which has been defined in the IAL.
Your IAL might need a little change, you will need field-name without space bar, and start the field name with alphabet. Oracle cannot support field-name started with number.
ex. select state as Status,
count(*) as All_Orders, -- instead of “All Orders”
...
sum(case
when TRUNC(wanted_delivery_date) between
TRUNC(sysdate - INTERVAL '5' DAY) and
TRUNC(sysdate - INTERVAL '3' DAY) then
1
else
0
end) as Days_Past_Due_3_5 , -- instead of “3 - 5 Days Past Due”
...
Data source definition:
thx!
Hello,
You can create custom view with your complex query, an then use this new view for LOBBY.
Of course you have to manually put names of columns in Lobby Data Source.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.