How to display groups with no results only using one table

[CanadianDesigner]

I am using Crystal Reports 11. Currently, a user will choose a number of groups from a parameter, and I want the report to show all groups chosen in the final report, but for ones where there are no results, for it to display something like "No results found."

The problem I'm having is since I am only using one table, I can't create an left outer join, so I'm not sure how to do this.

I thought I could add a running total to the group footer, but seem to be having trouble writing a formula that will make this happen. Any help would be much appreciated.

 

[CrystalizeCanada]

Hi CanadianDesigner,

One way to do it is to have a formula display those that are not present in the record set but present in the paramater choices. So lets say you chose Tom, Sue, Pam, Jim and there were no records for Jim and Sam it would display Jim and Sam as missing.

TO do this you would need two formulas:

//PURPOSE OF FROMULA: Find the position in the Paramater array of records that are present in the array.
//TIMING OF FORMULA:
WhileReadingRecords;
//VARIABLE DECLARATIONS:
NumberVar Array PresentAndCorrect; //this is the number array which hold the positions for all the matched names
StringVar Array DontCheck;
StringVar TheField:={Employee.Last Name};//Put your field here
Local Numbervar i;
//FORMULA RESULT:
If Not (TheField in DontCheck)
Then(
Redim Preserve DontCheck[Count(DontCheck)+1];
DontCheck[Count(DontCheck)]:=TheField;
//locate its position in the array
If (TheField in {?My Parameter})
then
for i := 1 to Count({?My Parameter}) do
if TheField= {?My Parameter} Then exit for;
Redim Preserve PresentAndCorrect [Count(PresentAndCorrect)+1];
PresentAndCorrect[Count(PresentAndCorrect)]:=i;
)


A second formula would go in the report header:

//PURPOSE OF FORMULA: Using a process of elimination display the parameter members not present
//TIMING OF FORMULA:
Whileprintingrecords;
//VARIABLE DECLARATIONS:
Numbervar array PresentAndCorrect;
Stringvar array DisplayIt;
Local numbervar i;
//FORMULA RESULT:
For i:= 1 to count({?My Parameter}) do
if not (i in PresentAndCorrect) then (
Redim Preserve DisplayIt [Count(DisplayIt)+1];
DisplayIt[Count(DisplayIt)]:={?My Parameter};
);
Join (DisplayIt,chr(13))

There may well be a much faster way of approaching this however this seemed to work with the Xtreme Sample Database

Gordon BOCP
Crystalize

 

[CanadianDesigner]

Hi Gordon,

Thanks so much for the response! My problem is, I don't want to display records that don't match, I only want to display group headings that have no results. I believe the formulas you've provided above works to display the records that don't fit, correct? If I did that, I would have thousands of results.

Am I misunderstanding?
CanadianDesigner

 

[CanadianDesigner]

Just to clarify a bit more... in case I'm being unclear... say I have records for Tom (blond hair), Sue (brown hair), Pam (blond hair), and Jim (brown hair) and in the report I have all results grouped into people with red hair, blond hair and brown hair.

My problem is, my report is currently only showing the groups which have results (Blond Hair and Brown Hair). Is there any way for me to have Red Hair still come up in the report, but just "No results found." listed underneath?

 

[CrystalizeCanada]

Hi,

Just to clarify as you didn't mention the role of the parameter in the follow-up postings. Is the following correct: You have a parameter that gives us the following choices: Blonde, Black, Brown, Red Hair. A user picks Blonde, Brown, Red hair from the paramater. Your report is grouped by hair colour. You want to see Red Hair No Results as a group heading as well as ofcourse the other ones they picked. N.B. You don't want to see Black No Results because the didn't pick that one.

Let me know if the assumptions are correct above.

Gordon BOCP
Crystalize

 

[CanadianDesigner]

My apologies, yes, that is correct. Thanks!

 

[CrystalizeCanada]

Thanks for clarifing

The formula I posted earlier would give you a result of Red Hair. In this scenario they could have chosen Black Hair too and we would need two seperate Groups: Red (Missing) and Black (Missing). The formula I posted earlier would now give us Red Hair and Black Hair (but not as a group yet).

So this is the information you need however its not a group header - I suggested placing this in the Report Header because to get these headings to show up as missing groups alphabetically distributed amongst the groups is potentially a lot of work, depending on the actual data we are dealing with for our grouped column. If it is a number column it isn't so bad however if it is a string column it is more work.

First confirm that the formula is giving the correct results for the missing groups in the report header by editing the formula and replacing TheField with your field. Then you can move on to positioning the results alphabetically amongst the groups.

Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Hi CanadianDesigner,

Another thought I just had if we had this 'hair' scenario is to create a command that would list out all the values 'red','black','blonde','brown' etc. This would act as the missing table allowing you to do your left outer join and simplifying this whole process.

This is assuming that these values are fixed as would be the case with hair colour.

In this case you could create a Command using a UNION JOIN you can 'fake' this table.

For example
SELECT 'blonde'
FROM anytable
UNION
SELECT 'brown'
FROM anytable
UNION
etc...

This could then give you a much simpler solution.

Linking to a command instead of a table can end up being slow however we can work on that to improve speed.

Gordon BOCP
Crystalize

 

[CanadianDesigner]

Thanks Gordon. I'd like to give this a try, but have never worked with Commands before. In your above example, where you have FROM anytable... what do I put in for anytable?

Do I enter the table name + the field? (ex. Customer.'HairColour') Also, can you finish that off to explain how I create the OUTER LEFT join?

Sorry for the extra hassle. I really appreciate all your help!

CanadianDesigner

 

[CrystalizeCanada]

Hi,

The anytable has to be the name of a valid table in the database, even though you are not using a field from this table. It doesn't make sense but that is how it has to be.

There are no field names here just values - the same values you would have in your parameter pick list. In the first SELECT line you can put an alias for the column heading, for eg.

SELECT 'brown' as HairColour
FROM arealtablename
UNION
SELECT 'blonde'
FROM arealtablename
UNION etc.

Then add this command to the report as well as of course the original table and you will get an opportunity to link with a Left Outer Join on the linking screen that will appear. You will link the HairColour field on the original table to the hairColour field in the command.

Sometimes you hit bugs and linking issues with commands however these can normally be dealt with.

G

Gordon BOCP
Crystalize

 

[lbass]

I didn't read this entire thread carefully, but you could use the following:

//{@detailsectionformula}:
whileprintingrecords;
stringvar haircolor;
if instr(haircolor,{table.haircolor}) = 0 then
haircolor := haircolor + {table.haircolor}+", ";

//{@displaymissing} to be placed in the report footer (Only):
whileprintingrecords;
stringvar haircolor;
stringvar missing;
numbervar i;
for i := 1 to ubound({?haircolor}) do(
if not({?haircolor} in haircolor) then
missing := missing + {?haircolor} +", "
);
if len(missing) > 0 then
"No data for : " +
left(missing, len(missing)-2)

-LB

 

[CrystalizeCanada]

Hi lbass,

This is a much easier way of doing the formulas method - also the second formula could also be placed in the header if the first one is modified to WhileReadingRecords instead of WhilePrintingRecords.



Gordon BOCP
Crystalize

 

[CanadianDesigner]

Thank you both SO MUCH for your help! lbass, I did end up using the formula you provided and it worked great.

CrystalizeCanada, I will keep working with the ones you suggested so I understand how they work in case there are other instances where they might be worth trying.

Thank you again... it is much appreciated!
CanadianDesigner

 

[lbass]

Thanks to CrystalizeCanada for pointing out that the display formula could be placed in the report header with that one change.

-LB