Problem grouping by Canada, U.S. or Both

[mbeech]

I'm using CR v10. I would like to set up a parameter on a report that lets the user pick one of three values: CANADA, U.S., or BOTH. I need help writing the formula for the report grouping. My formula, below, works for US and Canada, but gives me nothing for BOTH (since I have already used those records on the report).

If {ST005.COUNTRY} = "CA" then "Canada"
else if {ST005.COUNTRY} = "US" then "US"
else if ({ST005.COUNTRY} = "CA" or {ST005.COUNTRY} = "US") then 'Both'

Any help would be appreciated.

 

[synapsevampire]

The logic is flawed, you'll never have a both.

Data doesn't duplicate because you write a formula, your BOTH clause simply repeats what you've already defined, so it will never show as each record is already processed by the previous IFs.

Perhaps you should instead state what you are trying to accomplish.

-k

 

[mbeech]

What I am trying to accomplish is: I now have three separate reports on our report browser; Sales Canada-Only, Sales US-Only, and All Sales (which is US & Canada). I would like to replace the three reports with just one. Would using a parameter, based on Country code, be a possible solution, or is there another method you could recommend?

 

[synapsevampire]

It depends on the format of the report output.

You can reuse the record set within cross-tabs, so you can set up multiple cross-tabs in the report header/footer, and do that sort of thing, which is summary info.

Otherwise you can save the report under another name, then insert it as a subreport in the report header/footer, and then change it's record selection to have the BOTH criteria only.

-k

 

[lbass]

I think you're mixing up the record selection and the grouping. For the record selection, you can just use a multiple value parameter {?country}, with a record selection formula of:

{table.country} = {?country}

Users can then choose one of the two countries, or choose them both.

You can then decide whether you want to insert a group on country or not, depending upon whether you want to distinguish the countries when both are chosen.

-LB

 

[Madawc]

Create a formula field, a boolian to make the most suitable test:

if @param = "BOTH" and {table.country} in ["CA", "US"]
or
@param = {table.country}

Call this @TestParam and put it as an extra in the detail line, to be sure it is working, True or False as desired. Then say @TestParam in record selection and it should select according to the parameter.

Incidentally, does your data include other countries besides US and Canada? If not, you can simplify the test;

if @param = "BOTH" or
@param = {table.country}

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[mbeech]

Thanks for the suggestions! I did accomplish this task by setting up a multi-value parameter by country (per lbass) and then picking Allow Multiple Values in the parameter field setup. I now have just one report where users can pick US, Canada, or both.

 

[synapsevampire]

Sorry, I misunderstood, I thought that you wanted all 3 groupings in the report.

-k