Quarterly and YTD Percentage Subreport

[kwoo7706]

I’m looking for assistance with creating a subreport to display a percentage of loans that have a value in a date field compared to loans that do not have a value in the same date field. This would be a quarterly percentage and a YTD percentage. Any thoughts on how to do this?

 

[lbass]

This is a little vague. Why do you need a subreport to do this? Is the issue that you want to bring in larger time frames? You mention testing for whether a date has a value--do you mean whether it has a date in the specific quarter or YTD? What problems are you running into when trying to do this?

What is the structure of the main report? What fields are you grouping on? In what report section do you need the subreport results?

-LB

 

[kwoo7706]

Main Report

Selection Criteria = {ADDITIONAL_LOAN_DATA.ApplicationCreationDateTimeInAvista} in {?Start Date} to {?End Date} and {PROPERTY._StreetAddress} like ["", "*Approval*"]

Group Header #1 = {ACCOUNT_INFO.BrokerFirstName}
Group Header #2 = {LOAN_GENERAL.LenderRegistrationIdentifier}

Group Footer #1: Distict Count of {LOAN_GENERAL.LenderRegistrationIdentifier} Group Footer #1: Running Total for the Sum {MORTGAGE_TERMS.BaseLoanAmount} and Evaluates on change of Group #2 {LOAN_GENERAL.LenderRegistrationIdentifier} and Resets on change of Group #1 {ACCOUNT_INFO.BrokerFirstName})

Report Footer a:
Running Total for the Count of {LOAN_GENERAL.LenderRegistrationIdentifier} and Evaluates on change of Group #2 {LOAN_GENERAL.LenderRegistrationIdentifier} and never resets.
Running Total for the Sum of {MORTGAGE_TERMS.BaseLoanAmount} and Evaluates on the change of Group #2 {LOAN_GENERAL.LenderRegistrationIdentifier} and never resets.

Looking to incorporate a Quarterly and YTD percentage of loans displayed on the main report that have a “Pre-Approval Flip Date” which is a custom user defined field in the database. I’ve created the formula: If {CUSTOM_FIELD.AttributeUniqueName} = "Pre-Approval Flip Date" then {@DateValue}

{@DateValue} = DateValue({CUSTOM_FIELD.AttributeValue})

The only part of the selection criteria on the main report that would not apply to the Quarterly and YTD percentage is {PROPERTY._StreetAddress} like ["", "*Approval*"]

Does this help? Thanks for your time!

 

[lbass]

You didn't answer a number of my questions. Please take another look.

Also, what would be the likely range of the start to end date parameters? How do the Quarterly and YTD percentages relate to this? Is the Quarterly for the entire YTD or what?

-LB

 

[kwoo7706]

Okay I hope this helps you.

Why do you need a subreport to do this? I’m thinking that I need one because the parameters to run the subreport will be different then the main report. I’m thinking I may need two subreports, one for quarterly and one for YTD.

Is the issue that you want to bring in larger time frames? Yes, quarterly figures and YTD figures. The main report parameters are monthly.

You mention testing for whether a date has a value--do you mean whether it has a date in the specific quarter or YTD? Yes, whether it has {@PA Flip Date} in the specific quarter and YTD. {@PA Flip Date} = If {CUSTOM_FIELD.AttributeUniqueName} = "Pre-Approval Flip Date" then {@DateValue}

{@DateValue} = DateValue({CUSTOM_FIELD.AttributeValue})

What problems are you running into when trying to do this?

Subreport for Quarterly

Selection Criteria: Year({@PA Flip Date}) = Year({?Pm-@End Date}) and
Year({ADDITIONAL_LOAN_DATA.ApplicationCreationDateTimeInAvista}) = Year({?Pm-@End Date})

Group Header #1: ({@PA Flip Date} in descending order for each quarter

In what report section do you need the subreport results? Report Footer

 

[kwoo7706]

Okay I've got my Quarterly subreport working - so I'm good there. I have the report grouped by {@PA Flip Date} in descending order for each quarter and each quarter displays a date of 4/11, 7/11, 10/11 - how can I change the display to "Quarter 2", "Quarter 3", "Quarter 4" for the group.

 

