Hello,
I’m Using Crystal 2013 on Windows 10 with an Access/Excel (DAO) connection to an Excel spreadsheet as the data source. (Need Excel Data as the source for this project, normally not optimal, I know). I’m having issues with a formula dependent on other formulas accurately summing $ amounts at the group header level.
The sum formula is trying to get the Year-to-Date total transaction amount for the account group. (Year to Date is based on a fiscal year 7/1 to 6/30, which is entered as a parameter by the user, along with the Month of the transaction)
Formula: @AMOUNT YTD:
if {TRANS_CSV.TYPE} = "TRANSACTION" and {@PERIOD-FY} = to_number({?Current Fiscal Year}) then {TRANS_CSV.AMOUNT}
Dependent formula @PERIOD-FY:
Evaluateafter ({@PERIOD-MONTH});
Evaluateafter ({@PERIOD-YEAR});
If {@PERIOD-MONTH} in ['JUL','AUG','SEP','OCT','NOV','DEC'] then {@PERIOD-YEAR} + 1
else {@PERIOD-YEAR}
Dependent formula @PERIOD-MONTH:
whilereadingrecords;
ucase(left({TRANS_CSV.PERIOD},3))
Dependent formula @PERIOD-YEAR:
whilereadingrecords;
IF isnumeric(right(TRANS_CSV.PERIOD},4)) THEN tonumber(right({transactions_csv.Period},4))
Note: The field TRANS_CSV.PERIOD represents a month and year but is a string displayed as MMM-YYYY (i.e. JAN-2018) in the Excel data source. There is not ‘transaction date’ field.
The above formulas are all working correctly and a sum of @AMOUNT YTD is placed among many other sums in the Group header, but it appears all of the transactions of the last full calendar year are being included in the sum, when I only want the ones from the beginning of the fiscal year included. For instance, if the user-chosen parameters are Month=FEB and FY =2019, then all transactions dating back to February of 2018 are summed, when I only want the ones from July 2018 to February 2019 included.
It may have something to do with the sequence of when the formulas pass through CR, or perhaps I’m not doing this the right way? Any help would be much appreciated.
Thank you in advance.
I’m Using Crystal 2013 on Windows 10 with an Access/Excel (DAO) connection to an Excel spreadsheet as the data source. (Need Excel Data as the source for this project, normally not optimal, I know). I’m having issues with a formula dependent on other formulas accurately summing $ amounts at the group header level.
The sum formula is trying to get the Year-to-Date total transaction amount for the account group. (Year to Date is based on a fiscal year 7/1 to 6/30, which is entered as a parameter by the user, along with the Month of the transaction)
Formula: @AMOUNT YTD:
if {TRANS_CSV.TYPE} = "TRANSACTION" and {@PERIOD-FY} = to_number({?Current Fiscal Year}) then {TRANS_CSV.AMOUNT}
Dependent formula @PERIOD-FY:
Evaluateafter ({@PERIOD-MONTH});
Evaluateafter ({@PERIOD-YEAR});
If {@PERIOD-MONTH} in ['JUL','AUG','SEP','OCT','NOV','DEC'] then {@PERIOD-YEAR} + 1
else {@PERIOD-YEAR}
Dependent formula @PERIOD-MONTH:
whilereadingrecords;
ucase(left({TRANS_CSV.PERIOD},3))
Dependent formula @PERIOD-YEAR:
whilereadingrecords;
IF isnumeric(right(TRANS_CSV.PERIOD},4)) THEN tonumber(right({transactions_csv.Period},4))
Note: The field TRANS_CSV.PERIOD represents a month and year but is a string displayed as MMM-YYYY (i.e. JAN-2018) in the Excel data source. There is not ‘transaction date’ field.
The above formulas are all working correctly and a sum of @AMOUNT YTD is placed among many other sums in the Group header, but it appears all of the transactions of the last full calendar year are being included in the sum, when I only want the ones from the beginning of the fiscal year included. For instance, if the user-chosen parameters are Month=FEB and FY =2019, then all transactions dating back to February of 2018 are summed, when I only want the ones from July 2018 to February 2019 included.
It may have something to do with the sequence of when the formulas pass through CR, or perhaps I’m not doing this the right way? Any help would be much appreciated.
Thank you in advance.