I am trying to create a report that looks like the mockup below. The source table is ORGS. I can create this report manually, one year at a time using multiple Finds combined with Finds + Omits - using the Date Ordered and Date Canceled fields below but I would like to automate this if possible.
- Orgs:: Org_ID (unique)
- Orgs::Date Ordered
- Orgs::Date Canceled (empty if not canceled)
I can also create parts of this report using a multi-predicate relationship between another table (COHORTS) and Orgs.
I'm familiar with using filtered portals to create cross tab reports similar to the mockup below but have not been able to see how to do this using a separate related table or a self join in Orgs.
I have very little experience with SQL and have not tried that approach.
Any help or ideas would be greatly appreciated.