What is the best solution of this recurring problem?

[yehong]

I often run into a grouping problem in almost every other report.
The problem is:
It is always desired that if a group does not contain any records, it must still appear on the report.
Most common solutions that I have seen are to outer join tables or create tables with all groups and outer join with other tables.
I wanted to know if there is a best and easiest way to do it within Crystal? I am using CR8.5.
Currently I need to show 5 groups. So lets say if there is no detail for any group, the group still should appear on report.


Thanks,

 

[dgillz]

The best and easiest solution to this is one you have already mentioned in your post:

Most common solutions that I have seen are to outer join tables or create tables with all groups and outer join with other tables.

There is no easier way to do this.


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

I agree with dgillz because if you set it up in the database it's reuasble by Crystal and other processes, if you hardocde it into a CR then you're doomed to poor reusability and more maintenance.

And what do you fibd unappetizing about outer joins?

In CR 9 you can even generate a query and paste it in (OK, in CR 8.5 you can too using RDO/ADO, but I rarely suggest doing so).

-k

 

[Ngolem]

A simplistic method for doing this would be to make fake groups in your group Header.

This will only work on very repeatable groupings where you know the grouping values

Say for example you have a group based on {table.productname} and you have 5 potential product groupings

Beans
Carrots
Corn
Peas
Potatoes

in the report header place the formula

//@flagInit
WhilePrintingRecords;
booleanVar BeansFlag := false;
booleanVar CarrotsFlag := false;
booleanVar CornFlag := false;
booleanVar PeasFlag := false;
booleanVar PotatoesFlag := false;

Beans will always be the first grouping

NOTE: for this approach to work...the fake Group header sections must be in REVERSE order..

Potatoes
Peas
Corn
Carrots
Beans

this is required in order for the flags to work properly


So in the group header you create a fake Group header section for Beans

in the conditional suppress for that section put the formula

whilePrintingRecords;
booleanVar BeansFlag ;
if onFirstRecord and {Table.product} = "Beans" and not BeansFlag then
(
BeansFlag := TRUE;
TRUE;
)
else
(
BeansFlag := TRUE;
FALSE;
);

Now you add a fake header section for "Carrots"

in the conditional suppress

whilePrintingRecords;
booleanVar BeansFlag ;
booleanVar CarrotsFlag ;

if BeansFlag and not CarrotsFlag and {Table.product} = "Carrots" then
(
CarrotsFlag := TRUE;
TRUE;
)
else
(
CarrotsFlag := TRUE;
FALSE;
);

Now you add a fake header section for "Corn"

in the conditional suppress

whilePrintingRecords;
booleanVar BeansFlag ;
booleanVar CarrotsFlag ;
booleanVar CornFlag ;

if BeansFlag and CarrotsFlag and not CornFlag and {Table.product} = "Corn" then
(
CornFlag := TRUE;
TRUE;
)
else
(
CornFlag := TRUE;
FALSE;
);


and so on....as each group is passed...the proper group header or a fake one is printed...the flags of each group (real or fake) are updated as you go to control the sections.

in the report footer you also need a repeat of these fake headers in the case where say you only have the first 3 products and the reset are missing.

Here you put the subsections in the proper order and for each subsection the following formula is needed in the conditional suppress for that section.

for the "Beans" report subsection
//this will suppress the Bean report footer fake header if it is already done
whilePrintingRecords;
booleanVar BeansFlag ;
BeanFlag;

Similarly you would do the others using the appropriate flag.

There is no easy way of doing this but if the report groupings are few and never change ...this might be an approach to try.

This is not a prefered method...but I often had to resort to such methods since I was not allowed permission to use stored procedures or add/alter tables in most of the contracts that I have worked on and most clients were too busy with other issues to do it for me....so we kludge


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[yehong]

Thanks everybody for your inputs,and Jim I
really appreciate your detailed response. Infact this solution fits most of my requirements as in most cases the groups are fixed.
I will give it a shot and would let you know of the results.

Thanks,