Crystal Reports - Include null values in all report groups
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 &nb开发者_运维知识库sp; 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/10The report I would like to see is
Month - April
CompanyA - NULL CompanyB - NULL CompanyC - NULL CompanyD - 21/04/10Month - May
CompanyA - 10/05/10 CompanyA - 12/05/10 CompanyB - 05/05/10 CompanyC - NULL CompanyD - 14/05/10Month - June
CompanyA - 18/06/10 CompanyB - NULL CompanyC - NULL CompanyD - 02/06/10I 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.
精彩评论