How to Show when there are no records? 3

[BradW]

Hi, thanks for all assistance!

Using Crystal Professional 9 on a Oracle Database using ODBC to connect.

I have a report that summarizes records in several groups (in specified order using several specified groups and no "others" category)

I have everything working correctly, except when there are no records in a certain group, it does not show that group on the report due to the lack of records. This is the behavior that I expect from Crystal. However, my boss wants to see an entry for all groups, which means that he wants a zero for those groups that have no records.

Currently I get:

Group 1 1
Group 2 3
Group 4 17
Group 5 9
Group 7 2
(and so on)

What I want to get is:

Group 1 1
Group 2 3
Group 3 0
Group 4 17
Group 5 9
Group 6 0
Group 7 2
(and so on)

How can I get the zero labels for these empty groups to show up. I tried to write a formula that would insert itself in the right spot but it did not work.

Thanks again for all help!

 

[rhinok]

One of the easiest ways to accomplish this goal is to use left outer joins. The table that contains the field by which the report is grouped must be on the left sides.


~Kurt

 

[BradW]

Thank you for the quick answer. I should have given more details. All of the data is in one table, and there is a field that is being used to select only current records. So I am working on group totals for this one table and a left outer join will not handle this one because there is no join involved and there is no positive record anywhere in the dataset to key the group off of.

 

[Katy44]

I have a similar problem and would be interested in an answer.

 

[BTSTeam]

My first ever post to Tek-Tips, so here goes...

Maybe this is a bit clumsy but it works for me.

Set up another data table containing simply all possible group numbers i.e. 1-10, and then left join from that table.

 

[Madawc]

I only use Crystal 8.5. But I'd do it as a set of ten running totals, which I'd put in the report footer.

If your boss insists on seeing the totals first, you could also do it as a subreport in the header, also using running totals.

Madawc Williams (East Anglia)

 

[Katy44]

BTSTeam, thank you! It seems that that would work and I could certainly do it in this case. However there are times when you just don't have the rights to change the database, and I'd love to know if there's a way of doing it then as well (I actually have this problem twice, once on a database that I'll be able to change, and once on one that I shouldn't)
Madawc, I know your post refers to the question (which is nothing to do with me!) but I don't really follow. Could you go into more detail?

 

[BradW]

Thanks to Madawc, the running totals was just the answer to my problem, thanks for pointing me in that direction!

 

[Madawc]

To katy44

Suppose that records may be for apples, oranges, pears, bananas or guavas. You can group by type of fruit, but that does not show cases where there are no oranges. So you do a set of running totals (which are an option under the [Field Object] on the [Insert] menu). Each of them does a count of something, maybe account number. But each uses the formula option on the [Evaluate] section, and counts just for the one fruit.

You could also put each running total in a different section of the report footer. Suppress that section when the total is greater than zero, so only omitted groups are mentioned.

It must be the report footer, because running totals are accumulated during the course of the run.

Madawc Williams (East Anglia)

 

[Katy44]

Thank you very much!
I have tried it out and seen how it would work - I now know what to do if they demand that all groups are visible!

 

[sloht]

I am stuck in a task almost identical to BradW's first post in this thread. I am trying to group and summarize timestamped records by hour, and want to display a summary for hourly periods that do not contain any records. E.g. if the period from 1:00pm to 2:00pm does not contain any records, I would like to display a sum of zero for it alongside the sums for the other periods.
A little more detail as to how to use Running Totals for this would be fantastic. If I'm understanding how running totals work, I'm missing how you can use a running total to generate groups that aren't already there.
If using a formula in the evaluate section of Running Totals is the answer, a description of the formula would be really helpful.

 

[lbass]

sloht,

With this technique you would create a separate running total for each interval. You would select whatever field you are trying to count (or sum), e.g., {table.ID}, evaluate based on a formula:

time({table.datetime}) in time(1,0,0) to time(1,59,59)

Reset on change of group (if you are evaluating at the group level) or never (if at the report level).

You would then place the running total in the group footer or the report footer, depending on what level the running total is meant to evaluate, with a text box identifying the interval.

-LB