I need to find a fiscal year based on today's date and then use it in record selection

[former_member759204]

I am making a cross-tab report. I need to be able to reduce the records to current FISCAL year and 3 full fiscal years of history.
The fiscal year does not line up with any calendar years so I can't use any of the default system functions. The data does contain both a fiscal year field and a full transaction date so both options are available for use.

I have formulas in the formula fields that arrive at the correct years for starting and ending, but I having issues getting these same pieces of info into the record selection to keep the cross tab within the defined parameters.

For example, today's date is Sep 8, 2021. However, for this report and data, the fiscal year is year ending 2022. But Jan 2022 would also be in fiscal year 2022 based on the actual year end of the company. So I can't outright use the current year plus 1 or the current year directly in the record selection.

I have tried so many different ways to do this to no avail. At the end of the day, I need to be able to reduce the records to the current fiscal year plus the 3 previous fiscal years in my cross tab report.

My only other idea is to create a sub-report with the crosstab in it and figure out the years in the main report sending the actual parameters to the sub-report. But there must be an easier way for this record selection that I just can't think of.

Thank you for any suggestions!

Jody

[DellSC]

What are the start and end months of your fiscal year?

[former_member759204]

For one company that this report is for FY ends July. Another company ends in April.

So ideally, I would like to also make this so it can be based off the field audorg as well in the data. But I can make 2 reports if absolutely necessary. But for the purpose of the record selection, being able to identify the fiscal year should be sufficient I am thinking. But maybe not.

[ido_millet]

Take the online help example of returning the fiscal quarter and adapt it to your fiscal year logic.

I prefer the Crystal Syntax (see "Select expressions (Crystal syntax)" in the online help). But the core idea is the same: Crystal can't guess what your fiscal year date boundary logic is, so you need to provide it using a formula.

[former_member11696]

Hi Jody,

I know it's not common now adays but if you hit the F1 key when in CR Designer it opens up the Help file.

Select the Search tab and type in "fiscal year"

Maybe that's what you are looking for....

[former_member759204]

Thank you. However, I get nothing about fiscal year in my help directory sadly. Just the choose function. Though it may be possible to work with that... I will expore.

Thank you.