Share
Reportgood Morning
while generate this report from Hq for month or 2 month ago
for all stores - total money be incorrect
*This post is locked for comments
You’re offline. This is a read only version of the page.
875
good Morning
while generate this report from Hq for month or 2 month ago
for all stores - total money be incorrect
*This post is locked for comments
Sorry I can not understand Archelle
I honestly can't do it here on this conversation.
pm me and lets see what i can do :D
I use version 2.0.2000
Is this problem occurs for one before?
same problem - Error total sales
could you send the default report
think i found your problem: oh, well
revise the hq report:
Change the tables queried properties into this:
FROM TransactionEntry
INNER JOIN [Transaction] WITH(NOLOCK)
ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID
INNER JOIN Batch WITH(NOLOCK)
ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID = Batch.StoreID
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 Store ON [Transaction].StoreID = Store.ID
Like what i said before, the report you send me are different to rms hq default detailed reports.
Thank you for your effort with me archelle16 :D
------------------------------------------------------------------
total sales are the same if i generate the code from hq_sql and store_sql
and total sales is correct if i generate the code from rms_store
------------------------------
the problem is appear when i generate the report from rms_hq for all stores
-------------------------------
No
i Have not tried to update report sql queries
-----------------------------------
I wanna ask you few questions so that i can come up with the solution.
1. Is hq sales greater than store sales or vice versa?
2. Have you tried to update report sql queries? If yes, what for?
3. Have you tried to execute the sql by omitting the rest of the tables for checking?
Example:
Execute the ff query in hq:
Select SUM(TransactionEntry.Quantity * TransactionEntry.Price) as TotalSales
from [Transaction]
left join TransactionEntry on TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID
inner join Batch on Batch.BatchNumber = [Transaction].BatchNumber AND Batch.StoreID = [Transaction].StoreID
left join Store on Store.ID = [Transaction].StoreID
WHERE [Transaction].[Time] >= '2015-01-01 00:00:00' AND [Transaction].[Time] <= '2015-01-31 23:59:59' AND Store.StoreCode = 'StoreCode here'
Compare the result in store,
if store and hq sales are not tally and hq sales is greater than store sales, check your batches. It might doubled upon sync. However,
If hq sales is less than store sales, check your batches again, you might lost a single/multiple batch.
but when we run this report from sql server (the same criteria for details report)
the result to be correct
----------------------------------
select sum(TransactionEntry.Quantity *TransactionEntry.Price )
FROM TransactionEntry
INNER JOIN [Transaction] WITH(NOLOCK)
ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID
INNER JOIN Batch WITH(NOLOCK)
ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID = Batch.StoreID
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 Store ON [Transaction].StoreID = Store.ID
LEFT JOIN cashier ON [Transaction].cashierID = cashier.ID and [Transaction].storeID=cashier.storeid
WHERE ([Transaction].Time BETWEEN CONVERT (DATETIME, '2015-01-01 00:00:00', 102)
AND CONVERT (DATETIME, '2015-01-31 23:59:59', 102))
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
