|
Posted by Craig on September 21, 2007, 6:03 pm
Please log in for more thread options I agree. The report would be useful to me also. Please attach to newsgroup
Akber.
Craig
> Please post final report - so we can all learn.
>
> Thanks
> Danny
>> hi I haven't receivend any email. send again.
>>
>> "Gerd" wrote:
>>
>>> 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
>>>
>>> > HI Gred, its hard to do it from newsgroup send me email at alwani89
>>> > hotmail.com and also don't forget to rate once your problem solved by
>>> > us.
>>> > because I wanted to become this year the MVP and only your guys rating
>>> > will
>>> > make it.
>>> >
>>> > "Gerd" wrote:
>>> >
>>> >> 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 = "<YearStart>"
>>> >> FilterHilim = "<Today>"
>>> >> FilterConnector = reportfilterbooleanconOR
>>> >> End Filter
>>> >>
>>> >> Begin Filter
>>> >> FieldName = "[Transaction].Time"
>>> >> FilterOp = reportfilteropBetween
>>> >> FilterLoLim = "NULL"
>>> >> FilterHilim = "NULL"
>>> >> End Filter
>>> >>
>>> >>
>>> >> message
>>> >> > 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 things works please rate.
>>> >> >
>>> >> > "Gerd" wrote:
>>> >> >
>>> >> >> 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
>>> >> >>
>>> >>
>>>
>
>
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap