Grouping - Display Groups with 0 Value 2

[dunkyn]

I have a report grouped by a formula which sorts the data:
Problem is, if a group is not represented, the group does not appear in the summary. I need to show all groups, even if a value is zero. Any ideas much appreciated.

If IsNull {fieldname} Then 0
Else If {fieldname}="Group Q" Then 1
Else If {fieldname}= "Group Y" Then 2
Else If {fieldname}= "Group A" Then 3
Else If {fieldname}="Group G" Then 4
Else If {fieldname}="Group C" Then 5
Else If {fieldname}="Group X" Then 6
Else 7

TIA

 

[Madawc]

In Crystal, a Group is only shown if it has at least one member. That's a basic of the design.

I suggest you get the same look by some other method, maybe a subreport for each group.

Madawc Williams
East Anglia
Great Britain

 

[synapsevampire]

Create an additional group footer (remember to select suppress is blank on the additional section) and place a formula in it which leverages you current grouping formula, something like:

whileprintingrecords;
numbervar Counter;
stringvar TheOutput:="";
If next({currentgroupformula}) <> {currentgroupformula} +1 then
For Counter := {@currentgroupformula}+1 to next({@currentgroupformula})-1 do(
TheOutput := TheOutput+totext(Counter) +&quot;has no results&quot;+chr13
);
TheOutput

-k

 

[Ngolem]

I like SV's approach (though not necessarily that formula) for &quot;filling in the gaps&quot; of your database.

The problem with the subreport approach is that it is often difficult to actually generate that group if it doesn't exist.

to expand a bit on SV's approach...you create a subsection in the target group's footer. This will have to look of a fake Group header/detailsection and footer section all combined into a single subsection of the real Group's footer.

AFter you process one Group...this section will look ahead and see what the value of the next group will be. If there is a gap (no data) then you will execute this dummy section to fill in that gap.

You also must create a dummy header section in the same manner Why? because your missing data may be the first group(s) and this is what SV did not address. You only use the dummy header on the first record though.

Ok let's start

You must split your Group header section into (A) and (B)

Group(A) header is the fake one and in the the conditional suppress for the section you put the formula

Not onfirstRecord or {@GroupingFormula} = 0;

Now in this section You will make it look like the real Group/details/footer...hopefully it will be something simple like

Header: Description: &quot;Whatever the group is called&quot;

Details: Value: *** No records found ***

Footer: _________________________________________

I like the idea of using numbers for the grouping since this will make it easier to tell how many groups are missing

So you would put the following formula in the Fake Group Header(A)

@Fakeheader

WhilePrintingRecords;
//create an array of the descriptions for each group
StringVar Array GroupingDescriptions :=
&quot;Desc0&quot;,&quot;Desc1&quot;,&quot;Desc2&quot;,&quot;Desc3&quot;,&quot;Desc4&quot;,&quot;Desc5&quot;,&quot;Desc6&quot;,&quot;Desc7&quot;];
NumberVar GroupNum := {@GroupingFormula};
NumberVar pointer;
StringVar Result := &quot;&quot;;

if GroupNum <> 0 then
(
for pointer := 0 to GroupNum - 1 do
(
Result := Result + &quot;Description: &quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result := Result + &quot;Value: *** No Records found ***&quot; +
chr(13) + chr(10);
Result := Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + chr(10);
);
);
//NOTE: no provision in this formula if the length exceeds
//254 char...if this is possible then appropriate action
//must be taken.
Result;

********
Ok, This now is inserted into the Group (A) header and we are half way there

In the footer, we have processed at least one record ...the formula is very similar to the previous one....You create 2 footers (A) and (B)...the first one is real....the second one is fake

The second one is suppressed using the formula

(onlastrecord and {@GroupingFormula} = 7) or
( not nextisNull({@GroupingFormula}) and
Next({@GroupingFormula}) - {@GroupingFormula} = 1);

I like the use of numbers for the Grouping...it makes these formulas very simple

The Fake footer uses the formula as follows

@FakeFooter

WhilePrintingRecords;

StringVar Array GroupingDescriptions ;
NumberVar pointer;
StringVar Result := &quot;&quot;;

if not nextisNull({@GroupingFormula}) then
NumberVar GroupNum := next({@GroupingFormula}) ;

