Crystal Xi Compare fields to produce data

[GStenning]

Please help,
We have a datasource which holds Membership No and ProductCode. Each membership number can have 1 or multiple product codes.

What i need to do in crystal is to show the follwing:
1. All members who have both product codes.
2. All members who have a Product code 1 and not product code 2
2. All members who have a product code 2 and not product code 1
Once i have this info i can then start generating charts to show this compartive data.

Below is the data extract from source
MembNo Product
1 ST0708
1 ST0607
2 ST0708
2 ST0607
3 ST0708
3 ST0607
4 ST0708
4 ST0607
5 ST0607
6 ST0607
7 ST0607
8 ST0708
9 ST0708
10 ST0708
11 ST0607
12 ST0607

Many thanks

 

[lbass]

I think you could use a command like:

select table.`MembNo`, count(table.`Product`) as ProdCnt
from table
where table.`Product` in ('ST0607','ST0708')
Group by table.`MembNo`

Then you could create a formula {@Grp}:

if {command.ProdCnt} = 2 then "Both" else
if {command.ProdCnt} = 1 then
(
if {command.Product} = "ST0607" then
"ST0607 Only" else
if {command.Product} = "ST0708" then
"ST0607 Only"
)

Then you can group on this formula or use it in charts.

-LB

 

[GStenning]

Thanks, this has worked very well.
However....there always is a however!
When saved and reopened i get the follwing message..
"The saved data will discard since one or more formulas failed to compile". Then when i click ok and view the formula it shows and unknown field name "Group #1 Order" and a formula :

if IsNull ({@{@grp}})
then 2
else 2

If i delete it and save, the report runs with no problem...go back into the report same error.

Im grouping the report via the @grp formula and also showing a distinct count of MembNo. This allows me to create the chart.

Help!!! Many thanks in advance!

 

[lbass]

Did you start your report from scratch when you went to the command? I wonder if you have the remnants of your former approach that are interfering. Maybe go to record sort and see what's listed (you should only see {@grp} I think). Have you removed all tables, leaving only the command? I would try copying the command into a new report, and then recreate the chart.

-LB