Avatar of Taras
TarasFlag for Canada asked on

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.
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.
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
Mike McCracken

Can you upload the report file (rpt)?

I think you need to work on the query


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.
Mike McCracken

Have you tried using a multiple-column detail section?

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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
Mike McCracken

Is this a single table with 1 record for a club and a given role?  Perhaps 2-4 records in some cases?


I posted comment and attached a file that is not preset???
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Here again. Look at attachment.
Mike McCracken

In a single record from the query, do you have multiple roles or 1 role?


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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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?
Mike McCracken

I will work on a solution.  It can be done but you need to know the 10  clubs