In CR 8.5, create groups based on first 4 digits of Part_ID

I need to take an existing report and get sub-total of sales based on part type. i think best way is to break down the parts by aggregating the first 4 digits of the Part_ID. How do i do this?  TIA,,, Bill Here is the SQL of the report now:

SELECT
    RECEIVABLE_LINE."QTY", RECEIVABLE_LINE."AMOUNT",
    RECEIVABLE."INVOICE_ID", RECEIVABLE."INVOICE_DATE",
    CUST_ORDER_LINE."CUST_ORDER_ID", CUST_ORDER_LINE."PART_ID",
    PART."DESCRIPTION", PART."UNIT_PRICE"
FROM
    "SYSADM"."RECEIVABLE_LINE" RECEIVABLE_LINE,
    "SYSADM"."RECEIVABLE" RECEIVABLE,
    "SYSADM"."CUST_ORDER_LINE" CUST_ORDER_LINE,
    "SYSADM"."PART" PART
WHERE
    RECEIVABLE_LINE."INVOICE_ID" = RECEIVABLE."INVOICE_ID" AND
    RECEIVABLE_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND
    RECEIVABLE_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO" AND
    CUST_ORDER_LINE."PART_ID" = PART."ID" AND
    RECEIVABLE_LINE."AMOUNT" <> 0 AND
    CUST_ORDER_LINE."PART_ID" NOT LIKE 'NULL' AND
    RECEIVABLE."INVOICE_DATE" >= {ts '2011-03-01 00:00:00.00'} AND
    RECEIVABLE."INVOICE_DATE" < {ts '2011-04-20 00:00:00.00'}
ORDER BY
    CUST_ORDER_LINE."PART_ID" ASC
wallis34sysadminAsked:
Who is Participating?
 
mlmccCommented:
You could change the grouping formula to

If {ProductCode} = "Metal Halide") then
    Left({CUST_ORDER_LINE.PartId},6)
Else
    Left({CUST_ORDER_LINE.PartId},4)

mlmcc

0
 
mlmccCommented:
Is the PartId a number or a string?

If it is a string just use

Left({PartId},4)

If it is a number then

Left(CStr({PartId}),4)

mlmcc
0
 
wallis34sysadminAuthor Commented:
Part_ID is a varchar2 in Oracle 8i, so a string in CR 8.5.  How would I then do this? As i want to group using the criteria above, not use it as a criteria for part_id's returned in the qry?
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
wallis34sysadminAuthor Commented:
there is already a group#1 in report, which is CUST_ORDER_LINE.PART_ID
0
 
mlmccCommented:
Create a formula and group on the formula

Left({CUST_ORDER_LINE.PartId},4)

mlmcc

0
 
mlmccCommented:
You can change the group
Right click the group headerin the left margin
Click CHANGE GROUP
CHoose the formula as the new group

mlmcc
0
 
wallis34sysadminAuthor Commented:
Just as you described, but it does not change the grouping. What we want to do is have every PART_ID that begins with 260- to all be together, then every part that begins with 238- to be together etc.
0
 
mlmccCommented:
Can you upload the report?

mlmcc
0
 
wallis34sysadminAuthor Commented:
Sure will
0
 
wallis34sysadminAuthor Commented:
0
 
mlmccCommented:
Try this one
If it doesn't work, save it with some data so I can see what the issue is.

mlmcc
top-selling-products-in-descendi.rpt
0
 
wallis34sysadminAuthor Commented:
Ver. 8.5 can't open the file u sent
0
 
mlmccCommented:
The one you sent didn't have the formula I suggested.

Can you try that
Add the formula
Change th egroup
Run the report
If it doesn;t work, save with some data and upload the report.

mlmcc
0
 
wallis34sysadminAuthor Commented:
OK sorry i sent the file after i changed it back to original group, just checking to see if there was a difference between groups. Attached is the report with data.
top-selling-products-in-descendi.rpt
0
 
wallis34sysadminAuthor Commented:
Now they are getting rally fancy wanting me to first break down into groups by product code and then to get sub-totals based on just looking at either 4 digits or in 2 product code groups to look at 6 digits, is this possible?
0
 
wallis34sysadminAuthor Commented:
Actually now that i look this over better, i think it is returning the data we need. Only change is that in the product code Metal Halide we would like to group on 6 digits?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.