COMBINE SEVERAL RECORDS TO ONE

[GRECONCG]

Hi I am doing a tax report in Crystal and am listing all the counties in New York, now I want to take 5 specific counties and combine them so I just get one output for that and I want to call it NYC Tax. Is there a formula I could use to accomplish this? I am new to crystal and can't seem to figure out how to do this. Thanks

 

[CoSpringsGuy]

create a formula like this and then create a group on that formula

//
if {database county field} in ["county1","county2","county3","county4","county5",] then "NYC Tax"
else {database county field}

or you could eliminate all the other counties in your select criteria

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

remove the comma after county5

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[GRECONCG]

I tried that but I got an error message "a number, currency amount, boolean, date.....is expected here.(after the in). this is what I put in:

if {ADDRESS.COUNTY} in ["BRONX""RICHMOND""NEW YORK""QUEENS""KINGS""BROOKLYN""STATEN ISLAND"] then "NYC Tax"
else {ADDRESS.COUNTY}

I just want to combine five counties together to show as one and have the other in the state listed individually.

 

[CoSpringsGuy]

The example you just posted needs commas

if {ADDRESS.COUNTY} in ["BRONX","RICHMOND","NEW YORK","QUEENS","KINGS","BROOKLYN","STATEN ISLAND"] then "NYC Tax"
else {ADDRESS.COUNTY}

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[GRECONCG]

Thank you very much it works great.

 

[CoSpringsGuy]

Great! I was trying to duplicate the error message you recieved but was unable to. Glad its working

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[GRECONCG]

One more thing, in that group I woul dlike to just have totals, I have 4 columns of numbers but would like to just see one row showing totals, how can I do that?

 

[CoSpringsGuy]

right click the field you want totalled....

Click Insert -> Summary
Select Sum
Summary Location -> Group 3

Repeat for each column you want totalled

Then go to section expert and hide the detail section

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[GRECONCG]

I must have something wrong I can't select summary location -> group 3 and when I hide the detail section it hides all the other counties detail and I just want the NYC details hidden. When I use this formula it did put the 5 counties in one which is what I wanted then it has the others by them selves. I need to list all the counties and have the specific 5 labeled as NYC and then just have the NYC ones summarized all the rest still showing detail. Is there a better way to do this?

 

[CoSpringsGuy]

can you show what your out out looks like now and what you want it to look like? I think you can conditionally suppress the sections but im having trouble visualizing

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[GRECONCG]

Right now it looks like this:

ITEM TOTAL SHIPPING LOCAL TAX


NASSAU
221.75 12.10 20.17 33.85
198.75 27.00 19.47 25.75
24.95 6.93 2.75 31.88
397.50 17.40 35.79 14.90
290.00 30.00 27.60 20.00

133.00 93.43 $105.78 26.38

NYC Tax

79.75 10.50 8.01 90.25
150.20 10.45 14.26 60.65
230.00 27.50 22.85 57.50
510.00 10.45 46.19 20.45

970.00 58.90 $91.31 28.85

SUFFOLK

086.75 0.00 93.73 86.75
636.25 19.25 142.79 55.50
545.00 0.00 219.51 45.00
43.75 0.00 3.77 43.75
121.00 0.00 10.44 21.00
053.00 0.00 90.82 53.00
33.00 0.00 2.85 33.00
778.25 0.00 153.37 78.25
332.50 0.00 28.68 32.50
48.50 0.00 4.18 48.50
273.00 0.00 23.55 73.00
185.00 0.00 15.96 85.00
28.00 10.45 3.32 38.45
239.00 0.00 20.61 39.00
979.15 00.89 93.15 80.04
137.30 0.00 11.84 37.30
290.00 30.00 27.60 20.00

809.00 60.59 $946.17 70.04

The section that says NYC Tax I don't want details just totals, the rest I want the way they are. Thanks

 

[CoSpringsGuy]

go to Report -> Section Expert
click details
to the right, next to suppress, click the X-2 button

add this formula

{@whatever_you_named_that_formula_that_we_grouped_on} = "NYC Tax"

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[GRECONCG]

Perfect, thanks so much.