SQL

rbuser

I need to have the following SQL script in my EU report with the
SALESORDER_1.SHOWID as an autosearch criteria. The problem is that I cannot
join one table to two other tables such as is done in the following script.

SELECT DISTINCT SALESORDER_1.SHOWID,
SALESORDERDET_1.SONBR,
SALESORDERDET_1.LINENBR,
SALESORDERDET_1.ITEMID,
SALESORDERDET_1.ITEMDESCR,
SUM(SALESORDERDET_1.QTY) SUM_SALESORDERDET_1_QTY,
Sum(GSNTRACKING_1.QtyOut - GSNTRACKING_1.QtyIn -
GSNTRACKING_1.QtyTrans) Sum_GSNTRACKING_1_QtyOut
FROM SALESORDER SALESORDER_1
LEFT OUTER JOIN SALESORDERDET SALESORDERDET_1 ON
(SALESORDERDET_1.SONBR = SALESORDER_1.SONBR)
LEFT OUTER JOIN GSNTRACKING GSNTRACKING_1 ON
(GSNTRACKING_1.LOCATION = SALESORDER_1.showid AND GSNTRACKING_1.ITEMID
= SALESORDERDET_1.ITEMID)
WHERE ( SALESORDER_1.SOSTATUS <> 'X' )
AND ( SALESORDERDET_1.STATUS <> 'X' )
AND ( SALESORDER_1.SHOWID = '062204B' )
GROUP BY SALESORDER_1.SHOWID,
SALESORDERDET_1.SONBR,
SALESORDERDET_1.LINENBR,
SALESORDERDET_1.ITEMID,
SALESORDERDET_1.ITEMDESCR

Will there ever be a way to do this? When will we have the capability to
create any SQL statement in ReportBuilder EU Solution that we can type into
DBExplorer and still have the autosearch capabilities?
On another note I do not see the benifit in using "MagicSQL" instead of the
actual datasourcing of datasets. This has only limited what reports I can
create through the EU reporting solution!

Thanks All,
Branden Johnson

nardmoseley

Thanks for the feedback.

The current options are to

1. Create custom dataviews

For an example see RBuilder\Demos\EndUser\CustomDataViews

2. Write code to manually handle the autosearch and linking. For an example
download

www.digital-metaphors.com/tips/EditSQLAndSearch.zip

www.digital-metaphors.com/tips/EditSQLAndLink.zip



--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com