[lbass]

But what are you having trouble with?

I am also confused by the fact that you have a monthly report for the main report, and different time frames for the quarterly and YTD, and yet you want to do a percentage calculation comparing these. So are you trying to determine what the month's contribution is to the current quarter only? And the YTD?

You should insert additional RF sections and then in the RF_a, add a formula that allows you to pass the count from the main report to the subreports, like this:

whileprintingrecords;
shared numbervar gt := {#LoanCount}; //don't know your name for it

Then in RH_b add the YTD subreport, and in the report footer, add a formula like this:

whileprintingrecords;
shared numbervar gt;
distinctcount({table.loanID})%gt

Just not sure of what the comparison is for the Quarterly.

-LB

 

[kwoo7706]

LB said, "But what are you having trouble with?"

I don't know how to obtain the # of loans with a {@PA Flip Date} on the subreport compared to # of loans without a {@PA Flip Date} from the main report displayed in a Quarterly %. I also don't know how to do this for YTD.

LB said, "So are you trying to determine what the month's contribution is to the current quarter only? And the YTD?"

Quarterly Subreport: The month end # of loans on the main report compared to the quarterly total # of loans with a {@PA Flip Date} displayed in a %.

YTD Subreport: The month end # of loans on the main report compared to the YTD total # of loans with a {@PA Flip Date} displayed in a % (this would be cummunlative).

Sorry for the rough explanations - I am a novice with Crystal Reports and find explaining through text difficult. I appreciate your time!

 

[lbass]

Are you always running the main report for one month only?

Is the quarterly sub just for the quarter containing the month in the main report?

-LB

 

[kwoo7706]

Are you always running the main report for one month only? YES

Is the quarterly sub just for the quarter containing the month in the main report? YES

 

[kwoo7706]

The main report has date parameters starting from 1/1/2011 to an end date (month end date).

 

[lbass]

Sorry--still not clear. On the main report, do some loans have a flip date and some don't? Your stated comparisons sometimes seem to want a comparison of loans with flip dates from the subreports with all loans on the main report, but once you said you wanted to compare loans on the main report that DON'T have flip dates with loans on the subs that DO. Which is it?

It looks like the subs ONLY contain loans with flip dates, but that the main report contains both some with and some without flip dates?

-LB

 

[kwoo7706]

Okay, I apologize. I have the subreport created and grouped by {@Quarterly}:

if datepart("q",{ADDITIONAL_LOAN_DATA.ApplicationCreationDateTimeInAvista}) = 1 then "1st Qtr"
else
if datepart("q",{ADDITIONAL_LOAN_DATA.ApplicationCreationDateTimeInAvista}) = 2 then "2nd Qtr"
else
if datepart("q",{ADDITIONAL_LOAN_DATA.ApplicationCreationDateTimeInAvista}) = 3 then "3rd Qtr"
else
if datepart("q",{ADDITIONAL_LOAN_DATA.ApplicationCreationDateTimeInAvista}) = 4 then "4th Qtr"

For this group, I've inserted Count ({LOAN_GENERAL.LenderRegistrationIdentifier}, {@Quarterly}) to give me the # of loans for each quarter.

I've also created a formula PercentOfCount ({LOAN_GENERAL.LenderRegistrationIdentifier}, {@Quarterly}) to give me the % of loans for each quarter.

I know need to determine what percentage of loans that have a "flipped date" for each quarter which is represented by the formula: If {CUSTOM_FIELD.AttributeUniqueName} = "Pre-Approval Flip Date" then {@DateValue}

{@DateValue} = DateValue({CUSTOM_FIELD.AttributeValue})

How can I do this?

 

[lbass]

Create another formula:

//{@flipdatecnt}:
If {CUSTOM_FIELD.AttributeUniqueName} = "Pre-Approval Flip Date" then 1

Then your percentage is:

sum({@flipdatecnt},{@Quarterly})% count({LOAN_GENERAL.LenderRegistrationIdentifier})

If you needed a distinctcount of loans that are flipped, you would need to approach this a little differently.

-LB