# 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
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
###### Who is Participating?

Commented:
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

Commented:
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

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

there is already a group#1 in report, which is CUST_ORDER_LINE.PART_ID
0

Commented:
Create a formula and group on the formula

Left({CUST_ORDER_LINE.PartId},4)

mlmcc

0

Commented:
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

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

Commented:

mlmcc
0

Sure will
0

0

Commented:
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

Ver. 8.5 can't open the file u sent
0

Commented:
The one you sent didn't have the formula I suggested.

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

mlmcc
0

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

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