moni81011
asked on
Crystal Reports 2008
I am trying to create a report using Crystal Reports 2008 and a stored procedure in SQL 2008.
For my end result, I want the loans grouped by:
1.Lien Position
a.1st
b.2nd
then by:
2.@Adjustable
a.Fixed Rate
b.Balloon/Hybrid
c.Adjustable Rate (<= 1yr)
d.Adjustable Rate(> 1yr)
Then by:
3.@Type
a.> 15yrs
b.<= 15yrs
c.> 5yrs
d.<= 5yrs
Part of the report should look like this:
# of loans Amount
First Mortgages
Fixed Rate
>15yrs 500 50,000,000
15 yrs or less 1,000 60,000,000
Balloon/Hybrid
>5yrs 10 500,000
5 yrs or less 50 1,000,000
Adjustable Rate
1yr or less 20 2,000,000
>1 yr 100 10,000,000
Other Real Estate (2nd liens)
Closed End Fixed 400 10,000,000
TOTAL 2,080 133,500,000
Here is the code that I have for @Adjustable:
iif({stored procedure;1.LienPosition}= "1",iif(is null({stor ed procedure;1.BalloonPayDate }),iif({st ored procedure;1.LoanPayType}=" F","Fixed Rate",
iif({@DaystoRateChange}=0, "Other",
iif({@DaystoRateChange}<=1 2,"Adjusta ble Rate 1 yr or less","Adjustable Rate > 1 yr"))),"Balloon/Hybrid"),
iif(isnull({stored procedure;1.FirstRateChang eDate}),"C losed-end Fixed Rate",""))
@DaystoRateChange code:
iif(isnull({stored procedure;1.FirstRateChang eDate}),0,
iif({stored procedure;1.FirstRateChang eDate}<=Cu rrentDate,
{stored procedure;1.MthsBetweenChg },datediff ("m",cdate ({stored procedure;1.OpenedDate}),
{stored procedure;1.FirstRateChang eDate})))
@Type code:
iif({@Adjustable}= "Fixed Rate",iif({@YrsToMaturity} >15,">15 Years","15 Years or less"),
iif({@Adjustable}="Balloon /Hybrid",i if({stored procedure;1.LienPosition}= "1","> 5 Years",
iif({@YrsToMaturity}>5,"> 5 Years","5 Years or less")),""))
The results I am getting are only two loans that fall in the fixed rate category, which is incorrect.
Majority of the loans should fall under 1st lien fixed rate.Then some fall under
Adjustable Rate > 1 yr and the rest fall under “Other” which is also not correct. I am inserting
groups for Lien position, Adjustable and Type and specify an order within the groups. Any help
would be greatly appreciated. I am not too familiar with Crystal Reports would appreciate
your help.
For my end result, I want the loans grouped by:
1.Lien Position
a.1st
b.2nd
then by:
2.@Adjustable
a.Fixed Rate
b.Balloon/Hybrid
c.Adjustable Rate (<= 1yr)
d.Adjustable Rate(> 1yr)
Then by:
3.@Type
a.> 15yrs
b.<= 15yrs
c.> 5yrs
d.<= 5yrs
Part of the report should look like this:
# of loans Amount
First Mortgages
Fixed Rate
>15yrs 500 50,000,000
15 yrs or less 1,000 60,000,000
Balloon/Hybrid
>5yrs 10 500,000
5 yrs or less 50 1,000,000
Adjustable Rate
1yr or less 20 2,000,000
>1 yr 100 10,000,000
Other Real Estate (2nd liens)
Closed End Fixed 400 10,000,000
TOTAL 2,080 133,500,000
Here is the code that I have for @Adjustable:
iif({stored procedure;1.LienPosition}=
iif({@DaystoRateChange}=0,
iif({@DaystoRateChange}<=1
iif(isnull({stored procedure;1.FirstRateChang
@DaystoRateChange code:
iif(isnull({stored procedure;1.FirstRateChang
iif({stored procedure;1.FirstRateChang
{stored procedure;1.MthsBetweenChg
{stored procedure;1.FirstRateChang
@Type code:
iif({@Adjustable}= "Fixed Rate",iif({@YrsToMaturity}
iif({@Adjustable}="Balloon
iif({@YrsToMaturity}>5,"> 5 Years","5 Years or less")),""))
The results I am getting are only two loans that fall in the fixed rate category, which is incorrect.
Majority of the loans should fall under 1st lien fixed rate.Then some fall under
Adjustable Rate > 1 yr and the rest fall under “Other” which is also not correct. I am inserting
groups for Lien position, Adjustable and Type and specify an order within the groups. Any help
would be greatly appreciated. I am not too familiar with Crystal Reports would appreciate
your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did not write the initial code and you are right, it is a bit hard to understand. I will try to use the IF THEN ELSE from now on.
Thank you soooooooooooooo much for your help!