if GroupNum - {@GroupingFormula} <> 1 then
(
for pointer := {@GroupingFormula} + 1 to GroupNum - 1 do
(
Result := Result + &quot;Description: &quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result := Result + &quot;Value: *** No Records found ***&quot; +
chr(13) + chr(10);
Result := Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + chr(10);
);
);
//NOTE: no provision in this formula if the length exceeds
//254 char...if this is possible then appropriate action
//must be taken.
Result;

That is the bare bones of the approach....the formulas would be more complex depending on the nature of the information you want if there is no data for the groups...

Hope this helps








Jim Broadbent

 

[dunkyn]

Whoahh!!!!!!!!!!!!

You guys are GOOD!

Let me work on this and come back to you. I am SO GRATEFUL!!

 

[synapsevampire]

Jim: thanks for pointing out the oversite:

The group header formula might be easier to produce as:

whileprintingrecords;
numbervar Counter;
stringvar TheOutput:=&quot;&quot;;
If onfirstrecord and {@currentgroupformula} <> 0 then
For Counter := 0 to {@currentgroupformula}-1 do(
TheOutput := TheOutput+totext(Counter) +&quot; has no results&quot;+chr13
);
TheOutput

The 254 character limit that Jim warned only applies to version 8.5 and below.

-k

 

[dunkyn]

This is quite basic I am sure, but when you refer to the grouping formula, I assume it is the formula I am grouping on and see when I view Report - Change Group Expert. That is the field I have used in the formulas you provided.

In the fake footer formula I am getting an error message:

&quot;A subscript must be between 1 and the size of the array.&quot;

Look familiar? Any thoughts on what the bug might be?

Here is my formula:

whileprintingrecords;

stringvar array GroupingDescriptions;
numbervar pointer;
stringvar result:=&quot;&quot;;

if not nextisnull ({@MySort}) then
numbervar groupnum:=next({@MySort});
If groupnum-{@MySort}<>1 then
(
for pointer:={@MySort}+1 to GroupNum -1 do
(
Result:=result + &quot;Description: &quot; +
groupingdescriptions[pointer + 1] +
chr(13) + chr(10);
result:=result + &quot;Value: *** No Records Found ***&quot; +
chr (13) + chr(10);
result:=result + replicatestring(&quot;_&quot;,80) +
chr(13) + chr(10)

);
);



And, what is the replicatestring piece doing?

Many, many thanks.

 

[Ngolem]

ok...let us revise this formula a bit...

@FakeFooter

WhilePrintingRecords;

StringVar Array GroupingDescriptions ;
NumberVar pointer;
NumberVar GroupNum := {@GroupingFormula};
StringVar Result := &quot;&quot;;

if not nextisNull({@GroupingFormula}) then
GroupNum := next({@GroupingFormula});

if GroupNum - {@GroupingFormula} > 1 then
(
for pointer := {@GroupingFormula} + 1 to GroupNum - 1 do
(
Result := Result + &quot;Description: &quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result := Result + &quot;Value: *** No Records found ***&quot; +
chr(13) + chr(10);
Result := Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + chr(10);
);
);
//NOTE: no provision in this formula if the length exceeds
//254 char...if this is possible then appropriate action
//must be taken.
Result;

*********************************

The - ReplicateString(&quot;_&quot;,80) - just draws a line of 80 chars long...to separate one fake group from another

The reason for your error is that Crystal is not recognizing the size of the Description array.

Perhaps we should remove the variable declaration form the fake header and put it in the report header instead

**************
@Intialize array (Suppressed in Report Header)

//create an array of the descriptions for each group
StringVar Array GroupingDescriptions :=
&quot;Desc0&quot;,&quot;Desc1&quot;,&quot;Desc2&quot;,&quot;Desc3&quot;,&quot;Desc4&quot;,&quot;Desc5&quot;,&quot;Desc6&quot;,&quot;Desc7&quot;];

//make sure this below is in the formula as a formula cannot be just an array
&quot;&quot;;

**************

Of course, Desc0 ... etc are not the real descriptions.

This will modify the fake Header to


**********
@Fakeheader

WhilePrintingRecords;

StringVar Array GroupingDescriptions ;
NumberVar GroupNum := {@GroupingFormula};
NumberVar pointer;
StringVar Result := &quot;&quot;;

if GroupNum <> 0 then
(
for pointer := 0 to GroupNum - 1 do
(
Result := Result + &quot;Description: &quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result := Result + &quot;Value: *** No Records found ***&quot; +
chr(13) + chr(10);
Result := Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + chr(10);
);
);
//NOTE: no provision in this formula if the length exceeds
//254 char...if this is possible then appropriate action
//must be taken.
Result;


