Formula Help for eliminating duplicate values in Crystal reports

I have a report written in Crystal 2008 off sql 2008 and I'm having a problem with duplicate totals in a running total.
In my report there are 2 date fields, FBDETAIL.FISCAL_YEAR and Command.FISCAL_Year.
FBDETAIL.FISCAL_YEAR is the main driver, while Command.Fiscal_Year may be necessary for validation certain conditions.

The premise behind the formula is to capture a Budget for 2013, even though there may not be Actuals for 2013. I need for the runnnig total to sum the value when the FBDETAIL.FIscal Year is 2013.
Problem is that when I try to only grab the values when FBDETAIL.FISCAL_YEAR = 2013, I will get the value of 200 instead of 100.  

I need to always grab the value when (#1) FBDETAIL.FISCAL YEAR = 2013 AND COMMAND.FISCAL YEAR = 2013 is met or
(#2)FBDETAIL.FISCAL YEAR = 2013 AND COMMAND.FISCAL YEAR = 2012 but only if situation #1 is not true.  


I would greatly appreciate any help you guys can give me and apologize if this question gives you a headache or is confusing.

EX.
FBDETAIL         COMMAND         VALUE    
2013             2013            100
2013             2012            100
2012             2013            100


Formula:
if val({?Year}) = {FBDETAIL.FISCAL_YEAR} and {Command.FISCAL_YEAR}-1 <> val({?Year})-1  then true else
if val({?Year}) = {FBDETAIL.FISCAL_YEAR} and {Command.FISCAL_YEAR} = val({?Year}) then true else
false
metalteckAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
I think you already get this, but the basic problem is that your test is just looking at the years in the current record, but (in generic terms), your data includes type A records and type B records, and you only want to include the type A records if there is no type B record.  Since you're just looking at one record at a time, you end up including both.

 I haven't really tried to think this through too thoroughly, but how about using two running totals, one for each condition?

 One that is evaluated when
{FBDETAIL.FISCAL_YEAR} = val({?Year}) and {Command.FISCAL_YEAR} = val({?Year})

 And one that is evaluated when
{FBDETAIL.FISCAL_YEAR} = val({?Year}) and {Command.FISCAL_YEAR} <> val({?Year})


 So, the first running total is for the records where both years match, and the second is for the records where the Command year is different.  Then have a formula use the first running total (where both years match) if it's not 0, otherwise it uses the second running total.

if {#firstrunningtotal} <> 0 then
  {#firstrunningtotal}
else
  {#secondrunningtotal}


 Like I said, I haven't tried to think that through too thoroughly, but it seems like it would work, at least for producing a low level total.  If you're trying to summarize those figures at a higher level, I guess you'd need to use a variable and add the appropriate running total to the variable.

 If the data could include other years, you may need to refine the conditions for the second running total.  Maybe something like:

{FBDETAIL.FISCAL_YEAR} = val({?Year}) and {Command.FISCAL_YEAR} = val({?Year}) - 1


 Also, your record selection formula probably includes something like {FBDETAIL.FISCAL_YEAR} = val({?Year}).  If it does, you don't need to check that in the running total formulas.

 James
0
 
mlmccCommented:
Try

 
if val({?Year}) = {FBDETAIL.FISCAL_YEAR} and {Command.FISCAL_YEAR} = val({?Year}) then
       true
else if val({?Year}) = {FBDETAIL.FISCAL_YEAR} and {Command.FISCAL_YEAR}  <>  val({?Year})-1  then
      true
else
     false

mlmcc
0
 
metalteckAuthor Commented:
Thanks mlmcc...this works, but its still missing one situation.
I have certain records where the only detail is FBDETAIL.FISCAL_YEAR = 2013 and Command.Fiscal_Year = 2012. Can we modify your code to only include this situation if 2013 doesn't exist for both?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mlmccCommented:
Change the <> to =

mlmcc
0
 
metalteckAuthor Commented:
Problem with that is that its causing my numbers to duplicate because it is grabbing both situations: when both fields = 2013 and when only fbdetail= 2013 and command = 2012.

I need it to grab one or the other, not both. Do you think this can be done?
0
 
mlmccCommented:
ON a given record only one of those can be true.

Are you saying the records are "related" in some way so if there is one with both of 2013 then you don't want any with 2013 and 2012?

mlmcc
0
 
metalteckAuthor Commented:
mlmcc...you are correct and for the majority of records, this is working correctly.
There are certain records that have both situations, which is causing the duplication. But you are correct, I will always want both of the 2013. But if the fbdetail is the only 2013, I need that one. I need some sort of check that will look at the previous record or something that will avoid the dupliation.

Hope that helps.
0
 
mlmccCommented:
You can't do that in the record selection but you can do it when you evaluate the totals.

mlmcc
0
 
metalteckAuthor Commented:
The formula is currently being placed in evaluate formula field in the running totals.
Should I place it somewhere else? If so, where and how should I write it to check?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.