grouping formula

[ontu78]

Hello,

We are currently in the process of designing a report from a table that has the following sample data

Bus Unit Affiliate Amount
----------- ---------- ------------

01 02 2500
01 03 2330
01 04 40
02 01 -2500
02 03 -223
03 01 -400
03 02 540
04 03 650
04 06 100

and so on....

In other words, the BU/Affiliate combinations do not follow any specific pattern.

We are tyring to present the data in the following way in the report

Bus Unit Affiliate Total
---------- ---------- --------

01 02 2500
02 01 -2500
--------
Subtotal: 0

01 03 2300
03 01 -400
--------
Subtotal: 1900

01 04 40
04 01 0 ( because combination not defined in table)
---------
Subtotal: 40

02 01
01 02 ..............These 2 rows should not be in the report, as they are already in the report....


02 03 -240
03 02 540
------
Subtotal: 300

and so on.............


In other words, the opposite Bus Unit/Affiliate combinations rows should be following each other.

If someone could provide suggestions about ways to accomplish this, we'd be very thankful.

Thanks again

 

[Madawc]

You need to create a formula field to select the correct value at detail-line level, then group by that.

From your data, I assume that the Affiliate should be used for grouping if it is less than the Bus Unit, otherwise use the Bus Unit.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[ontu78]

Thanks for your suggestion. The Bus Unit and Affiate values are actually characters ( as in GL01, GL02 etc). I'm somewhat confused as to how creating a formula field at the detail section and grouping by it, can enable the opposite Bus Unit/Affiliate to follow each other and calculate the subtotal. Can you please elaborate?

 

[IanWaterman]

YOu will have to use a command to union the data.

select businessunit as "Entity", 'B' as "Type", Amount
from your_table
union all
select affiliate as "Entity", 'A' as "Type", Amount
from your_table


YOu will need to expand the queries with whare clauses if you have other filters, you may also need to add in extra fields into select depending on what else you want to report.

Ian

 

[Madawc]

To elaborate on my solution, have something like:

if {your.Affiliate} < {your.BusUnit}
then {your.Affiliate}
else {your.BusUnit}

Group records by @GroupField.

This should still work for GL01, GL02 etc., though you could also extract the numeric value using something like

ToNumber(Right({your.Affiliate}, 2))

The same for BusUnit to get pure number.

Sort the group by BusUnit, this option should be found under 'Report' on the menus.

As for totals, it is easy to total for a group. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Try adding the table a second time and link {table.BusUnit} to {table_1.Affiliate} and link {table.Affiliate} to {table_1.BusUnit}, using left joins in both cases. Group on {table.BusUnit} and then place the fields from each table in separate detail sections_a and _b directly under one another. If this gives you the desired display (I can't easily test this), report back. I think you would then have to test for duplication and add section suppression.

-LB

 

[ontu78]

Hello Ian and Lbass,

I took both of your suggestions. Here is what I did:

- Used the following SQL:

select BU as "Entity", 'A' as "Type",Affiliate as "Entity2", Amount
from Mytable
union
select Affiliate as "Entity", 'B' as "Type", BU as "Entity2", Amount
from Mytable

This results in the following sample data:

Entity Type Entity2 BU Affiliate Amount
------ ---- ------- --- --------- ------

GL01 A GL58 GL01 GL58 -500
GL01 B GL58 GL58 GL01 500
GL58 A GL01 GL58 GL01 500
GL58 B GL01 GL01 GL58 -500

In the report, when I group by Entity, and then by Entity2, I'm getting duplicate rows in the following manner:


Group by GL01
Group by GL58

BU Affiliate Amount
-- ---------- -------

GL01 GL58 -500
GL58 GL01 500
-----
TOTAL: 0



Group by GL58
Group by GL01

BU Affiliate Amount
-- ---------- -------

GL58 GL01 500
GL01 GL58 -500
-----
TOTAL: 0



The "Group by GL01,then by GL58" is exactly what I want. However, this also creates the "Group by GL58,then by GL01"
rows, which are basically duplicate rows, and I dont want them to be displayed. Is there a way to achieve this?

Thanks again

 

[lbass]

You have two sets of rows because of the union. Did you try my suggestion on its own?

-LB