Jump to content


Photo

(OT) Report Writer Report question


  • Please log in to reply
4 replies to this topic

#1 malaize

malaize

    Advanced Member

  • Members
  • PipPipPip
  • 80 posts
  • Location:Galesburg, Illinois
  • Interests:Computers, weight lifting, excercise, sports, Lord of the Rings, Star Wars, toys, video games, eating

Posted 21 December 2004 - 10:26 AM

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

#2 Videoflicks2004

Videoflicks2004

    GLOBAL MODERATOR

  • Moderators
  • PipPipPip
  • 241 posts
  • Gender:Male
  • Location:Canada
  • Interests:~ DVD ~ BLUE RAY ~ HD DVD ~ HOLOGRAPHIC DVD ~ Quantum Computing.

Posted 30 December 2004 - 10:51 AM

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

View Post


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
David Narciso
Ecommerce Operations Manager CVS Inc

dnarciso@cvsinc.ca

mobile: 4168238175

DISCLAIMER: Always backup your MOMWIN folder before attempting to perform any updates, testing, etc. Once you blast the data away it's gone forever.
My advice is based on my 8 years of experience with MOM and Dydacomp. If you are not comfortable with data manipulation then hire someone who is.

#3 malaize

malaize

    Advanced Member

  • Members
  • PipPipPip
  • 80 posts
  • Location:Galesburg, Illinois
  • Interests:Computers, weight lifting, excercise, sports, Lord of the Rings, Star Wars, toys, video games, eating

Posted 10 January 2005 - 02:04 PM

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

#4 jwurunner

jwurunner

    Advanced Member

  • Members
  • PipPipPip
  • 31 posts
  • Location:MA,USA

Posted 14 January 2005 - 08:54 PM

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

#5 Videoflicks2004

Videoflicks2004

    GLOBAL MODERATOR

  • Moderators
  • PipPipPip
  • 241 posts
  • Gender:Male
  • Location:Canada
  • Interests:~ DVD ~ BLUE RAY ~ HD DVD ~ HOLOGRAPHIC DVD ~ Quantum Computing.

Posted 15 January 2005 - 09:28 AM

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

View Post


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
David Narciso
Ecommerce Operations Manager CVS Inc

dnarciso@cvsinc.ca

mobile: 4168238175

DISCLAIMER: Always backup your MOMWIN folder before attempting to perform any updates, testing, etc. Once you blast the data away it's gone forever.
My advice is based on my 8 years of experience with MOM and Dydacomp. If you are not comfortable with data manipulation then hire someone who is.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users