Sub-Report not displaying all data

[LFB]

Hi all,

CRW 8.0.441
VB 6 (sp4)
SQL2000

How do you get a subreport to display all data in its database?

My main report contains database x.
My sub-report contains database y.
I am trying to run a report based on Customer.

Database x contains some Customer records that have no corresponding Customer in y and vice versa.

The idea is too see something like:
Customer x y
ABC 100
DEF 100 100
GHI 100
Where ABC is a Customer that exists in database x but does not exist in database y.

DEF is a Customer that exists in both databases.

GHI is a Customer that exists in database y but does not exist in database x.

I want to see all customer records for both database x and y.

At the moment I am only seeing subreport data where x.Customer = y.Customer. The main report still shows me all records in x.Customer.

I have tried linking and unlinking but it doesn't seem to make much difference (except that unlinking gives me 0 records).

Any suggestions most welcome.

 

[dbielenda]

Have you tried an outer joins? Maybe that will help.

 

[Ngolem]

How are the tables linked? Are you just returning one value for each customer?

If so you can use the Subreport to return a shared variable that is initialized to zero(or a ridiculous number if zero is a valid return eg. -9999999)...and updated to the value you are looking for with that customer.

Perhaps that would work...need more details

 

[LFB]

Hi, thanks for the responses.

I need to give you more detail I think.

I am using one table in the main report. I have one group in the main report, Customer. I have created a sub-report that uses a different table. It is actually a TimeSheet table and it calculates total labour cost. When I drop the sub-report into my main report I am using the 'Subreport Links' function for Customer.

In the main report I am getting total labour cost for each Customer which is fine. Problem is I have additional customer records in my TimeSheet table that I don't have in my main report.

What I cannot work out is how to get the sub-report to display the additional records.

I would expect to see additional lines on my report where the main report has no corresponding group that matches the additional entries in the sub-report.

So if the subreport has customer GHI, and the main report doesn't, I would expect to see an extra line in the main report with an amount for customer GHI. How to get the name to display is another problem. At this stage I am just trying to get the data into my report.

I thought the problem might be with the 'Subreport Links' since it creates a parameter that filters the data in the subreport according to what customers exist in the main report. However, I tried clearing the check box 'Select data in subreport based on field:' but it still don't work.

If I have no link it will simply print the total for all customers for each group in the main report, I don't want that.

Thanks again for any suggestions.

 

[kenhamady]

You can't launch a subreport linked from the main, if there is no record in the main. You have to start with a table that has all customers. Do you have a customer master table? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SmpTraining]

Ken's right. Create a main report with just the customer table in it and use two subreports, one for table X and one for table Y.

Link to each subreport with Customer ID

This way you will end up with all customers even if they don't have entries in table X, table Y or BOTH! Steve Phillips, Crystal Consultant

 

[Ngolem]

How many customer records in this timesheet table are there??.

One suggestion is not to link the subreport at all..have it run in the header.

create shared arrays in the subreport that will store the customerID and Total labour in separate arrays

eg

shared StringVar array CustomerID := ["","",....""];
shared NumberVar array TotalLabour := [0,0,....0];
numberVar counterflag := 0;

Make the arrays 100% larger than you need to a max of 1000 elements...you can use more arrays if necessary for greater than 1000 elements.


that would be your initialize formula above

now do the calcs of the labour and assign the results to the CustomerID and TotalLabour arrays

Put this subreport in the report header of the main report to run immediately....Suppressing all sections of the subreport will suppress it in the main report and make it as small as you can...

In the main report now you would refernece these shared arrays to get the reslts when you want them


I do this alot and it works well

Jim