R – Grouping and crystal reports

crystal-reports

I have a crystal report that is like this:

Group By Property.ID
  Group By RentalUnit.PropertyID
    Group By Tenant.PropertyID

That way it comes out looking something like this:

Property1
  unit1 data, tenant data
  unit2 data, tenant data
  ...
  unitN data, tenant data
Property2
  unit1 data, tenant data
  unit2 data, tenant data
  ...
  unitN data, tenant data
...
PropertyN
  unit1 data, tenant data
  unit2 data, tenant data
  ...
  unitN data, tenant data

The tables look like this:

Property:
  ID
  other data
RentalUnit
  ID
  PropertyId <-- This is the foreign key representing what property they belong to
  other data
Tenant
  ID
  PropertyID <-- foreign key to their property
  UnitNumber <-- foreign key to their unit
  other data

I want to change the report though. I'm passing it a variable call SelectedPropertyID, and I want to show only the rental units (and the tenant assigned to the rental unit) for the selected Property.

How can I do this? I'm rather new to crystal reports so please excuse my inexperience.

Best Answer

OK, I just refreshed my memory in Crystal Reports. I'm going by version XI R2, so things might be different in your version.

Here's what I did:

  1. Define a parameter for the Property ID. It's up to you to define it as either a free field or restricting its values to what's in the database. Whichever makes the most sense to your report.
  2. Open up the database expert. Go to the "links" tab and make sure you have links defined between these tables. If you don't, then define them (think of them as foreign keys). Close the database expert.
  3. Open up the select expert. Click "new" to define a new selection restriction. In the new window that pops up, drag your parameter and the Property ID field from the tblProperty table, with an equals sign between them. This is basically the report's WHERE clause. Your formula should look something like {?Parameter}={tblProperty.PropertyID}.

And that should do it.

Related Topic