Custom report. Blank data
Problem reported by eric de guzman - 3/6/2015 at 9:49 PM
Not A Problem
AR1005.
Edited SQL.in Report Designer
Verified that it is working properly in preview. But once loaded into Atrex itself, it only shows blank page
Im baffled as why this is so. Is there any particular reason this happens or is it by design?
 
anyway, this is a sample of the modified SQL
 
SELECT ar.TransDate Date, ar.SequenceNumber S,
       if(ar.Reference + ' | ' + ar.Terms is null then '   '  else ar.Reference + ' | ' + ar.Terms) RefT,
       ARTransTypeDescr(ar.TransType) + ': ' + Cast(ar.TransNum as VarChar(15)) Transaction, 
       cust.CustNum, Cast(if(cust.FirstName is null then Cust.LastName else cust.LastName + ', ' + Cust.FirstName) as varCHAR(80)) Customer,
       payment.Amount, 
       payment.Amount as AmountCollected, 
       payment.PaymentType "Payment Type" 
FROM ar ar INNER JOIN cust cust ON 
     (cust.CustNum = ar.CustNum)
      INNER JOIN Payment Payment ON 
     (Payment.TransNum = ar.TransNum)
      AND (Payment.TransType = ar.TransType)
WHERE ( ar.TransDate = CURRENT_DATE )
UNION ALL
SELECT ar.TransDate Date, ar.SequenceNumber S,
       if(ar.Reference + ' | ' + ar.Terms is null then '   '  else ar.Reference + ' | ' + ar.Terms) RefT,
       ARTransTypeDescr(ar.TransType) + ': ' + Cast(ar.TransNum as VarChar(15)) Transaction, 
       cust.CustNum, Cast(if(cust.FirstName is null then Cust.LastName else cust.LastName + ', ' + Cust.FirstName) as varCHAR(80)) Customer,
       ar.Balance Amount, 
       0.00 as AmountCollected,
       'Account' "Payment Type" 
FROM ar ar INNER JOIN cust cust ON 
     (cust.CustNum = ar.CustNum)
WHERE (( ar.TransDate = CURRENT_DATE )
  AND  (( ar.Balance <> 0 ) or (ar.TotalPaid = 0)))
ORDER BY ar.TransDate, ar.SequenceNumber

3 Replies

Reply to Thread
0
Mark Culos Replied
Employee Post
Eric,
 
There is most likely an issue with the event BeforeOpenDataPipelines.as the event is not executed in the preview by default.
0
eric de guzman Replied
Hmmm, now i dont know how to proceed with troubleshooting it.
can you point me to the proper direction?
iv reverted the SQL back to the original with just one modificattion. increased the varchar tp 50 and it blanked too.
0
Mark Culos Replied
Employee Post
I would revert to the standard report to get back to a know working condition.

The report is dynamically structured at run time using the mentioned event so that is where you must make your modifications and ensure that you do not alter the field aliases as those are used to identify the data to the cross-tab.

Reply to Thread