cancel
Showing results for 
Search instead for 
Did you mean: 

Problems with groups

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Because I want get ranges only groups without LL.

I try to sum the range of values (20-0, 30-20 or 75-50). And for this I use intermediate sums.

>>Could you post an example of that as well please?

Sorry, I don't understand what you mean.

abhilash_kumar
Active Contributor
0 Kudos

How did you come to the answer as '20'?

Could you post a data sample with expected results please?

-Abhilash

former_member205840
Active Contributor
0 Kudos

HI Olesya,

Go in Report--Selections--Record and give below formula :

{sect_type_code} startswith 'LL'


or


left(sect_type_code,2)  = 'LL'


-Sastry

Former Member
0 Kudos

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".

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

YES, It's work!

Thanks, Abhilash.

Former Member
0 Kudos

I don't use SQL, because I don't have connect to database.

I use scheme in xsd.

Answers (0)