query preview different than data preview

rbuser

Hi

I have a joined end user template. Ths sql created is below.
You will notice some criteria on field SOPITEMS.DESP_STAT.
it should only display where = PART or NONE.
Inside Data tab on eu reporter, using the Data preview that data is filtered
correctly, but when the report is previewed, this criteria is ignored.
One issue is that in the Sophead table there is also a DESP_STAT field with
different data, and I think the end user reporter is using the wrong field
to include in the criteria.

SELECT SOPHEAD.ORDER_NUM, SOPHEAD.ORD_DATE,
SOPHEAD.SALES_ACC, SOPHEAD.SALES_NAME,
SOPHEAD.ADDRESS1, SOPHEAD.ADDRESS2,
SOPHEAD.ADDRESS3, SOPHEAD.ADDRESS4,
SOPHEAD.POSTCODE, SOPHEAD.TELEPHONE,
SOPHEAD.VAT_NUMBER, SOPHEAD.DEL_NAME,
SOPHEAD.DEL_ADD1, SOPHEAD.DEL_ADD2,
SOPHEAD.DEL_ADD3, SOPHEAD.DEL_ADD4,
SOPHEAD.DEL_PCODE, SOPHEAD.DUE_DATE,
SOPHEAD.ORDERTAKEN, SOPHEAD.INVOICE_NO,
SOPHEAD.CUST_ORDER, SOPHEAD.NO_ITEMS,
SOPHEAD.ALLOC_STAT, SOPHEAD.DESP_STAT,
SOPHEAD.ACC_PRN, SOPHEAD.TOT_NETT,
SOPHEAD.TOT_VAT, SOPHEAD.TOT_GROSS,
SOPHEAD.NOTES_1, SOPHEAD.NOTES_2,
SOPHEAD.NOTES_3, SOPHEAD.CARR_NETT,
SOPHEAD.CARR_TCODE, SOPHEAD.CARR_TRATE,
SOPHEAD.CARR_TAMM, SOPHEAD.CARR_NOM,
SOPHEAD.CARR_DEPT, SOPHEAD.CARR_GROSS,
SOPHEAD.CARR_ONCE, SOPHEAD.CARR_CRGE,
SOPHEAD.SETT_DAYS, SOPHEAD.SETT_DISC,
SOPHEAD.EARLY_PAY, SOPHEAD.G_TAXCODE,
SOPHEAD.G_TAXRATE, SOPHEAD.G_NOMINAL,
SOPHEAD.G_DEPT, SOPHEAD.G_DESC,
SOPHEAD.ITEM_NETT, SOPHEAD.ITEM_TAX,
SOPHEAD.ITEM_GROSS, SOPHEAD.PART_DESP,
SOPHEAD.IMM_ALLOC, SOPHEAD.DEL_NOTE,
SOPHEAD.ANALYSIS, SOPHEAD.EMAIL,
SOPHEAD.FAX_NUMBER, SOPHEAD.CONTACT,
SOPHEAD.CUSTTAXCD, SOPHEAD.TOT_COST,
SOPHEAD.TOT_MARGIN,
SOPITEMS.ORDER_NUM AS ORDER_NUM_2,
SOPITEMS.ITEM_NO, SOPITEMS.S_CODE,
SOPITEMS."UNIQUE" AS UNIQUE_2,
SOPITEMS.S_DESC, SOPITEMS.COMMENT1,
SOPITEMS.COMMENT2, SOPITEMS.STK_TYPE,
SOPITEMS.QUANTITY, SOPITEMS.UNIT_QTY,
SOPITEMS.NUM_UNITS, SOPITEMS.UNIT_SALE,
SOPITEMS.UNIT_PRICE, SOPITEMS.PRICEPUNIT,
SOPITEMS.DISC_PRICE, SOPITEMS.DISCPUNIT,
SOPITEMS.DISCOUNT, SOPITEMS.TAX_CODE,
SOPITEMS.TAX_VALUE, SOPITEMS.NOM_CODE,
SOPITEMS.DEPT,
SOPITEMS.ALLOC_STAT AS ALLOC_STAT_2,
SOPITEMS.DESP_STAT AS DESP_STAT_2,
SOPITEMS.ALLOC_QTY, SOPITEMS.DESP_QTY,
SOPITEMS.QTY2DESP,
SOPITEMS.PART_DESP AS PART_DESP_2,
SOPITEMS.DUE_DATE AS DUE_DATE_2,
SOPITEMS.QTY_PRTED, SOPITEMS.QTY_TOPRT,
SOPITEMS.TOT_COST AS TOT_COST_2,
SOPITEMS.TAX_RATE, SOPITEMS.EXT_PRICE,
SOPITEMS.WEIGHT, SOPITEMS.UNIT_COST,
SOPITEMS.EXT_COST, SOPITEMS.GROSS_MARG,
SOPITEMS.COMMENT3, SOPITEMS.COMMENT4,
SOPITEMS.COMMENT5
FROM SOPHEAD SOPHEAD
INNER JOIN SOPITEMS SOPITEMS ON
(SOPITEMS.ORDER_NUM = SOPHEAD.ORDER_NUM)
WHERE ( SOPITEMS.DESP_STAT = 'NONE' )
OR ( SOPITEMS.DESP_STAT = 'PART' )


Thanks

Andy

nardmoseley

If the Query DataView is a detail linked to a master, then special linking
SQL is generated. See the following tech tip.

-------------------------------------------
Tech Tip: Linking DataViews
-------------------------------------------

Overview
---------

Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.

ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenever the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
The linking logic will only work properly if the Database engine's collation
of the detail query result set is in sync with ReportBuilder's internaly
field link comparison logic.