|
Posted by Gerd on September 13, 2007, 2:00 pm
Please log in for more thread options 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
>>
>>
>> > 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