Crystal & SQL Command

[zoonkai]

I'm trying to build a quite complex report & I'm new to Crystal & SQL. Here are the Key Specs.

mcPastPrdHdr contains CoNum, Date, & Parent Item.
icItemPr contains prices
mcPastPrdLn contains components (prices for them also)
icItem contains item major category

I need to get all of the items from mcPastPrdLn that are in the major category "SG"

I then need to sum up their prices & add it to the price of the Parent Item

Then on the Main Report, I'm going to use that price, date, Parent Item, DocNum, & some other info from the mcPastPrdHdr file, grouped by Whse & then by Production Line.

Here's the angle I'm going with right now. By adding the tables and linking them together in one report, I'm copying the SQL code it generates & trying to write a 'command' to have one "table" with all my summarized prices. (One line per document #). Then I'll add the mcPastPrdHdr table & get the remaining information & group it together. The problem I'm having is the sum of the listpr field.

Please note the Line I added that makes the query quit working.
*** "Sum ("QtyPer",”DocNum”) AS “TestField”" ***

Currently I'm also specifying the docnum, but that is just to make creating the report easier. Normally only CoNum, Date, & possible Whse will be the prompted fields.

Thanks for any help you can give me
Don
dond@bigtextrailers.com



SELECT
"icItem"."MajorCat",
"icItemPr"."ListPr",
"mcPastPrdLn"."DocNum",
"mcPastPrdLn"."ItemNum",
"mcPastPrdLn"."QtyPer",
"mcPastPrdLn"."CoNum",
"mcPastPrdHdr"."Whse",
"mcPastPrdHdr"."ActualDate",
"mcPastPrdHdr"."EnterDate",
"icItemPr"."Whse",

Sum ("QtyPer",”DocNum”) AS “TestField”

FROM {oj (("PUB"."mcPastPrdLn" "mcPastPrdLn" LEFT OUTER JOIN "PUB"."icItemPr" "icItemPr" ON ("mcPastPrdLn"."CoNum"="icItemPr"."CoNum&quot

AND

("mcPastPrdLn"."ItemNum"="icItemPr"."ItemNum&quot) LEFT OUTER JOIN "PUB"."icItem" "icItem"

ON

("mcPastPrdLn"."ItemNum"="icItem"."ItemNum&quot

AND

("mcPastPrdLn"."CoNum"="icItem"."CoNum&quot) LEFT OUTER JOIN "PUB"."mcPastPrdHdr" "mcPastPrdHdr"

ON

("mcPastPrdLn"."DocNum"="mcPastPrdHdr"."DocNum&quot

AND

("mcPastPrdLn"."CoNum"="mcPastPrdHdr"."CoNum&quot}

WHERE
"mcPastPrdHdr"."ActualDate"={d '2003-11-03'} AND
"mcPastPrdHdr"."Whse"='001' AND
"mcPastPrdLn"."CoNum"='001' AND
"mcPastPrdLn"."DocNum"='00009250' AND
"icItem"."MajorCat"='SG' AND
"icItemPr"."Whse"='001'

 

[dgillz]

Are you using crystal reports or crystal SQL designer?

If you are using crystal reports, there is no need to write SQL statements. Just place the objects on the report where you want to see them. Then right click the field you want to sum and select insert, grand total.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[zoonkai]

Crystal Reports Version 9

I need all the information derived from that code to sum up the numeric value of one field. then added it to a field that will show on the main report. All of this information is being calculated but never shown. For each record in the Header file, there are multiple records in the Line Item file. I want the Header records to be my detail & the Line Item records will have math calculations to get their numbers & add them to one field of the Header records.
I figured out how to hide detail records, but I need detail records. Just not those. I'd take a 3 page report & turn it into a 500 page report if I showed the Line Item File Records, and I'm not sure if I'd get what I need by doing it that way. I hope I explained that right.

Thanks,
Don