Using a formula to group? 1

[gapiesco]

I currently have a report which pulls data from a select field named chanout.channel where the selection "is one of" grabs info on 500+ entries. Since I have no other sort function from which to select, I'm assuming I can write a formulas to further define this group. Out of that 500+ channels entries, I would like to break down in 30+ city groups.


The report is currently sorting by a date group. I would like to create a second layer that would break down by city.

I'm currently running numerous separate SQL statements breaking things down in 10-20 piece chunks: select chanout.channel as “xxxx”, where (xxx.out_date between ‘xx-xx-2004’ and ‘xx-xx-2004’) and (chanout.channel in "xxx", "xxx,"))) order by chanout.channel, xxx.out_date, xxx.out_start;

 

[synapsevampire]

Pretty difficult to follow.

Try posting technical information rather than text descriptions.

Crystal version
Database used
Example data
Expected output

Since Crystal doesn't allow for numerous SQL statements as the data source for one report (unless they're joined or unioned, in which case they're not seperate), you might be better served elaborating on this as well.

You can use formulas to group data, but I'm not really sure what the question is here, perhaps that's all you wanted since you gave no indication of how one might extract state group data nor from what field.

-k

 

[gapiesco]

The specific information is of a confidential nature. It comes from an ODBC database. The field is chanout.channel. I would like to subdivide that field using a formula of sorts, something similar to SQL where I can stipulate a selection (similar to sort function in Crystal) where chanout.channel = (xxx, xxx, xxx, xxx).

Here's the sort formula as it current exists:
{outage.out_date} = {?Query dates} and
{outage.del} = "0" and
{chanout.channel} in ["xxx:314", "XXXX:313", "XXXX:343", "XXX:7040"]

Since I don't have the "technical" background, not sure I can be any more specific - unless you tell me what you need to know. Thanks.

 

[lbass]

I think you are showing the record selection formula, not a sort formula. If you want to cluster cities within groups, do not use the record selection formula area, but instead go to insert->formula field (this is in the field explorer)->new and enter a formula like {@cityclusters}:

if {table.city} in ["Boston", "Bangor","Hartford"] then "NorthEast" else
if {table.city} in ["Atlanta", "Miami"] then "SouthEast" else
if {table.city} in ["Los Angeles","Phoenix"] then "SouthWest" else
"Other"

Then you can insert a group on {@cityclusters}.

Not sure this is what you were looking for...

-LB

 

[gapiesco]

lbass,

I think your right. With a continuous series of if, then clauses, I should be able to break down my group. With 114 it will take a little time to input, but once I have it, I'm done - it'll be easy to update. I'll give it a try and see if that solves my problem. Thanks for deciphering my thread.

 

[gapiesco]

Lbass,

Can I use a similar statement to add another delimiter. For example:
if {table.city} in ["Boston", "Bangor","Hartford"]and if {outage.duration<'00:00:30'} then "NorthEast" else

 

[scottostanek]

You could also use the select/case statement to clean (YMMV) this up a bit. It seems like you want to test first how long the outage is and qualify the result so I added the "A:" and "B:" as examples. These could be reversed in nature if you want the longest outage reported first...

@Outageclusters
if {outage.duration<'00:00:30'} then
( select {table.channel}
case 1 to 313 : "A: Northeast"
case 314 : "A: Northeast Crner"
case 7040 to 7099 : "A: Section 3"
default: "Catchall"
) else
( select {table.channel}
case 1 to 313 : "B: Northeast"
case 314 : "B: Northeast Special"
case 7040 to 7099 : "B: Section 3"
default: "B: Catchall"
)

Scotto the Unwise

 

[lbass]

gapiesco,

If you add a delimiter to this formula, you are saying you only want to cluster these cities together IF they meet this other criterion, otherwise they belong in the "other" category--is that what you mean? If that's what you mean, you can use a formula like you suggested except you have an extra "if". Try:

if {table.city} in ["Boston", "Bangor","Hartford"] and{outage.duration} < time(00,30,00) then "NorthEast" else//etc.

Not sure what datatype {outage.duration} is, but I assumed it was a time.

If you only want to show cities on the report where the outage durage is greater than 30 minutes, then use time as a record selection criterion, and don't use it in the grouping formula.

-LB

 

[gapiesco]

You are correct. It is a time field. What I would like to accomplish is to take
if {table.city} in [xxx, xxx, xxx] then "Miami"

and add something like:

and outage.duration<'00:30:00'
and outage.duration>='00:30:00'

to subdivide channels within a city (XXX, XXX, XXX)into one of two or three additional categories.

I have a problem dealing with time because the way my database is set up, times are sometimes reflected like: 2 days, 23 hours, 53 mins, 10 secs. So we've had to write a huge formula to deal with time interpretation.

 

[lbass]

Instead of trying to combine the time grouping with the city grouping, I would insert another group based on the time intervals you want. What is your report structure? It looks like you might be trying to do something like:

Region (Clusters of Cities) - Group 1
City - Group 2 (Each city within the region)
Outage Duration - Group 3

Or maybe you want:

Region (Clusters of Cities) - Group 1
Outage Duration - Group 2
City - Group 3(Each city within the region by outage duration)

Is this correct? Can you give us samples of the various ways the time is displayed in your database, along with the datatype for the field? When you run the mouse over the field on the report, does it say "(String)" after the field name?

-LB

 

[gapiesco]

lbass,

I trying for:

By date
By City
By Outage duration (less than 30 mins)
By Outage.duration (greater than/equal to 30 mins)

Outage.duration is a timespan (String) Here's the first two of 18 lines of formula to deal with time:

If (mid({outage.duration},3,3)) = "day" and (length({outage.duration})) = 14 then
(cdbl(mid({outage.duration},10,2)))
Else If (mid({outage.duration},3,3)) = "day" and (length({outage.duration})) = 11 then
(cdbl(mid({outage.duration},10,2)))

I apologize for the many postings to get to the issue.

 

[lbass]

If the timespan formula is working properly, then you should be able to insert groups (in order) on:

{table.Date}
{table.City}
{@outageduration}

//where {@outageduration} is:
if {@yourtimespanformula} < 30 then "< 30 Mins" else
if {@yourtimespanformula} >= 30 then ">= 30 Mins"

This would give you a report design like:

03/23/04
Boston
< 30 Mins
Details
>=30 Mins
Details
Chicago
< 30 Mins
Details
>=30 Mins
Details
03/24/04
Boston
< 30 Mins
Details
>=30 Mins
Details
Chicago
< 30 Mins
Details
>=30 Mins
Details //etc.

Is this what you're looking for?

-LB

 

[gapiesco]

Yup! Give me a day or two to try and get it working. Thanks again.