I think this is better


Jim Broadbent

 

[zeugma1]

If you have access to the underlying database there may be a simple work around. You need the field on which you are grouping to be indexed, and you need to have or be able to create a table of all distinct valid categories for that field. Suppose you have two tables in the original report, Customers and Orders. You group on Customer.Category, but not all categories are represented on the report, giving you missing lines.
Suppose you have five customer categories: you need a table (create it if necessary) containing just one field and one record for each of these five categories. Let's call this table 'Categories'. Make sure that the Category field in the Customers table is indexed. Add the new 'Categories' table to your report, and link its 'Category' field to the indexed 'Category' field in the Customers table. Make the field on which your report groups not Customer.Category, but Categories.Category. Job done.

You can't always use this approach, but when you can it is simple and robust - no complicated formulae. It can also solve the related problem of missing lines in a cross-tab.

 

[dunkyn]

Jim - I have yet to test your code - you are a wizard!

zeugma1 - Great idea! I like it. Problem is, we are using stored procedures, and we don't have the expertise in-house to modify the code within it. Can you tell me if it is possible to link a stored procedure to an external table?

Also, is there any way to resolve this issue through the group selection formula (where subtotal = 0)?

Thank you both very much.

 

[dunkyn]

Jim -

I am getting an error on the fakeheader and I crash. Unfortunately, the string exceeds 254 characters.

Darn!

 

[Ngolem]

ok...as noted in the earlier post I thought that this might happen...it is not a big problem really

Probably what has happened is that you have run into several missing headers in a row....so we just create a second variable to handle the overflow...or if this still is a problem you will have to create yet a third I suppose. I will Bold the new code in the formula

**************************
@Fakeheader

WhilePrintingRecords;

StringVar Array GroupingDescriptions ;
NumberVar GroupNum := {@GroupingFormula};
NumberVar pointer;
StringVar Result := &quot;&quot;;
StringVar OverResult := &quot;&quot;;

if GroupNum <> 0 then
(
for pointer := 0 to GroupNum - 1 do
(
if length(Result) > 230 then
(
OverResult := OverResultResult +
&quot;Description: &quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10) ;
OverResult := OverResult + &quot;Value: *** No Records found ***&quot; +
chr(13) + chr(10);
OverResult := OverResult + ReplicateString(&quot;_&quot;,80) +
chr(13) + chr(10);
)
else
([b/]
Result := Result + &quot;Description: &quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result := Result + &quot;Value: *** No Records found ***&quot; +
chr(13) + chr(10);
Result := Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + chr(10);
);
);
);

Result;


You only display &quot;Result&quot; in this formula but now you create another subsection of the header and place the following formula DIRECTLY UNDERNEATH @Fakeheader

@FakeheaderOverflow

WhilePrintingRecords;

StringVar OverResult;

OverResult;


Make sure the &quot;Suppress blank Section&quot; is enabled and use the same conditional suppress on the section as you did with the subsection above it...

ie: Not onfirstRecord or {@GroupingFormula} = 0;

It is important to snug the lower border to @FakeHeader and Place @FakeHeaderOverflow at the top of its section...then the &quot;CanGrow&quot; property of both formulas will allow the section to expand as much as necessary without over-writing eachother.

So your structure should look like this

Group 1a @Fakeheader
Group 1b @FakeHeaderOverflow
Group 1c Real Header information


You would modify the footer section in a similar manner to prevent an overflow there.








Jim Broadbent

 

[Ngolem]

damn!!! hit the wrong button...

I wanted to correct the bolding but also offer a comment on this line

if length(Result) > 230 then

I have arbitarily chosen 230 as a trigger length...you may have to play with this a bit.

You want to make it such that you can fit in one more fake set of data...or switch to the new variable if you cannot.



Jim Broadbent

 

[dunkyn]

Thx! Thx! I'll give it a try!

Have a great weekend.

 

[dunkyn]

I tried to modify this and add a third tier = still getting the same error. Appreciate your efforts though. Brilliant work. Thx.

whileprintingrecords;
StringVar Array GroupingDescriptions;
NumberVar GroupNum:={@MySort};
NumberVar pointer;
StringVar Result:=&quot;&quot;;
StringVar OverResult:=&quot;&quot;;
StringVar OverResult2:=&quot;&quot;;

