4 replies to this topic

[malaize]

I am trying to create a report in R & R Report Writer version 9.0 that allows the user to enter an item code upon startup. After entering the item code R&R will calculate and display the total sales of that item for each year.


The output would look like this.

Item # 2004 2003 2005
A4403 $2930 $2811 $2933

I have stock.dbf as my master table. I am guessing that I need to create some sort of relation between stock.dbf and cms.dbf, and then between cms.dbf and items.dbf. Then I would need some sort of calculation that would grab each years sales for that item and put the total under each year title above.

Does this basic layout for this report sound about right to anyone?

thanks, malaize

[Videoflicks2004]

The Uncompressed version of the same file above. Try this one if you have trouble downloading the othe.

I am trying to create a report in R & R Report Writer version 9.0 that allows the user to enter an item code upon startup.  After entering the item code R&R will calculate and display the total sales of that item for each year.
The output would look like this.

Item #            2004      2003      2005
A4403          $2930      $2811      $2933

I have stock.dbf as my master table.  I am guessing that I need to create some sort of relation between stock.dbf and cms.dbf, and then between cms.dbf and items.dbf.  Then I would need some sort of calculation that would grab each years sales for that item and put the total under each year title above.

Does this basic layout for this report sound about right to anyone?

thanks, malaize

Hi Malaize,

Sorry about the delay in getting this to you. You already know what my world has been like at work

I 'd also like to thank you for your help the other day. You're a true gentleman!

The report I created is fast if you enter the query statement ITEM = and your stock number (whatever you labeled your stock). And also have the START and FINISH SCOPE (under DATABASE)= your stock number.

I tried to post the .rrw file on this forum but momfaq does not allow this type of attachment. Too bad. Here is a link to it from my server:


Download this report file.

We can add / change anything you like to the report but I think this is a good start.

Please note that this report looks at the items.item_sdate of each line item and uses this date to arrive at the annual subtotals.

Cheers,

David

[malaize]

Just wanted to update anyone who may be interested in this report of David's but hasn't dowloaded it yet.

IT WORKS GREAT!

It is exactly what I was looking for and then some.

Thank you very much for the help David.


malaize

[jwurunner]

Dave,

I am trying a similar report but for quantity comparision not dollars..I can't figure out which quantity column to use in the items table. The following columns are available:

QUANTO
QUANTF
QUANTB
QUANTP
QUANTS

Most have info..but non add up to the same totals in mom when you run the profit by product by year report...

Thanks

[Videoflicks2004]

Dave,

I am trying a similar report but for quantity comparision not dollars..I can't figure out which quantity column to use in the items table. The following columns are available:

QUANTO
QUANTF
QUANTB
QUANTP
QUANTS

Most have info..but non add up to the same totals in mom when you run the profit by product by year report...

Thanks

Hi Jwrunner,

To the best of my understanding this is how the following columns in ITEMS.DBF break down:

QUANTO = Quantity of items ordered for the given stock item.
QUANTF = The number of units from a line that are allocated.
QUANTB = The number of units from a line that are not in stock.
QUANTP = The number of units from a line that are PI or picked.
QUANTS = The number of units shipped on a line item.

Please note that you can also cross reference your selection with the ITEMS.ITEM_STATE field to verify that you are dealing with shipped items.

ITEM_STATE = "PS" or "SH" (Pending shipment / Shipped)

If you are using the report I provided as a basis for this new report it is important to note that you must BATCH process shipping in mom first. My report uses the ITEMS.Item_sdate to figure out when the line item was shipped.

Don't hesitate to send me the .rrw file you have created and I will take a look at your calculations. You may have some of your calculations resetting incorrectly as well.

Regards,

David