1. Home
  2. Point-Of-Sale Software
  3. Top selling items report including non-selling items

Top selling items report including non-selling items

In RMS Store Operations 2.0 I copied the top selling items report and changed the table sequence to use the item table as my first table with a left join on the transactionentry table (see attached report). The idea was that if there is no transactionentry record the item record would still show on the report. But this is not the case and i am trying to figure out where my report is wrong.

I don't use a date filter (ultimately I want to), assuming that if no transactionentry record exists the transaction date would be NULL.

Running the report only shows items with a transactionentry records. I verified that with various SQL statements and some items that are active in the items table are not on the report.

Further I would like to group the items by using the first 7 characters of the item.itemlookup code. I think i figured that out by using Formula "substring(Item.ItemLookupCode,1,7)". It seems to work but I would want to be sure that it also works with the non-selling items.

And last, because I group by the first 7 character of the itemlookup code I would want to trim the last 4 characters of the item.description field. Because the description can have different number of characters I can't use a fixed length. I tried different formulas, like Formula "substring(Item.ItemLookupCode,1,datalength((item.description) -4))" and different variations of it but I always end up getting syntax errors.

I would appreciate if somebody could check my report and point me into the right directions.

Thanks

Reply to
Gerd
Loading thread data ...

Hi Gerd, I am not sure but I already last 2 days given the solution what you said regarding the trasnaction about bringing the items on sale:

I know that you use the LEFT function and still only data for the transaction is coming use the below cluase for Transaction and TransactionEntry tables also Not inner but LEFT join too for these tables: LEFT JOIN TransactionEntry WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber

Regarding grouping it is okay you can use the LEFT function also and make sure to group by substring or LEFT function same way as you are presenting the data.

Regarding the Description you have given wrong formula, here is correct formula: substring(Item.Description,1,datalength(item.description) -4)

you specified substring(item.itemlookupcode and then in the data length you specify the description which I belive you did by mistake.

If th> In RMS Store Operations 2.0 I copied the top selling items report and

Reply to
Akber Alwani

Thanks for your suggestion, Akber. Without date selection I now see all my products, including the ones that never sold. However, I still have 3 issues:

1) the description field does not "chop off" the last 4 characters, the complete description is still displayed. i use the following formula Formula "MAX(substring(Item.Description,1,datalength(item.Description) -4))" 2) the grouping by the first 7 characters of item.itemlookupcode works if i have transactionentry record. for all not-sold items the report shows me the item number (first 7 character) for each item in the item table i tried it with having the item.itemlookupcode in the Group By field and with ItemLookupCode, which is the field name I assigned to the grouping of the itemlookupcode. 3) i still would like to use some date selection if possible. if i enter a date range i only get records with a transactionentry record (makes sense) but for all the ones without a transaction record is there a way to set the date selection to NULL or 0, or whatever the SQL statement would need? I thought about a filter like Begin Filter FieldName = "Department.Name" FilterOp = reportfilteropEqual FilterLoLim = "Loose Tea" FilterHilim = "Loose Tea" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim = "" FilterHilim = "" FilterConnector = reportfilterbooleanconOR End Filter

Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim = "NULL" FilterHilim = "NULL" End Filter

Reply to
Gerd

Reply to
Akber Alwani

Hi Akber,

I have sent you a private e-mail with the latest version of the report and some issues I still have with the report.

Could you confirm when you have received my e-mail?

Thanks

Reply to
Gerd

Reply to
Akber Alwani

Please post final report - so we can all learn.

Reply to
Danny

I agree. The report would be useful to me also. Please attach to newsgroup Akber. Craig

Reply to
Craig

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.