about "lastyearytd"

[raghu55]

hello all,
iam working on crystalreports 9,oracle 10g.

i have to get lastyear total amount i.e from jan-1-2005 to
dec-31-2005, if when i run the report on jan-02-2006.

i have given formula as below,

if
(
{DISBURSEMENTS.ACTUAL_DISB_DATE} in YearToDate
and {DISBURSEMENTS.ACTUAL_DISB_DATE} <= maximum(lastfullmonth)
)
then {DISBURSEMENTS.DISB_AMOUNT}

or
(
{DISBURSEMENTS.ACTUAL_DISB_DATE}>= lastyearytd
and {DISBURSEMENTS.ACTUAL_DISB_DATE} <= maximum(lastfullmonth)
)
then {DISBURSEMENTS.DISB_AMOUNT}

its giveing error in formula.
i have to run this report every month.

help requested..

thank you.

 

[lbass]

When you run the report in Feb will you still be looking for the 2005 calendar year? Or for Feb. 1 2005 to Jan 31 2006?

-LB

 

[raghu55]

no,
when i run the report in feb-1-2006 , it should give total amount for the months jan-1-2006 to feb-31-2006 .

 

[raghu55]

sorry, there is no feb -31,

that should be feb_28(i.e end of feb month)

 

[lbass]

You are saying inconsistent things. If you wanted current year to date, you would use:

{table.date} in yeartodate

If run today, that would be Jan. 1, 2006 to April 13, 2006. If you wrote a formula:

{table.date} in lastyearYTD

...if run today, the result would be Jan. 1, 2005 to April 13, 2005.

If you want all of last year, then you need to distinguish whether you mean all of the last calendar year, or whether you mean a year's worth of data, ending on the last date of the last full month, e.g., if run now, Apr. 1, 2005, to March 31, 2006, or what?

Please clarify and give several examples of what you want the formula to do.

-LB

 

[raghu55]

please see this
I am using crystal 9.

I have problem with formula. See the above selection criteria.

I need total disb_amt for the months jan,feb
when i run the report in the march.

i need total disb_amt for the last year from jan to dec
when i run the report on jan 2 of currunt year.

i have parameters startdate,end date.

selection condition for this.
sum if loan_type in (sf,su,cl,pl) for current ytd ending
on to-date.

i was created fromula as

if loan_type in["sf","su","cl","pl"] and

(if month(currentdate) <> 1 then
(

{LOAN.GUARANTEE_DATE} >= minimum(yeartodate)
and
{LOAN.GUARANTEE_DATE} <= maximum(lastfullmonth)
)
else
(
{LOAN.GUARANTEE_DATE} >= dateserial(year(currentdate)-1,1,1)
and
{LOAN.GUARANTEE_DATE} <= maximum(lastfullmonth)))

then
disb_amt

i place this formula field in running total as summmury field evalute for each record reset for each group.

its giveing disb_amt for jan month, but its not working for feb month.

its not working

 

[lbass]

You say it is not working for February--what do you mean by that? Is it providing the wrong figures? Do they look inflated? Or is the result null? Or are you getting somekind of error message?

-LB

 

[lbass]

I just tested your formula, using it in a running total, and it seems to work fine. I wonder if your record selection formula might be limiting your results. Please share your selection formula (report->selection formula->RECORD).

Also, note that if you are suppressing any records, you would have to take that into account in the evaluation section of the running total.

-LB

 

[raghu55]

simple way,

how i get the total lastyear disb_amt from jan to dec ending when i run the report in currentyear jan month.

what formula should i use in selection formula to get this.

selection condition for this.
sum if loan_type in (sf,su,cl,pl) for current ytd ending
on to-date.



loan_type-code,disb_amt ,gurr-date are the database fields, i have parameters startdate, enddate.

thank you.

 

[lbass]

I don't think you really need parameters for this, if you are looking for year to date information. You could just use the same criteria as in your running total:

loan_type in["sf","su","cl","pl"] and

(
if month(currentdate) <> 1 then
(
{LOAN.GUARANTEE_DATE} >= minimum(yeartodate)
and
{LOAN.GUARANTEE_DATE} <= maximum(lastfullmonth)
)
else
if month(currentdate) = 1 then
(
{LOAN.GUARANTEE_DATE} >= date(year(currentdate)-1,1,1)
and
{LOAN.GUARANTEE_DATE} <= maximum(lastfullmonth)
)
)

If you also want to work with records up to the currentdate, then change "maximum(lastfullmonth)" in each clause to "currentdate".

-LB

 

[raghu55]

do i put this formula in selection formula
to get the last calenderyear reports .

 

[raghu55]

above formula giveing error message , when i was put running total "the remaing text does not appear to be a part of formula

 

[lbass]

My last post was the record selection formula. I see no reason for an error message. Check the parens to make sure you have entered them appropriately.

This formula will allow you get the last calendar year if it's January, or the year to date through the last full month, if it is not January.

You should be able to then just insert a summary (sum) on your amount field without a running total.

-LB

 

[raghu55]

but, when i use this formula in selection formula
its giveing below sql querry.

SELECT

"LOAN"."LOAN_TYPE_CODE",
"DISBURSEMENTS"."DISB_AMOUNT",
"DOE_INSTITUTION_1"."INSTITUTION_ID",
"DOE_INSTITUTION_1"."NAME",
"LOAN"."GUARANTEE_DATE"

FROM
"EPIC_USER"."LOAN" "LOAN",
"EPIC_USER"."DISBURSEMENTS" "DISBURSEMENTS",
"EPIC_USER"."LENDER" "LENDER",
"EPIC_USER"."DOE_INSTITUTION" "DOE_INSTITUTION_1"

WHERE
("LOAN"."LOAN_SK"="DISBURSEMENTS"."LOAN_SK")
AND
("LOAN"."LENDER_SK"="LENDER"."LENDER_SK")
AND
("LENDER"."DOE_SK"="DOE_INSTITUTION_1"."DOE_SK")
AND
("LOAN"."GUARANTEE_DATE">={ts '2006-01-01 00:00:00'}
AND
"LOAN"."GUARANTEE_DATE"<{ts '2006-04-01 00:00:00'})

ORDER BY
"DOE_INSTITUTION_1"."INSTITUTION_ID"

the guee_date not in lastyear.

 

[raghu55]

but i need lastyear records also.

 

[raghu55]

user wants to select data from parameters. so these are must.

 

[lbass]

Please spend some time explaining the exact data you want to report on. Please explain how the parameter is meant to be used.

Your earlier posts made it sound like your report would only be using the data depending upon the currentdate. Now it sounds like you want to show previous periods depending upon a parameter. You cannot use lastfullmonth or yeartodate functions with a parameter date--they always refer to the currentdate.

It would probably help if you showed some sample data, with column labels.

-LB