Crystal Report Array display in Group footer

I have a big table which is not normalized.
Table has data about Clubs, Club Addresses, Club Roles, Members, Member Addresses.
tblMain.
Fields:
Club_ID, C_Name , C_Street_Address, C_City, C_Province, C_Postal_Code,  C_Phone,  C_WebCite, Club_Role, Club_Role_Member_Name, C_Role_Member_Street_Address, C_Role_Member_City,
C_Role_Member_Province, C_Role_Member_Postal_Code, C_Role_Member_Phone, C_Role_Member_WSite.
Every club has 10 different Roles: Role1,Role2,Role3.....
All roles could have several members or none.Only  two has to be displayed on report.
Except for role2 here 4 members have to be displayed. If role does not have any member  empty space has to be displayed for that Role member.
User needs report that will have layout as next: I attached Paint file that can give you hint, sorry is not perfect.
To solve this I went with two groups and arrays approach.
I made two group First Grouping will be Club second will be Club_Roles.
I created a formula for Role member Info it has its name and complete address (Club_Member_Role_Info).
To present Club info and Club Role member’s information in layout as on example I attached here.
 I  put formula: Club_Member_Role_ Info in  an Array,  I created 10 arrays for each role, Role1_Array,Role2_Array…...
I was planning to use those 10 Arrays to pull out its individual members and display them in report on specific places in group footer.
For all of Arrays  it will be member1 and member2 except for Role2 it has four members.
I am declaring arrays in report header,
Uploading them on detail line, Redim (clearing) them in group header (group Club_Role).
And displaying them in in group footer (group Club_Role).
I have problem to display arrays.
E.g. for Role1 I want to display Role1_ Array (member1)  and Role1_Array ( member2)  separately and put them in two different text boxes, however I am getting both of them  together, I cannot separate them.
Is it problem that I am putting all formula (Club_Member_Role_Info )in array on detail line where I am uploading array.
Rep-Layout.png
TarasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Can you upload the report file (rpt)?

I think you need to work on the query

mlmcc
0
TarasAuthor Commented:
Hi mlmcc.
I got this data source as export from Raisers Edge application and it is in form of Access file.
I cannot change structure of tables, linking and rest. Tables are connected on that way that I got such big query which I cannot shape or change. It looks like one not normalized table and I need to deal with it.
I cannot upload file as confidential issue restrict me.
0
mlmccCommented:
Have you tried using a multiple-column detail section?

mlmcc
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

vastoCommented:
You can create a view in order to normalize the table. if you expect that the data will be reimported the view could be in another database ( access or SQLServer) . Your report will work with the view and the array will be not necessary
The view will use UNION and will add the recordset for each of the roles
0
mlmccCommented:
Is this a single table with 1 record for a club and a given role?  Perhaps 2-4 records in some cases?

mlmcc
0
TarasAuthor Commented:
I posted comment and attached a file that is not preset???
0
TarasAuthor Commented:
Here again. Look at attachment.
Arrays-In-Rep.txt
0
mlmccCommented:
In a single record from the query, do you have multiple roles or 1 role?

mlmcc
0
TarasAuthor Commented:
In single record from query I have one role one person.
Next record could have the same role but different person. One role could have several persons or One person could be member of several roles it is rare but posible.
0
mlmccCommented:
There is no need for arrays then.

Group as you have it by club then role
Set the detail section to use multiple columns
Set the column width so you get 2 columns
Set it to be across then down

Build the address blocks in the detail section

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Ok mlmcc that works just one more small question how to get all 10 Roles shown on report if some clubs do not have them.
Some have all of them some not. But I need to show all Role title(group title) and empty space for those members that are not present?
0
mlmccCommented:
I will work on a solution.  It can be done but you need to know the 10  clubs

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.