Need help with date selection criteria on Fiscal year,July month

[LoriDurham]

Hello all, I am new to Crystal and I have a report where it looks for Fiscal year and Fiscal month

NO matter when I run the report Report should start with July

Suppose I run the report on July09 I need to get data only for July 09 and if report is scheduled in August then I need to get data for July09 and Aug 09 and If the report is run in Sept I need to get data for July Aug Sept, it should be a incremental process. And when we move to next year july 2010 and new fiscal year starts, so again Data should show up starting from July 2010. June is the end of fiscal year, I have field amount where I sum up for each month so when I run report for june 09 I need have that amount as O if its not zero then when I run the report In July2010 I need to get data from July2010 to July2009.I really need your help on this friends, thanks so much for time and this forums seems to be great.

Regards,
Lori.

 

[LoriDurham]

Small correction if the total amount > o in June 2010 then we need to pull report from July2010 to july 2009. Suppose last month july 2009 report, if i had amount > 0 by the end of june 2009 then i had to show data from july2009 to july 2008 else if the amount = 0 then I need to show july 2009 data. This report will be scheduled on 5th of everymonth, and when it runs each month it has to increment the data for each month.

Thanks again,
Lori

 

[LoriDurham]

TO keep it more simple it will increment if june 09 amount is = 0 and go back to previous july if amount in june 09 is > 0.

 

[lbass]

Try a record selection formula like this:

{Table.date} in date({?fiscalyear}-2,7,1) to lastfullmonth

This will return the current and last fiscal year. Then
create a formula {@June}:

if {Table.date} in date({?fiscalyear}-1,6,1) to date({?fiscalyear},6,30) then {table.amt}

Then go to report->selection formula->GROUP and enter:

(
sum({@June}) = 0 and
{Table.date} in date({?fiscalyear}-1,7,1) to lastfullmonth
) or
sum({@June}) <> 0

This assumes you are testing values only for the month of June--not sure whether you are really evaluating a cumulative total across all months of the previous year. Also don't know whether you are working with date fields or not.

-LB

 

[LoriDurham]

LB thanks for the response I have come up with the criteria in where clasue in command statement
like below
WHERE DATE(date field) between date( '07' || '-' || '01' || '-' || CHAR(YEAR(current date )))
and (((DATE(current date) - Day(DATE(current date)) day)+1 day)) - 1 day

Thought this will be much faster. This is a scheduled report so it cannot have parameters, But if there is not other way then I can include Fiscal year as paramter. Other option I am thinking is using subreport for conditon amt>0 by the end of june?. What do you think?. I really appreciate your help.

Regards,
Lori

 

[LoriDurham]

LB WHEN i use
{Table.date} in date({?fiscalyear}-2,7,1) to lastfullmonth I get error showing a datetime is required here on lastfull month

 

[lbass]

Sorry, that should be:

{Table.date} in date({?fiscalyear}-2,7,1) to maximum(lastfullmonth)

-LB

 

[LoriDurham]

Thanks LB, I will try your logic and will let you know. You been a great help
Lori

 

[LoriDurham]

I am getting data from July 09 to July 07 with the above selection criteria, I will example with data which might be helpful
If i run the report today I get data
Amt People
July 09 2000 12
If I run in Sept
Amt People
July 09 2000 12
Aug 09 350 2
so on If I run report 2010July below will be data

Amt People
July 09 2000 12
Aug 09 350 2
.
.
.
June2010 0 0
In above data there is no activity in June2010(end of fiscal year month) 0$ and 0 applicants. So when I run the report in August2010
I should get data like(again startting from July)
Amt People
July 2010 760 3

But in case if the report ran in July2010 was like below

Amt People
July 09 2000 12
Aug 09 350 2
.
.
.
June2010 652 4

Since there was an activity in end of fiscal year month I want to show data for August2010 as

Amt People
July 2010 760 3
June 2010 652 4
.
.
.
July2009 2000 12

I hope the above example is clear, any ideas how to get this?
Thanks so much,
Lori

 

[LoriDurham]

Any ideas friends?

 

[lbass]

I can't tell how you used my formulas, or what you used for the parameter. I'm also unclear on how you are identifying your fiscal months. If you are running a report for FY 2010 (July 2009 to June 2010) in August of 2009, don't you identify the month as August 2009?

For more help, please spell out exactly how you implemented my suggestion. I can't tell whether you followed through with each step.

-LB

 

[LoriDurham]

LB sorry if I was not clear
When is use your selection criteria
DATE({Command.APPDATE}) in date({?Year}-2,7,1) to maximum(lastfullmonth)
I did group on APPDATE on report, I get data from july09 to july 07.

Also your right when I run a report for FY 2010 (July 2009 to June 2010) in August of 2009 I should identify August 2009. I should get data in months incremental manner so when I run report in Aug I get data for July month, In sept I get data for both July and Aug and so on upto June. SO I run report till July of nexy year to get june data. After that when I run report again New fiscal year cycle should start ie from July. But When I run report for July and I get June data If i had amount> 0 then when I run report in August instead of starting new cycle from July I should get preevious year fical data too since june amount>0.
Please let me know if this is clear, or else I will come up with more examples. Thanks so much for taking time to help me.

Lori

 

[lbass]

I understand what you are trying to do, but you still haven't explained how you implemented my suggestion. If you entered 2010 as the {?fiscalyear} in:

DATE({Command.APPDATE}) in date({?FiscalYear}-2,7,1) to maximum(lastfullmonth)

...you would get initial data from 7/1/2008 through July 2009. This is only the first step. Then you would have to follow the remaining steps I suggested.

-LB

 

[LoriDurham]

I will try them again now LB, thanks so much again. I will let you know.