I have some data that shows the number of visits a person has done to all companies in our database. What I would like to do is show a report of all the visits and all the ones they haven't visited, grouped by month.
For example, assuming the entire database of companies is CompanyA, CompanyB, CompanyC and CompanyD and the visit data is as follows (created using an outer join so that all companies are included)
Company Name Visit Date
Company A 10/05/10
Company A 12/05/10
Company A 18/06/10
Company B 05/05/10
Company C NULL
Company D 21/04/10
Company D 14/05/10
Company D 02/06/10
The report I would like to see is
Month - April
CompanyA - NULL
CompanyB - NULL
CompanyC - NULL
CompanyD - 21/04/10
Month - May
CompanyA - 10/05/10
CompanyA - 12/05/10
CompanyB - 05/05/10
CompanyC - NULL
CompanyD - 14/05/10
Month - June
CompanyA - 18/06/10
CompanyB - NULL
CompanyC - NULL
CompanyD - 02/06/10
I can obviously group by month of date but this won't include the companies with no visit date for that month which I need. Is this kind of report possible in crystal reports?
Try making an "index" table of the month & company (I don't know if that is the right term for this. That's just what I call it). Make right joins from your index table to the actual data.
Your index table will look like:
Month Company
2010-01 Company A
2010-01 Company B
2010-01 Company C
2010-01 Company D
2010-02 Company A
2010-02 Company B
2010-02 Company C
2010-02 Company D
2010-03 Company A
...
Edit
Since it's a right join, you'll see all 4 companies each month whether or not they have data. Also, since you're hardcoding the months to the table, you'll have to update your index table periodically, but thanks to the wonders of copy,paste, and backspace, you should only have to do this every few centuries.