Solved

Formula Help for eliminating duplicate values in Crystal reports

Posted on 2013-06-17
11
786 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
ID: 39254163
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
ID: 39254249
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
ID: 39254261
Change the <> to =

mlmcc
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:metalteck
ID: 39254302
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39254377
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
ID: 39254409
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
ID: 39254534
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
ID: 39254562
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
ID: 39283852
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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