Duplicate Values

[acenterprises]

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

 

[briangriffin]

This is Lawson data, right?

Can you post your command? Offhand I would say you can probably modify the command to eliminate using the FBDETAIL table as a standalone and make your Crystal formulas simpler.

 

[acenterprises]

Yes, briangriffin, this is lawson data.

Here is my code for the Command statement:
SELECT
aux.MATRIX_CAT,
aux.MX_VALUE,
gla.COMPANY,
gla.ACCT_UNIT,
gla.ACCOUNT,
gla.SUB_ACCOUNT,
gla.FISCAL_YEAR,
gla.DB_AMOUNT_01,
gla.CR_AMOUNT_01,
gla.DB_AMOUNT_02,
gla.CR_AMOUNT_02,
gla.DB_AMOUNT_03,
gla.CR_AMOUNT_03,
gla.DB_AMOUNT_04,
gla.CR_AMOUNT_04,
gla.DB_AMOUNT_05,
gla.CR_AMOUNT_05,
gla.DB_AMOUNT_06,
gla.CR_AMOUNT_06,
gla.DB_AMOUNT_07,
gla.CR_AMOUNT_07,
gla.DB_AMOUNT_08,
gla.CR_AMOUNT_08,
gla.DB_AMOUNT_09,
gla.CR_AMOUNT_09,
gla.CR_AMOUNT_10,
gla.DB_AMOUNT_10,
gla.DB_AMOUNT_11,
gla.CR_AMOUNT_11,
gla.DB_AMOUNT_12,
gla.CR_AMOUNT_12

FROM GLAMOUNTS gla
JOIN GLNAMES gln
ON gla.COMPANY = gln.COMPANY
AND gla.ACCT_UNIT = gln.ACCT_UNIT

JOIN AUMXVALUE aux
ON gln.OBJ_ID = aux.OBJ_ID


WHERE gla.FISCAL_YEAR in (year(getdate()),year(getdate())-1)
--AND gla.ACCT_UNIT = '01000'
AND aux.MX_VALUE = 'ANES'
AND aux.MATRIX_CAT ='BUSINESSTYPE'


I appreciate all the help you can give me.

 

[briangriffin]

I don't have the AUMXVALUE table so I've left it off the example query. I would get it working without that table, make a note of the record count, then join it in and make sure the record count stays the same.

Anyway, this will return every budget account, and the corresponding activity if there is any:

 declare 
 @v_currentfy int,
 @v_priorfy int  
 
 set @currentfy = --your logic
 set @priorfy = -- your logic

select * -- add in the fields you want  

from  FBDETAIL fbd left outer join GLAMOUNTS gla
	on  fbd.acct_unit = gla.acct_unit
	and fbd.account = gla.account
	and fbd.sub_account = gla.sub_account
	and fbd.company = gla.company
	and fbd.fiscal_year = gla.fiscal_year

where
(fbd.fiscal_year = @v_currentfy or fbd.fiscal_year = @v_priorfy)
and budget_nbr = 1
and fbd.company = -- your company