I am using CR 10 for Sage. It is bundled in with MAS 500 Enterprise Application. MAS uses SQL Server.
I need all records from the Inventory table which are not in the Inventory Transaction table for a particular date range. So I used an SQL command as my data source with the following query:
select b.ItemID, c.QtyOnHand, c.WhseID, a.StdUnitCost, a.CreateDate
from dbo.timInventory a Inner Join dbo.timItem b
on a.ItemKey = b.ItemKey
Inner Join dbo.vdvInventoryStatus c
on a.ItemKey = c.ItemKey
and a.WhseKey = c.WhseKey
Where b.CompanyID = 'LC1'
And Not exists
(SELECT f.ItemID
FROM dbo.timInvtTran e Inner Join dbo.timItem f
on e.ItemKey = f.ItemKey
WHERE e.TranType in ('810', '706')
and e.CompanyID = 'LC1'
and e.TranDate between '2009-01-09' and '2009-01-20'
and a.ItemKey = e.ItemKey)
Order By b.ItemID
Query works perfectly and returns expected results. However, when I add this report as a Custom Report in MAS 500, no data is returned. It brings back a single blank sheet of paper.
Does anyone know for sure if reports created using SQL Commands can be processed in MAS 500? If so, can someone please let me know what I need to do. If not, anyone know how I can get my result using another method; maybe through an sql expression. Trying to avoid using a subreport, but will do it if absolutely necessary.
Thanks,
DLee
I need all records from the Inventory table which are not in the Inventory Transaction table for a particular date range. So I used an SQL command as my data source with the following query:
select b.ItemID, c.QtyOnHand, c.WhseID, a.StdUnitCost, a.CreateDate
from dbo.timInventory a Inner Join dbo.timItem b
on a.ItemKey = b.ItemKey
Inner Join dbo.vdvInventoryStatus c
on a.ItemKey = c.ItemKey
and a.WhseKey = c.WhseKey
Where b.CompanyID = 'LC1'
And Not exists
(SELECT f.ItemID
FROM dbo.timInvtTran e Inner Join dbo.timItem f
on e.ItemKey = f.ItemKey
WHERE e.TranType in ('810', '706')
and e.CompanyID = 'LC1'
and e.TranDate between '2009-01-09' and '2009-01-20'
and a.ItemKey = e.ItemKey)
Order By b.ItemID
Query works perfectly and returns expected results. However, when I add this report as a Custom Report in MAS 500, no data is returned. It brings back a single blank sheet of paper.
Does anyone know for sure if reports created using SQL Commands can be processed in MAS 500? If so, can someone please let me know what I need to do. If not, anyone know how I can get my result using another method; maybe through an sql expression. Trying to avoid using a subreport, but will do it if absolutely necessary.
Thanks,
DLee