Crystal report show records from right side that are not in left side of join

In Crystal report 2011 I have next tables: Invoice, customers and table suppliers
I am grouping on Customers and Suppliers.  Let say there is 5 suppliers.
Some customers have all 5 suppliers some have two or three.
I want to show for each customer all five suppliers does not matter if they do not buying from them or not.
How to get this.
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TarasAuthor Commented:
Somebody could say in Main report  just do sub report. And in sub report   do right side join between Customer and Suppliers  and put condition that join field CustomerID in Customer table  is Null and pull all records from Suppliers table.  So you get only Suppliers that do not have customers.
That is easy. Ok after I do this how to join – connect main report with this sub report when they  do not have common filed and plus to be more complicated Supplier is subgroup of Customer group in main report.
mlmccCommented:
Does the order matter?

Will the number of suppliers increase from 5?
Do the suppliers change?

There are ways with formulas and arrays to accomplish what you want in the main report without having to use a subreport.

If the suppliers will change can you add a subreport to the report?

mlmcc
TarasAuthor Commented:
mlmcc
Order does not matter.
The number of supplier will increase over time, for now they are 5.

 You suggest to add subreport .Supplier is grouping field, kind subgroup of Customer group in main report , If I add subreport where to add it?
Should I crate new section where supplier group is and put it below? I need just suppliers name as those suppliers  should have all 0 values in the report rows  for amounts that I am calculating for invoices.
Will subreport be linked through CustomerID as parameter in sub-report?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

TarasAuthor Commented:
Should subreport be grouped? As Group1 Customer -> Group2 Suppliers ?
mlmccCommented:
For the idea I have in mind the subreport will go in the report header.  It will create a list (Array) of suppliers.  The list will then be used to display the names of the "missing" suppliers.  It will be an unlinked subreport.

I assume you have a supplier table that can be used to get the list of all suppliers.

Create a NEW report using the supplier table as the source
Add a formula to the report header

Name - Declare_Variables
WhilePrintingRecords;
Shared StringVar Array Supplier_List;
Shared NumberVar Supplier_Count;
''

Open in new window


Add a formula to the detail section

Name - Build_Supplier_List
WhilePrintingRecords;
Shared StringVar Array Supplier_List;
Shared NumberVar Supplier_Count;
Supplier_Count := Supplier_Count + 1;
ReDim Preserve Supplier_List[Supplier_Count];
Supplier_List[Supplier_Count] := {SupplierNameField};
''

Open in new window


Add a sort to the subreport on the SUPPLIER NAME



In the current report add a formula to the report header

Name - Declare_Variables
WhilePrintingRecords;
Shared StringVar Array Supplier_List;
Shared NumberVar Supplier_Count := 0;
Global NumberVar Current_Supplier_Index := 0;
''

Open in new window


Add a second report header
Right click the Report Header in the left margin
Click INSERT SECTION BELOW
Put the subreport in this report header section (RHb)

If you don't have a group on supplier in the main report Add a group on Supplier

In the Supplier group header (main report)
Add a second group header
Right click the Group Header in the left margin
Click INSERT SECTION BELOW
Put the current group header information in GH2b

Add a formula to GH1a
Name - Display Missing Suppliers

WhilePrintingRecords;
Shared StringVar Array Supplier_List;
Shared NumberVar Supplier_Count;
Global NumberVar Current_Supplier_Index;
Local StringVar MissingSuppliers;

MissingSuppliers := "";
Current_Supplier_Index := Current_Supplier_Index  + 1;

While Current_Supplier_Index <= Supplier_Count AND
           Supplier_List[Current_Supplier_Index] < {SupplierNameField} do
{
    MissingSuppliers := MissingSuppliers  & chr(13) &  Supplier_List[Current_Supplier_Index ];
    Current_Supplier_Index := Current_Supplier_Index  + 1;
};
If MissingSuppliers  <> "" then
    Mid(MissingSuppliers,2)

Open in new window


In the customer group footer add a formula
Name - Display Remaining Suppliers

WhilePrintingRecords;
Shared StringVar Array Supplier_List;
Shared NumberVar Supplier_Count;
Global NumberVar Current_Supplier_Index;
Local StringVar MissingSuppliers;

MissingSuppliers := "";
Current_Supplier_Index := Current_Supplier_Index  + 1;

While Current_Supplier_Index <= Supplier_Count   do
{
    MissingSuppliers := MissingSuppliers  & chr(13) &  Supplier_List[Current_Supplier_Index ];
    Current_Supplier_Index := Current_Supplier_Index  + 1;
};
If MissingSuppliers  <> "" then
    Mid(MissingSuppliers,2)

Open in new window


mlmcc
TarasAuthor Commented:
mlmcc
 I need to add additional groups : So now  instead Customer--> Supplier , I have four Groups:  Customer_Catergoy --> Customer-->Suppliers_Type--> Supplier.  Is the positions of the subreport and formulas the same?
I apologize for this it came after I started this.
mlmccCommented:
Yes, though the supplier type group is going to be an issue.  As written it won't work since the supplier list will have all the suppliers but within a suppliers type, I assume the suppliers will be a subset of that.

The same basic idea works but it will need to rebuild the supplier list in the supplier type group header rather than for the full report.

I don't have time to redo it right now.  If you need help, I'll look at it tonight.

mlmcc
TarasAuthor Commented:
I will wait and I would appreciate it very much.
TarasAuthor Commented:
mlmcc
Those missing Suppliers should be at the end I mean after present are listed then goes those missing.
mlmccCommented:
Here is the basic idea.  I haven't fleshed out how to handle the missing Supplier Type yet.

mlmcc
Missing-Categories.rpt
Missing-Categories-Sub.rpt
TarasAuthor Commented:
mlmcc.
This is great, any progress with missing supplier type ?
mlmccCommented:
Not yet.  I'll need to work that issue tonight.

mlmcc
TarasAuthor Commented:
no problem, thanks.
mlmccCommented:
For missing Supplier types is it sufficient to see them or would you want to see all the suppliers also?
The first is relatively easy.  The other will require more thought.

mlmcc
mlmccCommented:
I think this has it for the missing types.

mlmcc
Missing-Categories.rpt
salary-by-week.xls
TarasAuthor Commented:
mlmmc
Why Customer 101 do not have supplier type Toy  and suppliers under this type?
TarasAuthor Commented:
A apologize I did not see you previous post, yes I need to see missing suppliers type for customer 101 and suppliers under this type.
mlmccCommented:
I attached the wrong report.

I'll get the right one tonight or perhaps have the full requirement done by then.

mlmcc
TarasAuthor Commented:
Hi mlmcc, any luck?
mlmccCommented:
Just got it working.

mlmcc
Missing-Categories.rpt
Categories.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Thanks a lot mlmcc, excellent as always.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.