In the past, I used a "worker table" to create specialized reports. It would have say, 20 text fields, (text01, etc.). I would write a script that would create a new record for each row in the report. Then populate each text field (column) with formatted data.
Here are a bunch of useful links to learn more about Cross Tab Reports
Other than the help already provided, there's not much more we can do without a more detailed understanding of your data model. The report format looks well suited for a cross tab report using one row portals, but just from this post, I have no idea what a "cohort" is nor how that data is stored and related to Orgs. I would guess that a "cohort" represents a group of Orgs but how they are grouped and what sort of relationship you have linking a table of Orgs with a table of cohorts is basic info we'd need to know in order to offer anything specific to your particular project.
I was asking for help creating a data model to create the CHURN report described in the original post because what I’m doing isn’t working. I didn’t go into this detail in the original post because I didn’t want to assume it’s a good starting point and wanted to think about it from a fresh perspective. But, this is what I’ve done so far.
Cohorts A::OrgsList = Orgs::OrgID
Cohorts A::OrgsList contains a list of OrgID’s created by doing a Find in Orgs to create a found set of Orgs at the beginning of each target year. The OrgID’s are stored in a field, Orgs::FieldList (Summary, List type). I copy this to Cohort A::OrgsList.
One record in Cohort A contains all of the OrgID’s for the Orgs that were customers at the start of each target year.
Using the same approach, I could (but haven’t yet) create another separate table (Cohort B) to create the cell values for Cohort B and Cohort B1 for a single year but I see two problems:
- How to combine the results of these two tables into a single report
- How to present more than one year - even using single cell portals. Since each record in Cohorts A references Orgs from a single year, a filtered portal won’t work.
I ‘m wondering if I’m approaching this all wrong. Actually, I’m assuming that I’m approaching it wrong. Hence, my post asking for ideas about the data model I should use to create the report.
Thanks, as always, for your help. Here’s a copy of the image in the original post for reference.
PastedGraphic-2.png 114.1 K
I am assuming that an Org and a customer are one and the same.
It looks like you can do this with three tables:
Each time you add a customer, you add a record in OrgEvent and enter data in a field to mark it as an "add". Each time a customer cancels, you add another record but put data in a different field to mark it as a cancellation.
This is then puts the data in a form that works for the cross tab report. Each pair of rows in your report represent a single record in your Cohort table. Filtered portals to OrgEvents provide your columns of data.
Thank you. I'll try that. I hadn't thought of using a join table.
Sent from my iPad
Thanks, David. I'll try this. Appreciate the reply.
Sent from my iPad