How to pass data from detail section as input of formula in Crystal Reports
For Example :
Crystal Reports "Detail Section A - Sorted By Customer ID 001" - data show as below
ITEM NAME SHELF NO QTY
Item A 1 12,000
item B 2 14,000
item C 2 10,000
item D 3 12,000
Crystal Reports "Detail Section B - Sorted By Customer ID 002" - data show as below
ITEM NAME SHELF NO QTY
Item E 4 16,000
item F 4 10,000
item G 5 18,000
item H 6 14,000
How do I count the percentage of QTY by SHELF NO for each Customer ID?
The results look like :
Customer ID 001
SHELF NO 1 : 25% ((12,000) / (12,000 + 14,000 + 10,000 + 12,000))
SHELF NO 2 : 50% ((14,000 + 10,000) / (12,000 + 14,000 + 10,000 + 12,000))
SHELF NO 3 : 25%
Customer ID 002
SHELF NO 4 : 44.8276%
SHELF NO 5 : 31.0345%
SHELF NO 6 : 24.1379%
No need to get report like this - just like to know how to pass SHELF NO and QTY into formula or any other way also can get the same result.
Please help
Crystal Reports "Detail Section A - Sorted By Customer ID 001" - data show as below
ITEM NAME SHELF NO QTY
Item A 1 12,000
item B 2 14,000
item C 2 10,000
item D 3 12,000
Crystal Reports "Detail Section B - Sorted By Customer ID 002" - data show as below
ITEM NAME SHELF NO QTY
Item E 4 16,000
item F 4 10,000
item G 5 18,000
item H 6 14,000
How do I count the percentage of QTY by SHELF NO for each Customer ID?
The results look like :
Customer ID 001
SHELF NO 1 : 25% ((12,000) / (12,000 + 14,000 + 10,000 + 12,000))
SHELF NO 2 : 50% ((14,000 + 10,000) / (12,000 + 14,000 + 10,000 + 12,000))
SHELF NO 3 : 25%
Customer ID 002
SHELF NO 4 : 44.8276%
SHELF NO 5 : 31.0345%
SHELF NO 6 : 24.1379%
No need to get report like this - just like to know how to pass SHELF NO and QTY into formula or any other way also can get the same result.
Please help
ASKER
To be more specific - I need all those number of percentage as variables and use them as one element of formula in other sub-report's detail section
To accomplish this you need to
1. Group by the Customer ID
2. Group by the Shelf No
Shelf Percent is calculated with
Sum({Qty},{Shelf Number}) / Sum({Qty}, {Customer ID})
Put the formula in the Shelf Group Header or Footer
mlmcc
1. Group by the Customer ID
2. Group by the Shelf No
Shelf Percent is calculated with
Sum({Qty},{Shelf Number}) / Sum({Qty}, {Customer ID})
Put the formula in the Shelf Group Header or Footer
mlmcc
... and use them as one element of formula in other sub-report's detail sectionYou want to pass these values to a subreport?
Assuming that the answer is "Yes" ...
Where is that "other" subreport?
Since you said "other" subreport, where are the quantity values that you're using to calculate those %'s located? Are they in the main report or in a different subreport?
The answers to those questions may determine the answer to the following question:
Are you just trying to pass the values (%, etc.) for one shelf at a time to the subreport?
Or are you trying to pass the values for multiple shelves, maybe all of them, to the subreport at the same time?
James
ASKER
Thank you James,
I was trying to pass all percentage (SHELF NO 1 - 25%, SHELF NO 2 - 50%, ...) to another subreport in same main report (For example : This main report contains five subreports : subreport 1, subreport 2, ... subreport 5 (they are from different tables)
Subreport 1 - have all ITEM NAME, SHELF NO and QTY information - Can I pass all percentage data - calculated from subreport 1 (or all ITEM NAME, SHLEF NO and QTY) data to subreport 5 in same main report? (I tried but only get the first row of data - Item A from subreport 1 Detail Section)
I was trying to pass all percentage (SHELF NO 1 - 25%, SHELF NO 2 - 50%, ...) to another subreport in same main report (For example : This main report contains five subreports : subreport 1, subreport 2, ... subreport 5 (they are from different tables)
Subreport 1 - have all ITEM NAME, SHELF NO and QTY information - Can I pass all percentage data - calculated from subreport 1 (or all ITEM NAME, SHLEF NO and QTY) data to subreport 5 in same main report? (I tried but only get the first row of data - Item A from subreport 1 Detail Section)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you James and Michael
As non-programmer and in one man Dept. , I spent lots of time to googling and figure out the solution (Array) according to your guidance. Currently I can pass whole set of data from one subreport to another subreport as elements of formula in there.
I learned a lot from both of your answer above, I really appreciate your help.
Thank you again
As non-programmer and in one man Dept. , I spent lots of time to googling and figure out the solution (Array) according to your guidance. Currently I can pass whole set of data from one subreport to another subreport as elements of formula in there.
I learned a lot from both of your answer above, I really appreciate your help.
Thank you again
You're welcome. Glad I could help.
James
James
http://devshed.us/Blogs/tabid/227/EntryId/31/Percentage-of-Total-field-in-a-Crystal-report.aspx
Open in new window
I would assume this could also go in the group footer.