Grouping formula problem

[arodri]

Hello,

I have a report (using Crystal XI) that has two groups, region (Group#1) and Sitename(Group #2). The data source is a stored procedure in SQL server.
Here is a sample of what the report looks like:

Southern Region Wash Lube
Site #1 2,122 325
Site #2 1,789 258
Site #3 3,689 0
Site #3a 0 256

My problem (not sure if this is even possible) is that I need to somehow combine Site #3 and Site #3a. Site #3 will never have any data for Lube (it will always be zero), and Site #3a will never have any data for Wash, but I need to combine the Wash number for Site#3 and the Lube number for Site #3a, and it needs to have the Site#3a label, so the report, ideally, will look like this:

Southern Region Wash Lube
Site #1 2,122 325
Site #2 1,789 258
Site #3a 3,689 256


I don't think this is possbile if Group#2 is sitename, so I thought I might need to create a forumla for Group#2 but I'm not even sure where to begin.

Any ideas would be great!

Thank you!

 

[MCuthill]

arodri,

At a glance, perhaps something like as follows in place of sitename for Group #2:

IF {sitename}="Site #3" THEN "Site #3a" ELSE {sitename}

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[arodri]

Hi MCuthill,

I tried that and I get the following error with the "else" part of the formula highlighted:

A boolean is required here

 

[MCuthill]

arodri,

can you please copy the contents of your formula field as well as advise as to the Data Type of the {sitename} field?

I just copied the above into Crystal Reports using "Branch Name" in my operations database and it worked a charm.

Thanks!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[arodri]

Sure! Here is the formula I'm trying to use for Group#2:

IF {SalesItemFlash.Sitename}="82 Site Wash"
THEN {SalesItemFlash.Sitename}="62 Site Lube"
ELSE {SalesItemFlash.Sitename}


Sitename is Varchar in the database

 

[MCuthill]

arodri,

Please change your formula as follows:

IF {SalesItemFlash.Sitename}="82 Site Wash" THEN "62 Site Lube" ELSE {SalesItemFlash.Sitename}

Effectively, what this formula does is simply hold a textual value to group on - not "assigning" a value to {sitename} itself.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[arodri]

Hello,

I made that change, and there were no errors in the formula, but I get the same results as when Group #2 was sitename. Both sitenames are still showing up, and they both have the zero value that I do not need.

Any other suggestions?

Thanks!

 

[MCuthill]

arodri,

Did you change Group #2 to Group on the new formula instead of the original field?

Just to confirm,

IF {SalesItemFlash.Sitename}="[b]82 Site Wash"[/b] THEN 
[b]"62 Site Lube"[/b] ELSE 
{SalesItemFlash.Sitename}

Not being familiar with your database, is one 82 and the other 62 for the same location? (perhaps a long-shot, but I would have expected both to be the same number if the same location but one for wash and the other for lube)

Cheers!



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[arodri]

Hello,

Yes - I no longer have sitename as Group#2. Now Group#2 is the formula you have above but that didn't work =(

For some reason, this is the only sitename in our database that is listed this way. They're essentially the same location, but different buildings. One does wash, the other lube. In the table in our database, all of the other sitenames (except this one..grr!) have one row assigned to them with all of the info for both the lube side and wash, but this sitename is listed seperately as two different sitenames instead of one, that's why I'm having this problem.

Hope that clears some things up!