Allow variables from the report filter screen to be passed into the report queries.(.qpr) For example if you have a complicated report prequery that gets data from sql server, summarizes it and then throws it into a view for the report and further filtering, Eg. The bold section should be the parameter of @@datetransfered from the report filter screen PreQuery2 /* get all movement from today to the as at date by store */ CREATE VIEW ViewItemMovement AS SELECT storeid, itemid, sum(InventoryTransferLog.Quantity) AS Quantity, case when InventoryTransferLog.Type = 1 THEN 'POXFER' ELSE 'ADJUST' END AS Type from InventoryTransferLog where InventoryTransferlog.DateTransferred between CONVERT(DATETIME, '2/1/2006 00:00:00', 101) and getdate() group by InventoryTransferLog.storeid, InventoryTransferLog.itemid, InventoryTransferLog.type UNION SELECT TransactionEntry.storeid,itemid, sum(TransactionEntry.Quantity) AS Quantity, 'SALES' AS Type FROM TransactionEntry LEFT JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND [Transaction].StoreID = TransactionEntry.storeid where [Transaction].Time between CONVERT(DATETIME, '2/1/2006 00:00:00',
101) and getdate() group by TransactionEntry.storeid, TransactionEntry.itemid [GO] /* as at onhand quantity = todays on hand - item movement */ create view viewasatonhandqty as select storeid, itemid, sum(quantity) as quantity from ItemDynamic group by storeid,itemid union select storeid, itemid, sum(quantity) as quantity from ViewItemMovement group by storeid, itemid [GO] /* summarize all data by item and combine to item details */ create view viewtemp1 as select itemid, sum(case when storeid = 1 then quantity end) as DTASATONHAND, sum(case when storeid = 2 then quantity end) as LSASATONHAND, sum(case when storeid = 3 then quantity end) as LNASATONHAND from viewasatonhandqty group by itemid---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane.