开发者

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/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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