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_Addre
C_Role_Member_Province, C_Role_Member_Postal_Code,
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
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_Addre
C_Role_Member_Province, C_Role_Member_Postal_Code,
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
ASKER
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.
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.
Have you tried using a multiple-column detail section?
mlmcc
mlmcc
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
The view will use UNION and will add the recordset for each of the roles
Is this a single table with 1 record for a club and a given role? Perhaps 2-4 records in some cases?
mlmcc
mlmcc
ASKER
I posted comment and attached a file that is not preset???
ASKER
Here again. Look at attachment.
Arrays-In-Rep.txt
Arrays-In-Rep.txt
In a single record from the query, do you have multiple roles or 1 role?
mlmcc
mlmcc
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
I will work on a solution. It can be done but you need to know the 10 clubs
mlmcc
mlmcc
I think you need to work on the query
mlmcc