Solved

Formula Help for eliminating duplicate values in Crystal reports

Posted on 2013-06-17
11
756 Views
Last Modified: 2013-07-05
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
0
Comment
Question by:metalteck
  • 4
  • 4
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:metalteck
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Change the <> to =

mlmcc
0
 

Author Comment

by:metalteck
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:metalteck
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You can't do that in the record selection but you can do it when you evaluate the totals.

mlmcc
0
 

Author Comment

by:metalteck
Comment Utility
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now