kelsanit
asked on
Crystal Report formula help to define current fiscal month and year
I have two tables "Orderlinemaster" & "Fiscal periods". They are joind by two field called fiscalyear and fiscalmonth. The fiscal periods table has the following fields (Month, year, fiscalmonth, fiscalyear, begindate, enddate, shippingdays)
My question is this - based on the "Currentdate" I need a formula that will display what the current fiscalmonth is.
For example: if today is 2/25/2009 the it would display fiscalyear = 2009 fiscalmonth = 12
The fields in the fiscalperiods talbe are defined below to help out:
Month: Calendar month of fiscal month
Year: calendar year of fiscal month
Fiscalmonth: fiscal month number
Fiscalyear: fiscal year number
Begindate: start date of fiscal month
enddate: enddate of fiscal month
shippingdays: number of shipping days in that fiscal month
Example to go along with above date sample output.
Month: 3
Year: 2009
Fiscalmonth: 12
Fiscalyear: 2009
Begindate: 2/22/2009
enddate: 3/28/2009
shippingdays: 25
My question is this - based on the "Currentdate" I need a formula that will display what the current fiscalmonth is.
For example: if today is 2/25/2009 the it would display fiscalyear = 2009 fiscalmonth = 12
The fields in the fiscalperiods talbe are defined below to help out:
Month: Calendar month of fiscal month
Year: calendar year of fiscal month
Fiscalmonth: fiscal month number
Fiscalyear: fiscal year number
Begindate: start date of fiscal month
enddate: enddate of fiscal month
shippingdays: number of shipping days in that fiscal month
Example to go along with above date sample output.
Month: 3
Year: 2009
Fiscalmonth: 12
Fiscalyear: 2009
Begindate: 2/22/2009
enddate: 3/28/2009
shippingdays: 25
Where do you want to display this? Is it just something that you want to show in the page header, or do you want to somehow identify the details that are in the current period, or ... ?
James
James
ASKER
Pete,
Yes, I have tried creating a sub report which I can easily get the current fiscal month and year and pass to my main report. However, I am unable to use those shared var. in the running totals. For example, I have two running totals that sum(netamount) and both with evaluate formula that needs to says the following:
FY2009 SUM: Where fiscalmonth = currentfymth (shardvar) and fiscalyear = currentfyyear (sharedvar)
FY2008 SUM: Where fiscalmonth = currentfymth (sharedvar and fiscalyear = currentfyyear -1
Yes, I have tried creating a sub report which I can easily get the current fiscal month and year and pass to my main report. However, I am unable to use those shared var. in the running totals. For example, I have two running totals that sum(netamount) and both with evaluate formula that needs to says the following:
FY2009 SUM: Where fiscalmonth = currentfymth (shardvar) and fiscalyear = currentfyyear (sharedvar)
FY2008 SUM: Where fiscalmonth = currentfymth (sharedvar and fiscalyear = currentfyyear -1
ASKER
I figured it out...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should really post your answer for the rest of us.
You would need a subreport based just on the fiscal periods table which identified the current periods details and stored then in some shared variables. This subreport would go in the main report header.
The main report can then access the shared variables containing the current period details.