Group by problem in Crystal report..

[sachindeva]

Hi,


i have two columns in my table (ACT)

activity cost

a 10
a 10
a 20
b 30
b 30


And here i will be displaying in crystal report group by 'a' database field with total amount


we should display looks like


activity Cost

a NA

b 30



So how do we display in this format in crystal report.


(Note : this is my requirement to display 'NA' for 'a' row since in my table (ACT) 3rd row value is different compare to first two rows. )


Thanks & regards
Ramesh
sapdeva@yahoo.com

 

[synapsevampire]

The requirement is that if each 3rd row for qa given group differs from the 2nd then you want to display NA?

Group by Activity and set up a counter using the 3 formula method:

GH:
whileprintingrecords;
numbervar Counter := 0;

Details:
whileprintingrecords;
numbervar Counter := Counter+1;

GF:
whileprintingrecords;
numbervar Counter;
If Counter = 3 then
(if previous({table.cost}) <> {table.cost} then
&quot;NA&quot;
else
{table.cost}

I suspect that what you really want is to display NA if any of the costs for a group are different, in which case you can right click the cost field and do a distinct count on cost, if it's greater than 1, then display NA, otherwise it's cost.

-k

 

[sachindeva]

Hi synapsevampire,

Thanks for your reply.

But in your answer

' if counter = 3 ' you specified static condition.. but it might be dynamic also.. we donno exactly that distict record number is 3. it might be any number.
suppose if you have another following 2 rows also available in future then,

activity cost

a 10
a 10
a 20
b 30
b 30
c 10
c 10
c 10
c 20

so report should be

activity Cost number amount

a NA 3 40

b $30 2 60

c N/A 4 50

In this case your code it won't work.

and your last suspection is also right.. i was trying to do same thing which you explained but it showing all N/A like

Activity cost total
a N/A 40
b N/A 60


syntax :

If DistinctCount {VW_CURRENT_PER_PAT_COST.COST} ) > 1 Then
&quot;N/A&quot;
Else
&quot;$&quot; + ToText({VW_CURRENT_PER_PAT_COST.COST},2,&quot;,&quot


is there any other suggestion will be appreciated.

Thanks
sachin

 

[lbass]

You need to group on {table.activity}. Then your formula would be:

If DistinctCount({VW_CURRENT_PER_PAT_COST.COST},{table.activity}) > 1 Then
&quot;N/A&quot;
Else
&quot;$&quot; + ToText({VW_CURRENT_PER_PAT_COST.COST},2,&quot;,&quot

Then suppress duplicates to get the desired display or drag the fields into the group header or footer.

-LB

 

[sachindeva]

Hi lbass,

WOW i solved this issue now..

Thanks for your great help..

Really now i am happy.. i solved my issue with your help..

I am appreciating your help..

Thanks once again..

- sachin