ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> CR to Break out Material and Labor Cost by Class
http://www.istechforum.com/YaBB.pl?num=1222208653

Message started by vswhipple on 09/23/08 at 15:24:13

Title: CR to Break out Material and Labor Cost by Class
Post by vswhipple on 09/23/08 at 15:24:13

I'd like to be able to create a CR that separates out the material cost and labor cost on work orders by product class. First question.... is it doable? Second question... what files do I need to link up?

I'm a DBA Classic user.

Thanks for any insights!
Vykie Whipple
Brey-Krause Mfg. Co.

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by Kelloggs on 09/24/08 at 08:01:05

You need two tables: BKICMSTR and WORKORD

I dont use CR, but this query works on Pervasive Control Center:

SELECT
BKICMSTR.BKIC_PROD_CLASS,
Sum(WORKORD.MTWO_WIP_AMAT) AS AMAT,
Sum(WORKORD.MTWO_WIP_AOUTPR) AS AOUTPR,
Sum(WORKORD.MTWO_WIP_ALABOR) AS ALABOR
FROM WORKORD INNER JOIN BKICMSTR ON WORKORD.MTWO_WIP_CODE = BKICMSTR.BKIC_PROD_CODE
WHERE WORKORD.MTWO_WIP_STATUS = 'R'
GROUP BY BKICMSTR.BKIC_PROD_CLASS

:P

Kelloggs

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by vswhipple on 09/24/08 at 09:36:07

Thanks so much for the feedback! The query worked perfectly; however, after I ran it I realized I did a poor job of describing the criteria. I'm hoping to be able to break out material and labor costs on closed work orders by item class for the year 2006. I'd like to compare these totals with the current year. I believe the query you created was for open work orders, right?

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by Kelloggs on 09/24/08 at 09:45:42

SELECT
BKICMSTR.BKIC_PROD_CLASS,
Sum(WORKORD.MTWO_WIP_AMAT) AS AMAT,
Sum(WORKORD.MTWO_WIP_AOUTPR) AS AOUTPR,
Sum(WORKORD.MTWO_WIP_ALABOR) AS ALABOR
FROM WORKORD INNER JOIN BKICMSTR ON WORKORD.MTWO_WIP_CODE = BKICMSTR.BKIC_PROD_CODE
WHERE WORKORD.MTWO_WIP_STATUS = 'C'
AND WORKORD.MTWO_WIP_AFIN >= '2006-01-01' AND WORKORD.MTWO_WIP_AFIN <= '2006-12-31'
GROUP BY BKICMSTR.BKIC_PROD_CLASS

Note:
I am using the Actual Finish Date
and if you have archived WO you need to run another query using the WORKHORD table

:P

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by vswhipple on 09/24/08 at 10:03:38

Perfect!! Thanks so much! :) Where can I learn to write queries like that??

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by GasGiant on 09/24/08 at 10:38:03

I recommend Learning SQL by Alan Beaulieu, from O'Reilly Media, Inc.

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by Kelloggs on 09/24/08 at 11:08:13

SQL for beginners

http://www.w3schools.com/sql/default.asp

and DBA file names, Field Names and field descriptions to be used for developing Crystal Reports and custom programs using DBA data

http://www.istechsupport.com/freedl.php

:D

Title: Re: CR to Break out Material and Labor Cost by Cla
Post by vswhipple on 09/24/08 at 11:12:23

Thanks for the great information!!