Displaying Empty Groups 2

[Canthros]

I've got a situation where I need to display a group which doesn't have any records associated with it. For instance:

Group 1        Summary
Group 2        Summary
Group 3        Summary
  Total        Sum

In the current situation, Group 1 does not display because the query does not return any values which fall into that group. However, if it doesn't display it will throw the formatting off, and probably cause some difficulties down the road. I haven't found any obvious method to solve this, and can't think of any way to simulate a record in the group without having to alter the data in the database. Any ideas?

 

[Ngolem]

I don't understand why you want to group on something that isn't there but yes it is possible.

use the formula like the following


If not isnull({Table.field}) then
{Table.field}
else
"1";

now group on this formula...the only thing to be carefull about is that {Table.field} must be a string in the above example...if it isn't then wrap it in a totext function


Jim Broadbent

 

[Canthros]

Not quite what I was getting at, I'm afraid. I have no records for this group, right now. In the future there might be records in the group, or there might not (I don't control data entry), but, for the report, I need to show the group name and the summary value (null or 0, or whatever). Doing so preserves the formatting of the report, which I did not design (or I wouldn't bother about this).

If I check for nulls, I'm not going to get what I want. This query involves a large join, so a null in that field indicates missing information (as opposed to information that just doesn't exist), and I don't want to lump that in with this group. I hope that makes things clearer.

 

[Ngolem]

Not really....I'm not sure what you are doing on the information you have given.

If the grouping field which is null now has values eventually then the GroupName will be the value of the
{Table.field} that you are grouping on. If this is a number simply convert it to a string using ToText()

instead of assigning a "1" when the {Table.field} is null call it anything you want that is more descriptive.

If not isnull({Table.field}) then
{Table.field}
else
&quot;<description>&quot;;

This type of formula when it is used as a group...will act like a normal group when data is present for {Table.field} and act as a constant when there is no data

As far as summaries go...they are still easy by using formulas to give instructions on what to do when or When not&quot; data exists.

show me how the report should look with or without a group to group on.

Jim Broadbent

 

[Canthros]

Sorry, it just occurred to me that some information is missing that might be helpful:

All three groups are custom groups. That is, each group already has a set criterion on which it matches records and pulls them into itself. They group on an existing field on each of three values in the field (they're used to specify some information about the type of object described by the record).

However, there are no records which contain the value which Group 1 is created around. Records matching into Group 2, and Group 3 both exist, and are shown, but there are no matching records for Group 1. Because Group 1 does not have any matching records, Crystal does not print it. I need some way to either override this behavior (and show Group 1 even though there is nothing in it), or simulate a record in the group without adding data to the database or pulling in records that I don't want (like the null values).

If this isn't possible, I'll just need to work something else out, but I would like to know if it can be done before I go about trying to change the way the report is laid out.

 

[Canthros]

View of report (with records in all groups)

Group 1               $80
Group 2               $85
Group 3               $20
  Total              $185

View of report (without records in Group 1)

Group 1                $0
Group 2               $85
Group 3               $20
  Total              $105

I've hidden the details because I'm only interested in the group and report sums. When there are no records in Group 1, I'd like for it to show up with a zero. I can handle a custom summary (I've already got one going to get rid of duplicates -- selecting distinct isn't available for this query), but I don't know how to make the group show up.

 

[synapsevampire]

You can't report on data that doesn't exist.

If you only have a few groups, consider cheating this by building a few running total fields and place them in the report footer, each of which would have the explicit Evaluate->Use a Formula to match each criteria.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

describe how this &quot;custom Group&quot; is created... Jim Broadbent

 

[Canthros]

By custom group, I meant that the group was on a particular attribute/field but in a specified order (rather than ascending/descending on the group name or field value), which also requires specifying a name for the group. This terminology was used in the Crystal Reports 8.5 help file, so I didn't think it would be confusing to use it here.

In the meantime, I think that k/synapsevampire has provided me a solution (one which now seems obvious enough that I cannot believe it hadn't already struck me fully upon the forebrain). I thank both of you for your effort; it is much appreciated.

 

[Ngolem]

Ok...then there are a finite number of &quot;Special Groups&quot;

One thought ,I had, was to create 2 arrays...like this in the Report header

@Initialization (suppressed in report header)

WhilePrintingRecords;

//list the special Groupnames here as they would exist in Crystal
stringVar array SpecialGroup := [&quot;GroupName1&quot;, &quot;GroupName2&quot;, ... , GroupName_n];
//initialize totals to zero
numberVar array GroupTotals := [0,0,0,...,0];
&quot; &quot;; //this is necessary to make the formula legal

both arrays have the same number of elements to them...also you see that you have the zero totals that you want if no data appears


Now...you can do mechanical totals to sum the totals using a formula like this

@CalcSums (suppressed in the detail section)

WhilePrintingRecords;

stringVar array SpecialGroup ;
numberVar array GroupTotals ;
numberVar iCount;

for iCount := 1 to ubound(SpecialGroup) do
(
if SpecialGroup[iCount] = {Groupname} then
GroupTotals[iCount] := GroupTotals[iCount] + {table.field};
Exit for;
);


Now comes the easy part....in the report footer place your display table of results using these formulas

@DisplayGroupNames (in Report footer, &quot;can grow&quot; enabled)

WhilePrintingRecords;

stringVar array SpecialGroup ;
stringVar result := &quot;&quot;;
numberVar iCount;

for iCount := 1 to ubound(SpecialGroup) do
(
result := result + SpecialGroup[iCount] + chr(13) + chr(10);
);

result;

the only caution is that the size of &quot;result&quot; cannot exceed 254 characters...if it does then use 2 or more similar formulas.

@displayGroupTotals (placed next to @DisplayGroupNames , &quot;can grow&quot; enabled)

WhilePrintingRecords;

stringVar array GroupTotals ;
stringVar Tresult := &quot;&quot;;
numberVar iCount;

for iCount := 1 to ubound(GroupTotals) do
(
Tresult := Tresult + GroupTotals[iCount] + chr(13) + chr(10);
);

Tresult;

If there is more than one display of the groupNames formulas you will have to have the same number of formulas for the totals as well

There you go....done Jim Broadbent