?
Solved

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

Posted on 2011-04-19
16
Medium Priority
?
407 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:wallis34
  • 9
  • 7
16 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35427023
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

by:wallis34
ID: 35427257
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

by:wallis34
ID: 35427286
there is already a group#1 in report, which is CUST_ORDER_LINE.PART_ID
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 101

Expert Comment

by:mlmcc
ID: 35427580
Create a formula and group on the formula

Left({CUST_ORDER_LINE.PartId},4)

mlmcc

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35427587
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

by:wallis34
ID: 35427678
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 101

Expert Comment

by:mlmcc
ID: 35428070
Can you upload the report?

mlmcc
0
 

Author Comment

by:wallis34
ID: 35428099
Sure will
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35428247
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

by:wallis34
ID: 35428285
Ver. 8.5 can't open the file u sent
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35428332
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
 

Author Comment

by:wallis34
ID: 35432519
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

by:wallis34
ID: 35432533
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

by:wallis34
ID: 35432611
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 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35433289
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question