suppress using formula 1

[lkh5650]

Hi

I am using formula in crystal reports to evaluate the group within a group

for instance
within group A, there can be group a,b,c
and i wanna evaluate/suppress
if there is group a, discard group b & c
if there is group b, discard group c
if there is only group c, then display group c

right now the group is sorted as a,b,c
can i do that?

i also need it so that it doesn't display the rows (data) under that group that i dont need..

I used select expert in group, and suppressed it but it would only suppress the group title (?) not the records with it..
just like this

previous({CASES.ID})= {CASES.ID} and {@SourceHierarchy} > {@PreviousHiarchy} ;

 

[lbass]

Create three conditional formulas like this:

//{@a}:
if {table.groupfield#2} = "a" then 1

//{@b}:
if {table.groupfield#2} = "b" then 1

//{@c}:
if {table.groupfield#2} = "c" then 1

Then go to report->selection formula->GROUP and enter:

if {table.groupfield#1} = "A" then
(
if sum({@a},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "a" else
if sum({@b},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "b" else
if sum({@c},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "c"
) else
true

This assumes that {table.groupfield#1} has other values like "B","C", etc., where you do not want any group selection to occur. I'm not sure if that is your intent.

If the above does not give you the results you hoped for, please provide a sample where you reference actual fields, not generic "groups". If you use any formulas, you should ALWAYS provide their content.

-LB

 

[lkh5650]

Hi
Thank you for the reply.

I will give an example of codes

currently there is this field called
{SOURCE_TYPE}
it can take values like, C,D,K,L,M,O,R,S,T and null

and each case can have multiple source_type
I want them to have hierarchy, so i introduced a formula called sourcehierarchy

if isnull({SOURCE_TYPE}) then 9
else if {SOURCE_TYPE} = 'C' then
1
else if {SOURCE_TYPE} = 'R' then
2
else if {SOURCE_TYPE} = 'L' then
3
else if {SOURCE_TYPE} = 'S' then
4
else if {SOURCE_TYPE} = 'D' then
5
else if {SOURCE_TYPE} = 'K' then
6
else if {SOURCE_TYPE} = 'M' then
7
else if {SOURCE_TYPE} = 'O' then
8
else if {SOURCE_TYPE} = 'T' then
9
;
I sorted groups in ascending order
so if it has C, M, O
group will display in order of C, M, O (according to hierarchy)

I want to suppress the group if the previous group had higher hierarchy

So I did this,
created formual field called
previoussource
previous({SOURCE_TYPE})
I also created previoushierarchy formula fields
similar to that of sourcehierarchy but replacing {SOURCE_TYPE} with {@previoussource}

then I dont know what to put in

i had this this formula field called
deletesource
if {CASEID} = previous({CASEID}) and {@previoushierarchy} < {@sourcehierarchy} then
'Y'
else
'N'
;

then
if i go to report->selection formula->GROUP
what should I enter?
{@deletesource) = 'Y'?

when I do
it gives me an error saying
the formula cannot be used because it must be evaluated later..

 

[lbass]

Did you try my suggestion? If so, what happened?

-LB

 

[lkh5650]

I don't understand what your code is trying to do..
what I meant to say in my first post is

I have it so that data is grouped by caseid, then it is grouped by source

within the same caseid, if the source c (highest in hierarchy) is there, then none of the other source should be shown..except that source/group


if {table.groupfield#1} = "A" then
(
if sum({@a},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "a" else
if sum({@b},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "b" else
if sum({@c},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "c"
) else
true

table.groupfield#1 would mean case id, which doesn't need to be used in if statement... in my case since it doesnt matter what the value is.. ??

I can't see how i can use ur code in my report at all...

 

[lkh5650]

OKay So i did the help on field sum
i understand what your code is trying to do

since my field for group field #2 can take up to 10 values
should i then create conditional formula
a,b,c,d,e,f,g,h,i,j.... to accommodate all?

 

[lkh5650]

sorry actually
if sum({@a},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "a" else
if sum({@b},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "b" else
if sum({@c},{table.goupfield#1}) <> 0 then
{table.groupfield#1} = "c"
)
in here

shouldn't it be
if sum({@a},{table.goupfield#1}) <> 0 then
{table.groupfield#2} = "a" else
if sum({@b},{table.goupfield#1}) <> 0 then
{table.groupfield#2} = "b" else
if sum({@c},{table.goupfield#1}) <> 0 then
{table.groupfield#2} = "c"
)

 

[lkh5650]

hi

I have it as this

if sum({@a1},{AE_CASES.CASE_ID}) <> 0 then
{AE_CASES.CASE_ID} = "C" else if
sum({@a2},{AE_CASES.CASE_ID}) <> 0 then
{AE_CASES.CASE_ID} = "R" else if
sum({@a3},{AE_CASES.CASE_ID}) <> 0 then
{AE_CASES.CASE_ID} = "L" else if
sum({@a4},{AE_CASES.CASE_ID}) <> 0 then
{AE_CASES.CASE_ID} = "S" ;

in the report selection formula-> group

and

as formula fields
//{@a1}:
if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'C' then
1;
//{@a2}:
if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'R' then
1;
//{@a3}:
if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'L' then
1;
//{@a4}:
if isnull({AE_REPORT_SOURCES.RPT_SOURCE_TYPE}) then 1
else if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'S' then
1
else if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'D' then
1
else if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'K' then
1
else if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'M' then
1
else if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'O' then
1
else if {AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = 'T' then
1
;

and it doesn't work
my report would now not display anything... empty report

 

[lbass]

You should have your first group on CaseID and your second group on Source_type (or don't have a second group), and your group selection formula should be:

if sum({@a1},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "C" else if
sum({@a2},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "R" else if
sum({@a3},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "L" else if
sum({@a4},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "S"

I tested this and it worked. I'm assuming from your formula for A4 that you want any case IDs that only have something other than C,R,L to show only S records.

-LB

 

[lkh5650]

Thanks it works
However for last line
sum({@a4},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "S"
i was hoping I could display all of them not only "S"
so all of them except C,R,L
(so S,D,K,...., inclduing null)

How would I do that??
or should I just introduce more conditional variables for each value, S,D,K, and just add more else if statements..

 

[lbass]

If you want all the rest to display if there are no C,R,L, then change the last part to;

sum({@a4},{AE_CASES.CASE_ID}) <> 0 then
true

-LB

 

[lkh5650]

Hello

THanks for the reply
but when i do that
now it only shows me the cases wehre a4 <> 0, every other records where a1~a3 <> 0 is not displayed.. ??

if sum({@a1},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "C" else if
sum({@a2},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "R" else if
sum({@a3},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "L" else if
sum({@a4},{AE_CASES.CASE_ID}) <> 0 then
true


This is what I have..

 

[lbass]

What is it you want to see if there are records based on {@a4} but no C, R, L records? Can you show a sample?

-LB

 

[lkh5650]

so for instance case ID abcd has source of C/S then I would like the report to only show records of C

if the case ID abcde has sources of S/""(NULL)/K then id like it to show only one (any one of) those 3. so record of S or "" or K which i dont care

 

[lkh5650]

something is really weird

It was acting weird so i changed the report - selection group w.e part to


if sum({@a1},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "C" else if
sum({@a2},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "R" else if
sum({@a3},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "L" else if
sum({@a4},{AE_CASES.CASE_ID}) <> 0 then
{AE_REPORT_SOURCES.RPT_SOURCE_TYPE} = "S"

the original one i had
and now it doesn't display any records
i didn't change anything for formula of a1~a4 either..

 

[lkh5650]

SO I tried save & close re-openning the crystal report file and refreshed and it now displays the records..

why does this happen??

and how do i still do this
//so for instance case ID abcd has source of C/S then I would like the report to only show records of C

if the case ID abcde has sources of S/""(NULL)/K then id like it to show only one (any one of) those 3. so record of S or "" or K which i dont care//

 

[lbass]

Okay, in retrospect, I think we made this more complicated than necessary. I think you should group on case ID and then insert a second group on your earlier formula (changing the first line):

if isnull({SOURCE_TYPE}) then 10
else if {SOURCE_TYPE} = 'C' then
1
else if {SOURCE_TYPE} = 'R' then
2
else if {SOURCE_TYPE} = 'L' then
3
else if {SOURCE_TYPE} = 'S' then
4
else if {SOURCE_TYPE} = 'D' then
5
else if {SOURCE_TYPE} = 'K' then
6
else if {SOURCE_TYPE} = 'M' then
7
else if {SOURCE_TYPE} = 'O' then
8
else if {SOURCE_TYPE} = 'T' then
9

Also place the same formula (that you have used for group#2) in the detail section and insert a minimum on it at the formula group level (Group#2). Then go to group sort->select the tab with your formula->topN=1 (not sure which order--ascending/descending)--try each.

-LB

 

[lkh5650]

Thank you for your reply LB
but i am not quite understanding what I am supposed to do
can you explain it to me alittle bit more in detail?

Currently

group1- Case id
group2 -source

 

[lbass]

Group on the formula I showed in my last post. This will become your group number 2. Place the same formula in the detail section and insert a maximum on it at the new group 2 level (this activates the group sort). Then go to report->group sort and select maximum of the formula and select topN=1. Try both sorts ascending and descending to see which one is appropriate.

-LB

 

[lkh5650]

Thanks for the reply

Group on the formula I showed in my last post. This will become your group number 2.
=> so create a formula field name lets say group_2
then paste the formula
//if isnull({SOURCE_TYPE}) then 10
.
.
.
else if {SOURCE_TYPE} = 'T' then
9// there?



Place the same formula in the detail section
=> in the detail section of the report, place the formula
{@group_2} ??



and insert a maximum on it at the new group 2 level (this activates the group sort).
=> ?????? insert summary of that field ({@group_2}) ? and set the summary location to group#2??

Then go to report->group sort and select maximum of the formula and select topN=1. Try both sorts ascending and descending to see which one is appropriate.

-LB


I hope i am understanding this right. I will try it now and let you know