Link to home
Start Free TrialLog in
Avatar of timbraun
timbraunFlag for Belize

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?
Avatar of carmodyk
carmodyk
Flag of United States of America image

Please send me the TablesQueried value from the report, I might be able to help
Avatar of timbraun

ASKER

Thanks for responding. The tables queried are as follows.

FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
         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.DiscountReasonCodeID = ReasonCodeDiscount.ID
         LEFT JOIN   ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID
         LEFT JOIN   ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = 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.QuantityDiscountID = 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
ASKER CERTIFIED SOLUTION
Avatar of carmodyk
carmodyk
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well thanks for clarifying. I kinda believed that there is no option for  but I wasn't sure. Thanks for posting the workaround.