how not to count suppressed fields in a group 1

[hatemgamil]

hi all
lets say i have a report just like that:

customer_ID Customer_Name Order_Amount Order_Date
(#Group1 VipCustomer)
1 qwe 3 3/3/2010
2 dw 4 3/1/2010
3 sd 6 5/5/2009
(#Group1 NomralCustomer)
4 xyz r 3/3/2010
5 dee 9 3/1/2009

i have used suppression formula in the details section to hide the data where year(Order_Date)=2009 to show 2010 records only and it works so i have a report like that :

customer_ID Customer_Name Order_Amount Order_Date
(#Group1 VipCustomer)
1 qwe 3 3/3/2010
2 dw 4 3/1/2010

(#Group1 NomralCustomer)
4 xyz r 3/3/2010

then i want to get subtotal of customers for each customer group where year(Order_Date)=2010 ,but i cant do it,i have created a formula like that
if year(Order_Date)=2010
count(Customer_ID,Customer_Type) //customertype the grouping field
but it doesnt work correctly it gives me the total count of all customers in 2009 and 2010

for eg the subttotal for (#Group VipCustomer ) for year(Order_Date)=2010 is 2 but it gives me 3 which means it also count the suppressed record ,,can any one help me in this one urgent

thnx

 

[IanWaterman]

Use a running total instead.

In your evaluate condition use the reverse of your suppression formula.

Ian

 

[lbass]

When you use a conditional formula like this:

if year({table.date}) = 2010 then
count(field,group)

...you are saying: If the year of the current row = 2010 then show me the count of all of the records.

You can either use the running total approach, or you can try a conditional formua like this:

if year({table.date}) = 2010 then 1

Then insert a SUM, not a count, on this formula to get a count of records meeting your criterion. This will work unless you have duplicate records. Note that a count of a conditional formula returns the number of times the formula executes, NOT the number of times the condition is met.

-LB

 

[hatemgamil]

thnx Lb for ur post but what u said doesnt work ,,i went for the second approch and made a formula
if year({table.date}) = 2010 then 1
then i righted click on report and inserted an summary on formula and i choosed sum but i doesnt appear on run time??

 

[lbass]

I don't know what you mean by "it doesn't appear on run time".

-LB

 

[hatemgamil]

i dont see it on the report?although i did what u have said
1-created a formula then i added
if year({table.date}) = 2010 then 1
2-i right clicked on report and inserted an summary on formula
and i can see the summary when design
but when i run the report i cant c it

,i am using CR in vs2008
thnx

 

[lbass]

If you did not specify you wanted it summarized at the group level, it would have defaulted to the report footer. You can drag the result from the report footer into the group footer if you wish, or copy and paste it there. You must have the report footer suppressed if you can't see the summary at the end of the report.

-LB

 

[hatemgamil]

it works lb thank u soo much ,,
but i wana ask u about another thing
now i want to make a percentage to have report like that

customer_ID Customer_Name Order_Amount Order_Date %
(#Group1 VipCustomer)
1 qwe 3 3/3/2010
2 dw 4 3/1/2010
subtotal 2 (2/3)*100

(#Group1 NomralCustomer)
4 xyz 4 3/3/2010
subtotal 1 (1/3)*100

total 3

but i doesnt seems that i have something wrog ,,i get this total field(3) by creating a running field that count orders and in evaulate in added formula (Order_Date)=2010

but the instead of (2/3) it appears like (2/2) so i get 1

 

[lbass]

Don't use the running totals. Instead use a formula like the following. Let's assume that your conditional formula is called {@cnt2010} and that your group is on {table.customertype}. Then your percentage formula would look like this:

sum({@cnt2010},{table.customertype})%sum({@cnt2010})

-LB

 

[hatemgamil]

MR. lbass,,,,big thanks to u ,,u saved my day ,,thnx again u did a great help for me