Formula to group on elements of a field

[IanWaterman]

I am building a funding report, where funds can be allocated to specific or groups of regions, each region is designated by a single letter. The regions are entered as a string of letters in a single field

Fund Region

Fund 1 A
Fund2 BC
Fund3 CD
Fund4 AEF
Fund5 BDF

I can design report to give me a report for all funds accessible by a single region eg A will return Funds 1 & 4., ie run the report for each region separately.

However, I can not do a report for all Regions, which will group the funds eg

Region A Funds 1 & 4
Region B Funds 2
Region C Fund 2 & 3
Region D Funds 3 & 5
Etc

All l get is the first instance of the fund

Region A Fund 1 & 4
Region B Fund 2
Region C Fund 3

Ie it assigns fund 2 to region B, and can not be seen in the report by Region C

There are 16 regions and each fund can be assigned to 1 to 7 regions, the data can be added to the Region field in any order, as is can be appended restrospectively. (ie the list need not be alphabetical.

It is not practical run the report 16 times, the only alternative I can think of is to create a temporary table using PLSQL, but I am trying to avoid this.

Thank you

Ian

 

[Ngolem]

Hi Ian

I see your problem. Crystal does not run through data more than once.

If I were doing this report I would not group on Region (but perhaps you have other reasons to do so.)

Rather I would group on the Fund and as the data is run through I would store the regions which use this fund in a variable String array. The array would be then used in a display in the report footer....or a group footer if the analysis was to be repeated.


@Initialize (placed in the report header suppressed)

WhilePrintingRecords;
//initialize 10-20% more regions than necessary
//if there was a possibility of expansion of regions.
//first element is Region A...second is Region B....etc
stringVar Array Regions := ["","","","",.......,""];
" ";//necessary to make the formula legal

now with fund as the last grouping place the following in the detail section

@itemizeRegionalFunding

WhilePrintingRecords;
stringVar Array Regions ;
stringVar tempRegion := {Table.Region};
numberVar icount;

for icount := 1 to Length(tempRegion) do
(
//uppercase A is ascii 65
if Regions[asc(tempRegion[icount]) - 64] <> &quot;&quot; then
Regions[asc(tempRegion[icount]) - 64] :=
Regions[asc(tempRegion[icount]) - 64] + &quot;, &quot;; +
{table.Fund}
else
Regions[asc(tempRegion[icount]) - 64] :=
Regions[asc(tempRegion[icount]) - 64] ;
)

now in your report footer you can display the results

I'd make 2 colums of results (dividing the array in half)

the first formula

@DisplayRegionFundingA

WhilePrintingRecords;
stringVar Array Regions ;
numberVar icount;
StringVar result := &quot;&quot;;

//make sure you have an &quot;even&quot; number of array elements
for icount := 1 to ubound(regions)/2 do
(
result := result + &quot;Region &quot; + chr(64 + icount) + &quot; &quot; +
Regions[icount] + chr(13] + chr[10}
);

result;

the other formula woi=uld be similar from
ubound(regions)/2 +1 to ubound(regions)

make the display fields &quot;can Grow&quot; and that should do it.

Jim Broadbent

 

[IanWaterman]

Jim

Thanks for your input, I have a report grouped on funds and lists regions, that is used by the fund managers.

This report is for the Regional Financial controllers who need to know where they can draw money.

Ian