creating empty groups? 
Author Message
 creating empty groups?

I can't figure out how to do the following:

I am calculating counts of phone calls for each month. If I group by
month and calculate counts for the groups, I only get those months that
have at least one call. However, I would like to display zeros for the
missing months again. Can you create empty groups for the missing months
in Crystal (and/or SQL?)  Or is there a better solution for this
problem?

In other words, currently I am getting something like this:

March   24
May      41
June      12
...

Instead, I want to get

January    0
February  0
March      24
April       0
May         41
June         12
....

Any suggestions? CR version 8.0, database is MS Access in case if it
matters.

Thanks in advance!



Sat, 01 May 2004 05:35:01 GMT  
 creating empty groups?
Hi,

I am using CR7, so there may be some differences.  In any case, Crystal
can't group on values that don't exist in the data table.  If there are no
records for February, Crystal doesn't even know that February is a possible
value for the Month field.  If the table is empty, it wouldn't know about
any of the months, but you would presumably still want a report with the
months listed and 0s.

What I believe you have to do is to do your own counting for the 12 months
you know could exist.  You could do this by creating 12 formula fields along
the lines:

Global NumberVar Jan;
If {MyTable.Month} = "January" Then Jan := Jan + 1;
Jan

These formula fields would accumulate the numbers of records for each month,
with 0 if there were no records for a given month.   You could the use these
formula fields in the report footer opposite text objects that give the name
of the corresponding month.

Regards,

John..............

Quote:

> I can't figure out how to do the following:

> I am calculating counts of phone calls for each month. If I group by
> month and calculate counts for the groups, I only get those months that
> have at least one call. However, I would like to display zeros for the
> missing months again. Can you create empty groups for the missing months
> in Crystal (and/or SQL?)  Or is there a better solution for this
> problem?

> In other words, currently I am getting something like this:

> March   24
> May      41
> June      12
> ...

> Instead, I want to get

> January    0
> February  0
> March      24
> April       0
> May         41
> June         12
> ....

> Any suggestions? CR version 8.0, database is MS Access in case if it
> matters.

> Thanks in advance!



Sat, 01 May 2004 09:58:40 GMT  
 creating empty groups?
I've done reports like this by writing a "Driver" report that uses a table that
I know wil have records from every month (actually in my case, every day), then
using that to trigger a subreport in the group footer that does the totals for
each period.


Sat, 01 May 2004 23:02:23 GMT  
 creating empty groups?
John,

That makes sense. I though that the formulas might be the solution, but I
couldn't quite figure it out. Thanks!

Al

Quote:

> Hi,

> I am using CR7, so there may be some differences.  In any case, Crystal
> can't group on values that don't exist in the data table.  If there are no
> records for February, Crystal doesn't even know that February is a possible
> value for the Month field.  If the table is empty, it wouldn't know about
> any of the months, but you would presumably still want a report with the
> months listed and 0s.

> What I believe you have to do is to do your own counting for the 12 months
> you know could exist.  You could do this by creating 12 formula fields along
> the lines:

> Global NumberVar Jan;
> If {MyTable.Month} = "January" Then Jan := Jan + 1;
> Jan

> These formula fields would accumulate the numbers of records for each month,
> with 0 if there were no records for a given month.   You could the use these
> formula fields in the report footer opposite text objects that give the name
> of the corresponding month.

> Regards,

> John..............


> > I can't figure out how to do the following:

> > I am calculating counts of phone calls for each month. If I group by
> > month and calculate counts for the groups, I only get those months that
> > have at least one call. However, I would like to display zeros for the
> > missing months again. Can you create empty groups for the missing months
> > in Crystal (and/or SQL?)  Or is there a better solution for this
> > problem?

> > In other words, currently I am getting something like this:

> > March   24
> > May      41
> > June      12
> > ...

> > Instead, I want to get

> > January    0
> > February  0
> > March      24
> > April       0
> > May         41
> > June         12
> > ....

> > Any suggestions? CR version 8.0, database is MS Access in case if it
> > matters.

> > Thanks in advance!



Sun, 02 May 2004 03:29:25 GMT  
 creating empty groups?
Charliy,

That's another good idea! As a matter of fact, I managed to find a message in
microsoft.public.vb.crystal archive (on Google) that describes approximately the
same solution. I guess that's the way to go!

Thanks,
Alex

Quote:

> I've done reports like this by writing a "Driver" report that uses a table that
> I know wil have records from every month (actually in my case, every day), then
> using that to trigger a subreport in the group footer that does the totals for
> each period.



Sun, 02 May 2004 03:30:11 GMT  
 creating empty groups?
I use Previous or Next functions to identify the missing data and then a
section with formalas to display the missing label and values.

If month({table.datefield})<> month(next({table.datefield}))) + 1 then
"missing month"

I've even written formulas to print Feb, March if those months are missing.
See the articles in Crystal Clear on www.chelseatech.co.nz/pubs.htm

Quote:

> I can't figure out how to do the following:

> I am calculating counts of phone calls for each month. If I group by
> month and calculate counts for the groups, I only get those months that
> have at least one call. However, I would like to display zeros for the
> missing months again. Can you create empty groups for the missing months
> in Crystal (and/or SQL?)  Or is there a better solution for this
> problem?

> In other words, currently I am getting something like this:

> March   24
> May      41
> June      12
> ...

> Instead, I want to get

> January    0
> February  0
> March      24
> April       0
> May         41
> June         12
> ....

> Any suggestions? CR version 8.0, database is MS Access in case if it
> matters.

> Thanks in advance!



Mon, 03 May 2004 14:51:52 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. creating a program group and group items

2. RemoveFromGroup removes the group when emptied

3. Why One Empty GROUP in Report ?

4. Why One Empty GROUP in Report ?

5. Empty Groups in Crystal Reports

6. create empty table from query

7. Creating a new database and adding appropriate empty tables

8. Outlook Automation - SaveAs creates message with empty body

9. creating an empty *.pst

10. Grouping Tool Windows: tab group or tiled group

11. Creating Empty subdirectories at installation

12. Create Empty Recordset

 

 
Powered by phpBB® Forum Software