Group expert omitting fields - Crystal XI 1

[ReillyC]

Each application is usually assigned a product. One application can be assigned multiple products.


Note the application numbers are alphanumeric.

App # Country Product
USA2 US DVD
USA2 FR DVD
USA2 KR DVD
USA1 US HDTV
USA1 JP HDTV
USA1 UK HDTV
USA2 US HDTV
USA2 FR HDTV
USA2 KR HDTV
USA3 US HDTV
USA3 UK HDTV
USA3 SG HDTV
USA3 US TV
USA3 UK TV
USA3 SG TV
USA1 US VCR
USA1 JP VCR
USA1 UK VCR


Using the group expert, I created 2 groups DTV and VCR.

However the summary feature isn't counting TV for the 2nd group a created. I think it's omitting the VCR applications because it's already been counted in the first group DTV

Incorrect results

DTV = (HDTV, TV) = 12

VCR = (TV, DVD, VCR) = 6
As you can see the TV classification overlaps into both categories so I thought about using a running total since I can reset the group but it's not working.

I want each product to be counted no matter how many group it appears in.

Desired results
DTV = (HDTV, TV) = 12

VCR = (TV, DVD, VCR) = 9
Any suggestions?
Thanks
R

 

[lbass]

You should delete the group, as each record can appear in only one group. Then use running totals where you count the application, evaluate using a formula:

{table.product} in ["HDTV","TV"] // or ["TV","DVD","VCR"]

Reset never.

Place the running totals in the report footer.

-LB

 

[ReillyC]

BRILLIANT. It works great.

Thanks again lbass.

 

[ReillyC]

One other related question. I need to group the data by quarters. How would the formula change? or Should I create separate reports for each quarter?

Thanks
R

 

[lbass]

If you don't have row inflation (and it appears you don't), a better solution than my first suggestion would be to use conditional formulas like:

if {table.product} in ["HDTV","TV"] then 1

Create a second formula for VCRs. Place these in the detail section and right click on each and insert summaries (SUM, not count) at the group level (quarter) and at the report level.

Otherwise, to continue with the running total solution (which will run a bit slower), you would create separate running totals for the group level, where you reset on change of group (date), and for the report level (reset never).

-LB