on 09-03-2014 7:28 AM
Hello, I have some problems with groups. I work with CR2013.
I have next data. For example:
EQ1
iT 0 20
iR 0 20
iD 0 20
EQ2
iT 20 30
LL 20 30
LL 20 30
iE 20 30
EQ3
LL 50 75
iP 50 75
iO 50 75
iP 50 75
iD 50 75
I must get sum for all ranges (20-0, 30-20 or 75-50) then I don't have element LL. The answer: 20.
But I don't know how this to do this in Crystal reports.
On SQL:
SELECT a.assembly_id, a.md_out - a.md_in
FROM DM_BHA_RUN a
WHERE a.assembly_id NOT IN (
SELECT DISTINCT b.assembly_id
FROM CD_ASSEMBLY_COMP b
WHERE b.sect_type_code = 'LL')
order by a.assembly_id
Hi Olesya,
Are you trying to get rid of records in a group that have type_code = 'LL'?
What field are you trying to sum? Could you post an example of that as well please?
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, Sastry.
If I use same formula in Report--Selections--Record or Report--Selections--Groups, I get the next resulte:
EQ1
iT 0 20
iR 0 20
iD 0 20
EQ2
iT 20 30
iE 20 30
EQ3
iP 50 75
iO 50 75
iP 50 75
iD 50 75
And sum of intermediate sums = 55, because i exclude only records with "LL".
I want exclude whole group with "LL".
Try this:
1) Create a formula called (@LL) with this code:
If {sect_type_code} startswith 'LL' or left(sect_type_code,2) = 'LL' then 1
2) Go to Report > Selection Formulas > Group and use this code:
Sum({@LL}, {Group_Field}) = 0
Replace {Group_Field} with the field you've used as the Group on the report.
-Abhilash
Because I exclude whole group with "LL".
Ok))) Sorry, I understend your question now))))
I have 2 tables:
TABLE 1 - CD_ASSEMBLY_COMP
assembly_id sect_type_code
001 iT
001 iR
001 iD
002 iT
002 LL
002 LL
002 iE
003 LL
003 iP
003 iO
003 iP
003 iD
And TABLE 2 - DM_BHA_RUN
assembly_id md_in md_out
001 0 20
002 20 30
003 50 75
And I want get sum all (md_out-md_in) where assembly_id don't have "LL".
May be this post more simply.
Why don't you use the SQL Query you posted above as the datasource for the report?
Go to the Database Expert > Select the Connection > Select Add Command > Paste the SQL query > Click OK.
Drag and drop the required fields and then just right-click the numeric field you wish to sum > Insert Summary > Choose Sum > Place the summary on the Group Footer.
-Abhilash
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.