summary by division

[zsyed]

I've a crystal report that need to show summary by division except one division which would need to be merged with one of the other divisions. The logic to share the merger is as follows. First 3 digits have Division code and the rest 8 digits have common account codes. When it comes to MAI division the account code would match with one of the other divisions. Where ever the account matches with other divisions then that MAI value would be part of the other division summary. How can build a formula to have this summary? Please find below example data.



div code value
ELE ELE40040000 250
MAI MAI40040000 150
MAI MAI40740000 200
MAI MAI43010000 300
PCE PCE40740000 400
REN REN43010000 100

Result
ELE ELE40040000 250
MAI40040000 150
total ELE 400
PCE PCE40740000 400
MAI40740000 200
Total PCE 600
REN REN43010000 100
MAI43010000 300
Total REN 400

Many Thanks in Advance.

 

[kray4660]

From what I can tell you need to do a grouping by the numeric part of the code.

Formula to group by.
Right({code},5)

Then you can have a running total to sum the value and reset on the group.

I see that you do not display the div for the MAI. You can have a formula like this (you can include the value if you want):

if {div} = "MAI" then
{code}
else
{div}+" "+{code}

I hope this helps.

 

[CoSpringsGuy]

Will there always only be one value for MAI associated with only one code?
if so group as Kray mentioned then create a formula for the group footer.

Minimum({value},{div})%maximim({value},{div})

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

Minimum({value},{KrayFormula})%maximim({value},{KrayFormula})

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[zsyed]

sorry I must tell you that There will be more than one code associated with each division. MAI would have all possible numeric part that could possibly be associted with other divisions. Actually the I would need a report by division group except MAI. because MAI need just need to be split into and associate with other groups.
I could not follow formula grouping.
Thanks.

 

[zsyed]

In principle, MAI was an old division catering all accounts. Now We have new divisions formed due to enhanced activity. Hence, we tend to see both. This is transition period, from MAI to new divisions. Over a period of time MAI slowely disappeas from the list and can only have other divisions. Trust this helps understanding the logic.

 

[lbass]

Try grouping by:

mid({table.code},7,3)

-LB