Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Linked Subreport Formula Post Reply Post New Topic
Author Message
miamitourism
Newbie
Newbie
Avatar

Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
Quote miamitourism Replybullet Topic: Linked Subreport Formula
    Posted: 08 Nov 2012 at 9:04am
Hi folks,
I am a total newbie at this and really need some guidance! Cry
 
I already have two reports that are finished and wonderful:
  • One report is a list of customers that have bills due in the current month for a particular sales rep. Let's call this one IP_Revenue_Due
  • The other report is a payments report that shows which of those same clients have already paid. Let's call this one IP_Payments

I need to create a new report (let's call it IP_Rep_Bonus_Report) that has the first report (IP_Revenue_Due) in there as a subreport so that I can create a formula to figure out if the rep handling these accounts has achieved a certain sales/revenue collection benchmark.

Particularly, I need to create a formula field for the main report that looks at the sum of all revenue due for that month (that's a running total field that I already have in the subreport) and calculates what percentage of revenue due was actually collected, using the totals from the IP_Payments report. I have *no* idea where to start! Can anyone offer any suggestions?

SM
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 08 Nov 2012 at 9:45am
I am not entirely sure what you are trying to achieve.  But look into shared variables in a formula.  It may allow to achieve what you need.
IP IP Logged
miamitourism
Newbie
Newbie
Avatar

Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
Quote miamitourism Replybullet Posted: 08 Nov 2012 at 10:09am
Let's assume report A gives me a list of all clients belonging to a sales rep that need to pay their bill this month and at the end of it, there's a running total of the total amount of everyone's bills.
 
Report B is a report of all payments that were received during the month for that sales rep. I need a formula field that will figure out the percentage of revenue collected based upon report A's running total, and the running total of report B, which is the total amount collected that month.
 
If a sales rep exceeds 55% of revenue collections, they are entitled to a bonus payout.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Nov 2012 at 11:02am

if you stick with the design process of sub reports, Kevlray was on the mark with shared variables as your only real option.

However you likely can achieve what you want by altering your data set through joining the tables together in this new report % report.


Edited by DBlank - 08 Nov 2012 at 11:04am
IP IP Logged
miamitourism
Newbie
Newbie
Avatar

Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
Quote miamitourism Replybullet Posted: 09 Nov 2012 at 3:39am
Yes, I suspected that I would need to use a shared variable, but I have no clue how to even get started. I wouldn't know what to write in the shared variable formula.
 
I did try to join the tables to get the results I need...  it's a long story, but I can't do it because of the stupid way the DB was set up. Basically there is no relationship between payments and invoices in the DB, so the joining option isn't really an option for me.
IP IP Logged
miamitourism
Newbie
Newbie
Avatar

Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
Quote miamitourism Replybullet Posted: 09 Nov 2012 at 4:37am
OK, so I dug around a bit and tried the following shared variable formula in the subreport: (The #Month Total field already gives me the correct amount of all renewal revenue due in that month in the subreport.)
whileprintingrecords;
shared currencyvar RenewalRevenueDue := ({#Month Total})
 
In the main report, I created this shared variable formula:
 
whileprintingrecords;
shared currencyvar RenewalRevenueDue := 0
 
I then placed the shared variable formula into the main report, but no matter where I put it, details, header, footer, whatever, it's always $0, which is obviously not correct. I need to to give me the same value that I am getting for the #Month Total field in the subreport.

I feel like I'm getting closer, but could really use some help with this shared variable.
 
Humbly thanking the readers in advance for any help!Confused
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 09 Nov 2012 at 6:20am
Why are you setting the RenewalRevenueDue to zero in the main report?  If the variable is being assigned in the sub-report.  Just have the following code in a section after the sub-report (the sub-report cannot be hidden).

whileprintingrecords;
shared currencyvar RenewalRevenueDue
IP IP Logged
Post Reply Post New Topic
Printable version 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



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.