Displaying non-matching records 1

[JCruz063]

Hi All,

I'm working with Crystal Reports 10, Developer Edition on a machine with Windows 2000. The database I'm using is MySQL Server. Here's my situation...

The Data
I have a database with two tables: user and program. The user table simply contains a list of users. The program table contains a list of programs created by each user. Users are expected to complete a program every day and thus, the program table contains a date field specifying when the program was created.

The Report
I have a report that prints yes or no for every day of the month for each user, telling whether or not the user completed the expected program in each day. Thus, each page of my report shows a given month, a list of users, and a whole bunch of boxes (more precisely, 1 for each day of the month). The boxes show an 'x' if the there is a program in the database for that user in that day. Make sense? (btw, the boxes are not really boxes; they're just formulas with borders)

The Problem
Some users have no programs at all for certain months, and therefore, they don't appear in the report for those months. That's exactly the problem. I need the entire list of users to print for each month, whether or not programs exist in the database for them. Currently, I'm grouping the records by month (group 1) and by user (group 2). How can I force the entire list of users to print for each month?

I'm aware that it makes sense for the users not to show up if they don't exist in a given month. That's obvious. I'm looking for some trick or workaround that's going to force the records to show up. Any ideas?

Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[synapsevampire]

The big kid method, such as in data warehouses is to use a period table, check my FAQ:

faq767-4532

I wrote it based on SQL Server, but you should be able to convert it to MySQL without a great deal of effort.

Here's a Crystal whitepaper for doing it within Crystal:

http://support.businessobjects.com/library/kbase/articles/c2008492.asp

Here's their calendar whotepaper which one might adapt:

http://support.businessobjects.com/library/kbase/articles/c2011476.asp

If you search here you'll find lots of threads on this topic, but I urge you to take a long term approach and create a period table.

-k

 

[JCruz063]

-k,
Thanks for your reply!

I have all the dates. What I need is the users. But then again, I have all the users also. I'm retrieving the records from the database using a Command in Crystal Reports which implements a left join between user and program. All users are retrieved, so I always see all of them. What I don't see is how to make all users print for the months where they have no programs.

Thanks.

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[synapsevampire]

Left outer join the users to your program table, that might do it depending upon other filtering criteria.

If not, left outer the period table to the program to the user.

-k

 

[lbass]

You could try something like the following:

Use the table containing all dates in a main report, where you group on month. Then add an unlinked subreport to group header_b which uses the user table with a left join to the program table. Do not make any selections on the program table. Instead create a shared variable in the main report and be sure to place it in group header_a of the main report:

whileprintingrecords;
shared numbervar monthx := month({table.date});

Then in the subreport, go to the section expert->details->suppress and enter:
whileprintingrecords;
shared numbervar monthx;
month({table.date}) <> monthx;

You should now see all names and be able to add a formula that displays "yes" or "no" per day.

This method will be slow, but I think it will do what you want.

-LB

 

[JCruz063]

Guys... A million thanks for your replies! (sorry for my late response).

LB, A subreport did it. Thanks again.

JC

_________________________________________________
To get the best response to a question, read faq222-2244.