Crystal Reports Formula in Groups

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Formula in Groups

Author	Message
gem1204 Newbie Joined: 10 Jan 2010 Online Status: Offline Posts: 4	Topic: Formula in Groups Posted: 20 Apr 2010 at 9:51am
	I have a report where I'm sales my product. I have a formula field that shows the gross profit margin in the footer of the report that works just fine. the formula is: If Sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue}) >0 then ((Sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue})- Sum({Proc_ExecSummary_IncomeStmntByMonth;1.GrandTotalCogsVar})) / sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue})) *100 else 0 This works fine for my footer but I also need to show the same summary for each product but when I put this formual field the group footer for each product it still computes the gross profit margin for the whole report. How can I set up my formula to run based on each group?
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 20 Apr 2010 at 9:58am
	Change your formula to use summarized data for group level Summary(field to sumamrize, group field) If Sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue,table.product}) >0 then ((Sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue,table.product})- Sum({Proc_ExecSummary_IncomeStmntByMonth;1.GrandTotalCogsVar,table.product})) / sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue,table.product})) *100 else 0 NOTE: you have to have an actual report group that uses the 'Table.Product' field Edited by DBlank - 20 Apr 2010 at 9:59am
	IP Logged

gem1204 Newbie Joined: 10 Jan 2010 Online Status: Offline Posts: 4	Posted: 21 Apr 2010 at 4:00am
	Thanks for your help The datasorce for the reports is an SQL Server stored procedure named Proc_ExecSummary_IncomeStmntByMonth_Details and the group I want to perform the summary on is called SONumber. I have a group based on SoNumber, the actual field from the stored procedure as shown by crystal reports is {Proc_ExecSummary_IncomeStmntByMonth;1.SoNumber}. I tried your suggestion and added the table name and field name to formula using my stored procedure as the table name. ( I assumed you were talking about "record source" and not necessarily a table name.) I havn't been able to get it to work. I entered the the field name as you suggested using my stored procedure name as the table name and get an error "the field name is not known.” This is a sample of how I'm trying to use your suggestion to use table name and field name. ---------------- Sum({Proc_ExecSummary_IncomeStmntByMonth;1.Revenue},{Proc_ExecSummary_IncomeStmntByMonth;1.SoNumber}) --------------- I have tried it with and without braces enclosing the table name and field name. Do you have any idea what I'm doing wrong now? Thank you for responding to my question so quickly. I hope you can determine what I'm doing wrong and help me fix this. Thanks
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Apr 2010 at 4:17am
	You are correct in that the table=source. Can't tell why it is choking on that formula. Group level summaries are Function (field_to_Summarize, Grouped_field) so your formula looks correct. Try using the insert Smmary function to get the fields you need to appear in the Formula editor. Click on the Insert Summary (Sigma Sign button) Select the Revenue field Make it a SUM Summary Location = SoNumber group. Now in your formula editor under the Report Fields you should now see a field that has Sigma SIgn in fromnt of it and labeled something like: Group#1:Proc_ExecSummary_IncomeStmntByMonth;1.SoNumber - A: Sum of Proc_ExecSummary_IncomeStmntByMonth;1.Revenue You can now select this as a usable field for writing formulas Does this help? Edited by DBlank - 21 Apr 2010 at 4:18am
	IP Logged

gem1204 Newbie Joined: 10 Jan 2010 Online Status: Offline Posts: 4	Posted: 21 Apr 2010 at 5:40am
	Thanks for your help DBlank. I've been to a lot of forums and this is by far the best. I've never gotten such quick responses and expert help in any of the other forums. This forum is great
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum