timbraun
asked on
RMS report, filter Last Month
Is there a way in Retail Management System reports to have the filter date automatically show Last Month dates when opening a report?
I know I can specify Last Month in the drop down list upon opening, but is there a way so the dates of "Last Month" (e.g. right now it would be May 1-31) will be automatically put in as a filter? The .qrp file is editable and I can filter by <Today> and <MonthStart>, but I cannot get anything to work that has to do with Last month...
Any ideas?
I know I can specify Last Month in the drop down list upon opening, but is there a way so the dates of "Last Month" (e.g. right now it would be May 1-31) will be automatically put in as a filter? The .qrp file is editable and I can filter by <Today> and <MonthStart>, but I cannot get anything to work that has to do with Last month...
Any ideas?
Please send me the TablesQueried value from the report, I might be able to help
ASKER
Thanks for responding. The tables queried are as follows.
FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.Transacti onNumber = [Transaction].TransactionN umber
INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber
LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID
LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountR easonCodeI D = ReasonCodeDiscount.ID
LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChange ReasonCode ID = ReasonCodeTaxChange.ID
LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnRea sonCodeID = ReasonCodeReturn.ID
LEFT JOIN Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID
LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID
LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID
LEFT JOIN QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityD iscountID = QuantityDiscount.ID
What I actually need is to have the following filter revised so the <today> to <today> would be something like <lastmonthbegin> to <lastmonthend>. But I don't know if RMS has something like that?
Begin Filter
FieldName = "[Transaction].Time"
FilterOp = reportfilteropBetween
FilterLoLim = "<Today>"
FilterHilim = "<Today>"
End Filter
FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.Transacti
INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber
LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID
LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountR
LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChange
LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnRea
LEFT JOIN Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID
LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID
LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID
LEFT JOIN QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityD
What I actually need is to have the following filter revised so the <today> to <today> would be something like <lastmonthbegin> to <lastmonthend>. But I don't know if RMS has something like that?
Begin Filter
FieldName = "[Transaction].Time"
FilterOp = reportfilteropBetween
FilterLoLim = "<Today>"
FilterHilim = "<Today>"
End Filter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well thanks for clarifying. I kinda believed that there is no option for but I wasn't sure. Thanks for posting the workaround.