Solved

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

Posted on 2011-04-19
383 Views
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
0
Question by:wallis34

LVL 100

Expert Comment

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

Author Comment

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

Author Comment

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

LVL 100

Expert Comment

Create a formula and group on the formula

Left({CUST_ORDER_LINE.PartId},4)

mlmcc

0

LVL 100

Expert Comment

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

Author Comment

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

LVL 100

Expert Comment

mlmcc
0

Author Comment

Sure will
0

Author Comment

0

LVL 100

Expert Comment

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

Author Comment

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

LVL 100

Expert Comment

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

Author Comment

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

Author Comment

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

Author Comment

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

LVL 100

Accepted Solution

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

## Featured Post

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I Â will demonstrate that undo for DMLâ€™s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasoâ€¦
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.