Solved

Formula Help for eliminating duplicate values in Crystal reports

Posted on 2013-06-17
11
799 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 35

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Throw Error 7 35
Applying Roles in Common Scenarios 3 19
Need age at date of document 5 20
A question about syntax 5 27
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 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