Hello,

I am using Crystal XI and designing a  cross tab report where in I summarize sales by month (rows) and by Category (columns). My problem is that the category itself is contained in a serial # as in AB-239878932; AB-32094809; XY-223430098, etc. where AB and XY are the categories

When I generate the cross-tab I obviously get each reference as  a separate column. I would like to 'group' all the AB's under one column and summarize them as a group; and ditto with XY's, etc.

I've tried to look into groupings but there seems to be no option to use a formula to produce the column/grouping based on the string starting characters. In any case the cross tab is in the report header so I doubt the grouping would have worked.

I've tried the select expert which could allow me to use two different cross-tabs but it still does not do a grouping by categories.

I was trying to avoid different subreports as I find myself creating a lot of sub-reports I'm starting to worry about maintenance down the road if ever I have to re-engineer them.

I was hoping for some ability such as when I group by date then CR gives me the option to select to group by date year, date moth, date, week, etc.)

Help and Thanks



Edited by wunof11 - 24 Oct 2011 at 3:05pm

I tried subscript [] as in TableName.FieldName [1 to 2] to extract the first two characters then used that value as a column but all it did was return the 2 characters but still give a separate column for each record. Could you suggest another formula?

Just in case I may not have explained my self too well but in the sample values I gave in the original post  I was hoping to get a result such as this:


       

	AB	XY	CC
Sep-11	Sum of all ABs in Sep	Sum of all XYs in Sep	Sum of all CCs in Sep
Oct-11	Sum of all ABs in Oct	Sum of all XYs in Oct	Sum of all CCs in Oct
Nov-11	Sum of all ABs in Nov	Sum of all XYs in Nov	Sum of all CCs in Nov

 Sorry DBlank, I just realized you did give a formula to try out. Will do so and let you know how it comes out


Edited by wunof11 - 25 Oct 2011 at 1:31am