Showing All Group Possibilities Regardless of Record Selection

[Nassy]

Hi,

I want to check whether there is a way in Crystal to do the following:

I have a report which splits employees into the number of months they have been in service at our company:

0-12 Months
1-2 Years
2-5 Years
5-10 Years
10 Years+

The 'Time in Service' Categories have to be created with a formula @categories as the categories do not exist in the data.

When I group by my formula I also apply a record selection to show just one years data. However, while I would like to see all the different group values (ie 0-12 Months, 1-2 Years etc) regardless of whether there are any employees or not, Crystal will only show the groups for which there is data.

So the question is - how do I change this?

Normally I know the answer would be to use a left outer join but I dont have that option. I then created a Categories Table with the same categories as in my @Categories formula but found that I could do nothing with it in Crystal as it doesn't seem to be possible to link to a formula field in Crystal.

I know that I could write the formula in SQL and add it as a column to my Employees table, but it does seem a lot of hassle (and my SQL is not as good as my Crystal formula writing skills).

Any Ideas?

Thanks

Nassy

 

[IdoMillet]

Remove the date condition from the Record seelction formula.

Show the summary counts of employees in a CrossTab.

Show the detail information for the last year by applying the date record selection condition (in reverse) to the section suppress attribute. This way all records would be retrieved, but only those that you want to see will be shown.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

My generic suggestion is to do away with this problem forevermore by creating a table on your database which contains all dates, along with the month number of each, and whatever other attirbutes you might find interesting (business day, etc.).

Now you can always use the date selection against this table, and left outer your table to it, resulting in rows for whatever periods you want.

-k

 

[Nassy]

Hi

Thanks. These suggestions have really helped.

IdoMillets suggestion has proved a good workaround but I think eventually I will need to create a table with dates in it.

Thanks guys!

Nassy

 

[Ngolem]

Also...you could collect all of the information using no date restriction in your record selection in the detail section....

BUT display the results as a summary in a Group footer or report footer. This way you can display all the groups whether there is data or not. No worries about join problems
If you need help on how to collect and display the data ....just ask

Jim Broadbent

 

[nicklp]

I'm a brand new user to Crystal Reports, and I'm having a very similar problem (well, exactly the same). Just hoping I can find a quick fix, your answers are very helpful.

I'm selecting records within Date Range and that meet the requirement of 14 or more items ordered. I want to group by Date, and show the number of orders placed for each date. Unfortunately, when grouped like this, the dates with no orders don't show at all, but I'd like the date to show with a count of 0. Any advice (overly simplified if possible) would be greatly appreciated.

Thanks,
Nick