if GroupNum<>0 then
(
for pointer:=0 to GroupNum -1 do
(
if length(Result)>100 then
(
Result:= OverResult +
&quot;Desc:&quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
OverResult:=OverResult + &quot;Value: *** No Records Found ***&quot;+
chr(13) + chr(10);
OverResult:=OverResult + ReplicateString(&quot;_&quot;,20) +
Chr(13) + chr(10);
)
else if length(Result)>101 then
(
Result:= OverResult2 +
&quot;Description:&quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
OverResult2:=OverResult2 + &quot;Value: *** No Records Found ***&quot;+
chr(13) + chr(10);
OverResult2:=OverResult2 + ReplicateString(&quot;_&quot;,80) +
Chr(13) + chr(10);
)
else
(
Result:= Result + &quot;Description:&quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result:=Result + &quot;Description: &quot; +
GroupingDescriptions[pointer +1] +
chr(13) + chr(10);
Result:=Result + &quot;Value: *** No Records Found ***&quot; +
chr(13) + chr(10);
Result:=Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + Chr(10);
);
);

);

Result;

 

[Ngolem]

Your limits on the size of the string are WAY TOO SMALL You want the character lenght to approach 254 char without going over...you had it moving to the next variable at 100 char and the next at 101 char....

Also you have mixed Result and OverResult together....probably because of cloning errors....you have to be careful when cloning

I have made corrections to your formula and highlighted them for you to see. NOTE: I have reduced this to a single &quot;overflow&quot; variable since I think your error caused the problem

If you compare this with yours above you will see many deletions....Follow my formula exactly please


whileprintingrecords;
StringVar Array GroupingDescriptions;
NumberVar GroupNum:={@MySort};
NumberVar pointer;
StringVar Result:=&quot;&quot;;
StringVar OverResult:=&quot;&quot;;


if GroupNum <> 0 then
(
for pointer := 0 to GroupNum -1 do
(
if length(Result)> 230 then
(
OverResult:= OverResult +
&quot;Desc:&quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
OverResult := OverResult + &quot;Value: *** No Records Found ***&quot;+
chr(13) + chr(10);
OverResult := OverResult + ReplicateString(&quot;_&quot;,20) +
Chr(13) + chr(10);
)
else
(
Result:= Result + &quot;Description:&quot; +
GroupingDescriptions[pointer + 1] +
chr(13) + chr(10);
Result:=Result + &quot;Value: *** No Records Found ***&quot; +
chr(13) + chr(10);
Result:=Result + ReplicateString(&quot;_&quot;,80) +
chr(13) + Chr(10);
);
);

);

Result;

Review the previous instructions I gave you....make sure they are followed and the formulas are exactly as I have shown...If there are still overflow problems we will try to find out why this is happening.....

For example: How big are these descriptions?? GroupingDescriptions[pointer + 1]

Jim Broadbent

 

[RichardWyant]

dunkyn

I am having the same scenario, what was your outcome or is there a better alternative to create the grouping. thanks Rich

 

[zeugma1]

re linking stored procedures and tables: I think the answer is sometimes - do what they do in China (try it and see!). Set up ODBC names and have a go. I know that Crystal will sometimes take mixed sources and sometimes not, depending.

 

[dunkyn]

Richard -

See the thread on Join Stored Procedure with Table.
The best solution I believe lies in how the tables are joined. You need a left outer join to include fields with all values.

 

[zeugma1]

This method mimics what you would do in old fashioned batch processing. In the group header, create an additional section for each category which can appear (or not). If there are three categories, this would give you this arrangement in the report design:

Group header #1a:
Group header #1b:
Group header #1c:
Group header #1d:

#1a is the stuff that will appear normally if there are records for that category. #1b,#1c and #1d contain just the name of a category and '0' entries in the columns - type them in as text fields. You conditionally suppress these as follows:

#1a is unconditionally visible;

You know what group 1 should be, say something called 'urbo', so at #1b put the suppression formula:
(GroupNumber=1 and {CategoryName}=&quot;urbo&quot or GroupNumber<>1

You know what group 2 should be, say something called 'mirf', so at #1c put the suppression formula:
(GroupNumber=2 and {CategoryName}=&quot;mirf&quot or GroupNumber<>2

..and so on. If there are records for a group then the row of zeroes will be suppressed. If there are no records for a group, then section #1a will not appear (your original problem) but the row of zeroes will. You might need to drag #1a to be the last section to get the order of appearance right.