Record Selection Formula 1

[khan82]

I am designing a report that shows rental charges for any given month. The effective charge date of the charges should always be less than or equal to the effective date entered under the parameter by the user.

The table is PML_LEASE__LEASE_RECURRING_CHG_LOG

This table contains the record "Charge_Type"
This table also contains the record "effective charge date"
This table also contains the record "amount" associated with a particular charge type and effective date

for e.g. if we have two charge types Rent & Parking
Rent
Effective date: 01/01/2013 $1,000
11/01/2013 $1,500
01/01/2014 $1,700

Parking
Effective date: 01/01/2013 $20
10/01/2013 $50
12/01/2013 $80

Right now i have the following:

Record Selection formula.
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} <= {?Effective Date}

Group Selection formula.
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date}=maximum({PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date},{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type})

How can i design the select formula to print a charge which has a date of 11/01/2013 and also print the parking charge that fall in that date range? I want to group the report by leases and then by charge type. Will it still work?

Cheers

 

[pmax9999]

I think you are on the right path.

Using the Record Selection and Group Selection you posted, I don't see why you wouldn't get what you want if you:
[ul]
[li]Insert a Group on the Lease ID (as Grp 1)[/li]
[li]Insert a Group on {PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} (as Grp 2)[/li]
[li]Sort the records in ascending order based on the {PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date}[/li]
[li]Move the {PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} and {PML_LEASE__LEASE_RECURRING_CHG_LOG.Amount} fields to GF2[/li]
[/ul]
does that give you what you need. Maybe I have misunderstood the question.

Cheers
Pete

 

[khan82]

Sorry Pete....maybe i didn't explain it better.....how will the report only pull leases that will have a rent start date = date entered by user(this condition should only be for the table.charge_type = "rent"

it should pull rest of the charges on the same table as long as their effective date is <= date entered by user.

Thanks

 

[pmax9999]

So, based on the sample data from your first post, what results would you expect to see?

Pete

 

[khan82]

so if a user was to enter 11/01/2013 as the date on the prompt, he would see:

Rent $1,500
Parking $50

 

[pmax9999]

OK, so what result are you getting?

 

[khan82]

It gives me all the leases that have any charges with an effective date <= 11/01/2013

I want it to show me charges for only the leases that have a charge type "Rent" with an effective date of 11/01/2013. But to also show other charge types e.g. "Parking" so long as their rent effective date is <= 11/01/2013.

 

[pmax9999]

To be honest, this doesn't make sense to me because you would need to run the report with the parameter set to the exact date that the rent changed for it to appear.

Anyway, I think this Record Selection formula will work:

[Code RecordSelectionFormula]
(
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} = 'Rent' and
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} = {?Effective Date}
)
or
(
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} <> 'Rent' and
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} <= {?Effective Date}
)
[/Code]

Does this give you what you need? If not, please explain how the results differ from what you want/

Cheers
Pete

 

[khan82]

It almost gave me what i needed..

For leases with charge type "Rent" effective as of 11/01/2013 it gave me the charge type rent effective effective 11/01/2013 and also any other charge types associated with the lease which were effective less than 11/01/2013(Correct solution)

But it also....

For leases with charge type "Rent" not effective as of 11/01/2013 it gave me all other charge type associated with the lease except the Charge type rent.(this result needs to be eliminated)

 

[pmax9999]

OK, I think I finally understand. This would be a bit simpler if there were always only 2 possible Charge Types ('CAM' or 'Rent') but I have taken the approach there could be others, or at least there may in the future be others. Do the following:

1. Create the following Formula (called "RentID"):

If	{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} = 'Rent' 
Then	1
Else 	0

2. Amend the Group Selection Formula as follows:

Maximum({@RentID}, {Table.Lease_ID}) = 1 and
Maximum({PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date},{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type}) = {PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date}

If there is no record with a Charge Type of 'Rent' for the Lease, it will not be included on the report.


Hope this helps.

Cheers
Pete

 

[khan82]

it works! i am going to run some more tests to confirm...it took 30 min to run but maybe thats because it had to sift through all the records...

my record selection formula is:

{PMN_TENANT__TENANT_ADDRESS.Tenant_Address_Type} = "Street" and
{PML_LEASE__LEASE.Status} = "Current" and
(
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} = 'Rent' and
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} = {?Effective Date}
)
or
(
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} <> 'Rent' and
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} <= {?Effective Date}
)

 

[pmax9999]

That does sound like a long time, but it depends on the number of records, ie the size of the database.

One of the problems is that the database will return all records the based on the he Record Selection, and it is then up to Crystal to filter out those that do not meet the Group Selection criteria. It is generally preferable to have the Database Server do as much of the processing as possible, but isn't always simple to achieve. It is usually possible to improve the efficiency of these types of reports using Database Views or Stored Procedures, or more complex Crystal Reports functionality such as Commands and SQL Expressions, but is difficult to achieve via these forums.

But looking at the entire record selection, I would suggest one further change, just to be certain that the And and Or are processed I the right order.

{PMN_TENANT__TENANT_ADDRESS.Tenant_Address_Type} = "Street" and
{PML_LEASE__LEASE.Status} = "Current" and
(
	(
		{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} = 'Rent' and
		{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} = {?Effective Date}
	)
	or
	(
		{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} <> 'Rent' and
		{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} <= {?Effective Date}
	) 
)

The format (with indentations etc) is ignored by Crystal but I find it makes it simpler to see the order/grouping, particularly when mixing And with Or.

Anyway, glad it has helped. Let us know if you find any issues with the data returned by the report (after changing the Record Selection as suggested).


Cheers
Pete