Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: last fiscal year Post Reply Post New Topic
Page  of 2 Next >>
Author Message
kreusch
Newbie
Newbie


Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
Quote kreusch Replybullet Topic: last fiscal year
    Posted: 06 Feb 2014 at 9:13am

Hi,
I am using the formula below to pull my last fiscal year totals. our fiscal year starts in may. The formula was working great as of Dec 2013 but when we run it as of Jan 2014 I am not getting any data. Any insight is appreciated.


//Last FY
If {GLIncomeStatementStage1.GLTransactionDate}in date (year ({GLIncomeStatementStage1.SelectDate})-(if month ({GLIncomeStatementStage1.SelectDate})<5
then 2 else 1),5,1)
to dateadd ('YYYY', - (if month ({GLIncomeStatementStage1.SelectDate})<5 then 2 else 1), {GLIncomeStatementStage1.SelectDate})
then {GLIncomeStatementStage1.GLTransactionDebitAmount}
else 0
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 06 Feb 2014 at 12:12pm
Is the determination of the "current fiscal year" based on the current date or on some date parameter?

I would break this down into multiple formulas to make it easier to track what's happening and where you might be seeing ann an error... So, assuming that the FY starts on May 1st, the start and end dates of the current fiscal year would be this:

Current FY Start Date
if Month(currentdate) >= 5 then Date(Year(currentdate), 5, 1) else Date(Year(currentdate) - 1, 5, 1)

Current FY End Date
if Month(currentdate) >= 5 then Date(Year(currentdate) + 1, 4, 30) else Date(Year(currentdate), 4, 30)

If the calculation has to be based on the SelectDate field, then replace "currentdate" with "{GLIncomeStatementStage1.SelectDate}" in these first two formulas.

Your prior year start and end dates would then look like this:

Prior Start Date
DateAdd('yyyy', -1, {@Current FY Start Date})

Prior End Date
DateAdd('yyyy', -1, {@Current FY End Date})

You would then re-write the final formula to something like this:

If {GLIncomeStatementStage1.GLTransactionDate} in {@Prior Start Date} to {@Prior End Date} then {GLIncomeStatementStage1.GLTransactionDebitAmount}
else 0

By breaking it down this way, you can temporarily add any or all of the date formulas to your report to see what the values are for debugging purposes.

-Dell
IP IP Logged
Jimm
Newbie
Newbie


Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
Quote Jimm Replybullet Posted: 04 Apr 2017 at 2:57am
Date(Year(currentdate), 5, 1)

what is 'Date' mean here?

I tried it in sql and it can not recognize 'date'
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 Apr 2017 at 4:14am
That is a Crystal function, not a sql function. It converts the year, month, day parameters to a date.

What type of database are you connecting to? I may be able to get you the correct syntax, but each type of database is different.

-Dell
IP IP Logged
Jimm
Newbie
Newbie


Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
Quote Jimm Replybullet Posted: 05 Apr 2017 at 3:31am
Thanks, Dell.

I am trying to set up crystal report to pull fiscal year (from October 1 last year to 9/30 next year). I think the most easiest way put @fiscalYearBegin and @FiscalYearEnd in sql WHERE clause in below sample sql code:

select

HSPC.LEVEL_OF_CARE,
Case when FINANCIAL_CLASS_NAME = 'Medicare' Then 'Medicare'
     when FINANCIAL_CLASS_NAME = 'Medicaid' Then 'Medicaid'
      ELSE 'Other' End as FINANCIAL_CLASS_NAME,
COUNT(HSPC.TX_ID) as DaysLevCare,
--HSPC.CALENDAR_DATE,as
convert(varchar, HSPC.CALENDAR_DATE, 103) as CALENDAR_DATE,

HSP.Serv_Area_ID as ServiceArea,
LOC.LOC_ID

From
V_HSPC_COST_REPORT_DATA HSPC inner join HSP_TRANSACTIONS HSP
on HSPC.TX_ID = HSP.TX_ID LEFT Join CLARITY_LOC LOC
ON HSP.FACILITY_ID = LOC.LOC_ID
where
(HSPC.CALENDAR_DATE >= {?FiscalYearBegin} and HSPC.CALENDAR_DATE < {?FiscalYearEnd} )
and   HSP.DEPARTMENT = '1001501001'   ---Hospice department   Jim Meng
and ((0 IN {?Service Area}) OR HSP.SERV_AREA_ID in {?Service Area})
group by
Case when FINANCIAL_CLASS_NAME = 'Medicare' Then 'Medicare'
     when FINANCIAL_CLASS_NAME = 'Medicaid' Then 'Medicaid'
      ELSE 'Other' End
,HSPC.LEVEL_OF_CARE
,HSPC.CALENDAR_DATE
,HSP.Serv_Area_ID
,LOC.LOC_ID
IP IP Logged
Jimm
Newbie
Newbie


Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
Quote Jimm Replybullet Posted: 05 Apr 2017 at 3:46am
Dell,

Any suggestion?

This report is yearly run report and should be scheduled.

how can I set up FiscalyearBegin and FiscalYearEnd? in sql or in crystal report?

Thanks.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 05 Apr 2017 at 4:15am
Will this always be the prior fiscal year based on the current date or could it be the current fiscal year based on the current date?

Also, what type of database are you connecting to? Oracle? SQL Server? Something else?

-Dell
IP IP Logged
Jimm
Newbie
Newbie


Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
Quote Jimm Replybullet Posted: 05 Apr 2017 at 5:31am
connect to SQL database server.

fiscal year may vary for each report.

Mine should be from '10/1/last year' to '9/30/next year'. total 12 months: 10/11/12 months from previous year, then include 01/02/03/04/05/06/07/08/09 months.

Not sure I answer your question.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 05 Apr 2017 at 6:08am
So this will always show the prior fiscal[/] year which actually contains months 10, 11, and 12 of the prior calendar year and months 1-9 of the current calendar year. Is that correct?

-Dell
IP IP Logged
Jimm
Newbie
Newbie


Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
Quote Jimm Replybullet Posted: 05 Apr 2017 at 6:14am
exactly
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.