Link to home
Start Free TrialLog in
Avatar of Taras
TarasFlag for Canada

asked on

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.
Avatar of Taras
Taras
Flag of Canada image

ASKER

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.
Avatar of Mike McCracken
Mike McCracken

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
Avatar of Taras

ASKER

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?
Avatar of Taras

ASKER

Should subreport be grouped? As Group1 Customer -> Group2 Suppliers ?
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
Avatar of Taras

ASKER

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.
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
Avatar of Taras

ASKER

I will wait and I would appreciate it very much.
Avatar of Taras

ASKER

mlmcc
Those missing Suppliers should be at the end I mean after present are listed then goes those missing.
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
Avatar of Taras

ASKER

mlmcc.
This is great, any progress with missing supplier type ?
Not yet.  I'll need to work that issue tonight.

mlmcc
Avatar of Taras

ASKER

no problem, thanks.
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
I think this has it for the missing types.

mlmcc
Missing-Categories.rpt
salary-by-week.xls
Avatar of Taras

ASKER

mlmmc
Why Customer 101 do not have supplier type Toy  and suppliers under this type?
Avatar of Taras

ASKER

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.
I attached the wrong report.

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

mlmcc
Avatar of Taras

ASKER

Hi mlmcc, any luck?
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Taras

ASKER

Thanks a lot mlmcc, excellent as always.