I need to create a report that will allow me to choose a Customer, Enter Posting Dates, and output a report that shows every item they every purchased. There is a catch though, if they purchased for example:
Customer: C001235
Posting Dates: 12/28/2008..01/01/2009
12/28/2008 ITEM123
12/29/2008 ITEM123
01/01/2009 ITEM456
When the report outputs I only want 1 instance for each item purchased regardless of the date like this:
Unique Items Report
Customer: C001235
Posting Dates: 12/28/2008..01/01/2009
ITEM123
ITEM456
I assume I might use one of the "Lines" tables like "Posted Sales Invoice Lines" or such but I would probably need to use a temporary table at some point which I'm not very good at creating reports based on temporary tables so I'm a bit stuck here.
Can anyone show me some sample report code to get me started?
Half-empy or half-full how do you view your database?
Thanks.
0
Answers
First Data Item: Customer
Second Data Item: Item Ledger Entry (Using a key that starts with Item No. and contains Customer No.)
Then I would output my Item details in a Group footer causing section record for each Item (and you can utilize also the totals).
If you have a Key that starts with Customer, Item No, Posting Date then you'll need only One dataitem. (Item ledger entry is an example. You could use also sales invoice line, return receipt line etc...)
You can do this also with many dataitems if you don't have a suitable key and you want to avoid creating one...
Thanks.
You have to forgive me but I'm a bit slower in the morning, it's 9:00am here in Pennsylvania and a big day in history.
I put together a report how you suggested but can't seem to get all the pieces right.
Here is my report so far:
Thanks.
Go to the Item Ledger Entry DataItem properties and add:
DataItemTableView -> SORTING(Source Type,Source No.,Item No.,Variant Code,Posting Date) WHERE(Source Type=CONST(Customer))
DataItemLink-> Source No.=FIELD(No.)
GroupTotalFields -> Item No.
In your Item Ledger Entry Body add the Item No. with simple font.
In your Item Ledger Entry Group Footer add the Item No. with Bold font.
You'll see the body repeating the items for all entries and the footer showing once for each item.
then remove the body and you are ok....
It's the main picture....
Thanks for your help.
Thanks.
Keep in mind one thing though.
By using a certain key for sorting, you can create reports like this using a single dataitem. But try to use existing keys instead of creating new keys, for performance reasons... Many times I personally find it better to create a report that has several dataitems and might be a bit "heavy" than creating new keys in large tables like "Item ledger Entry"
Just some additional info...