I have a table with main categories followed by sub categories.

 

Example:

 

100      Fruits            <-- Main category

100.1   Apples          <-- Sub category

200      Vegetables   <-- Main category

200.1   Carrots         <-- Sub category

etc...

 

I have grouped the report on the main categories easily enough but I can't figure out for the life of me how to get the description label to return only the main category description (ie: Fruits, Vegetables, etc...)

 

I figured out how to return the proper description with a Select Case formula but there are over 25 main categories & I'm sure there has to be a simpler way.  Could somebody please help point me in the right direction.

 

Thanks kindly

bCritter

 

 

I would try to create a formula that looks something like this:

 

left({MyTable.CategoryField}, InStr({MyTable.CategoryField}, '.') - 1)

 

Then group on this formula and sort on the Category Field.  This will always get you the main category as the first record in the group.  You can then put your data in a Group Header section to get just the main group listing.

 

This method assumes that you want to include the subgroup data in the query so that you can do sums or counts or something of the sort.

 

If you don't want the subgroup data to appear in the report at all, there's a way to filter for that - however, it's more efficient to do that in the query than it is to use a Crystal formula for it - if you use a Crystal formula, Crystal will bring ALL of the data into memory and filter it there.  What type of database are you connecting to?  With that information I can probably give you the exact syntax to use for the filter to get this done in the database.

 

-Dell

 

If {MyTable.CategoryField} = {@GroupFormula} then '*'

else {MyTable.AssetID}

 

If the Asset ID field is a number, use -1 instead of '*'.  This should get your main category to the top of the list.

 

-Dell