Add Group doesn't display all fields.

[CrystalVisualBOracle]

Hi all. I'm trying to group by a field called description in my report but when I click to Add a Group in CR, that field doesn't appear. My report is based on a Crystal SQL Query which looks as follows:
SELECT
A.MC_LICENSE_ID,
A.COUNT,
B.HITS,
B.USERS,
C.M_PUBLICATION_ID,
C.DESCRIPTOR,
C.MC_MEGACLIENT_ID,
C.MC_MEGACLIENT_KEY
FROM
MC_LICENSE_DATA A,
MC_PUBN_HITS B,
MC_VALID_PUBLICATION C
WHERE
A.M_PUBLICATION_ID (+)= C.M_PUBLICATION_ID AND
A.MC_MEGACLIENT_ID (+)= C.MC_MEGACLIENT_ID AND
C.MC_MEGACLIENT_ID = B.MC_ID (+) AND
C.MC_MEGACLIENT_KEY = B.MC_KEY (+) AND
C.M_PUBLICATION_ID = B.PUBN_ID (+) AND
C..MC_MEGACLIENT_KEY = '{?MegaClient_Key}'

When I add this query to my report, I can add any of the fields to see them on the report. Unfortunately, only the other 7 fields (not descriptor) appear in the drop down box to add as a group. Does anyone know why it won't display all of the fields so I can choose which to group by?

Thanks,
CrystalVisualBOralce

 

[smcnulty2000]

When I've seen this, it has always been because the field I was trying to use was of a datatype that doesn't support grouping. Like the Memo field in MS-Access.

Basically, if your "description" field is too large of a field, you will see this kind of problem.

What I would try, and I don't know if this would work, would be to put the field into a formula that trims it. If it isn't available in the drop-down boxes, then I would put it in by hand.

Then group by the formula, instead of the field.

If you can manipulate the database itself, you could put a short description field in, parse a copy of the first part of the "descrption" over to it, then pull that into your query for grouping.

Maybe someone else has some better suggestions.

 

[kenhamady]

Fields that aren't available for grouping also won't be available for forumulas. See the FAQ on memo/VarChar fields.

You might have to create a virtual column in your database that is shorter (view/SP) and use that as your group field. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/