Linking Reports in Crystal 8.5

[cis12232004]

I have a report that generates the inventory items that are on order and I want to link this inventory report with another report that generates the usage for each inventory item that the company uses. My question is, how do I integrate the two reports without losing any data since there are items in the usage analysis report that will not have any items on order but I still want these items to show. I'm using Solomon 5.5 to generate/display the crystal reports.

Thanks for ANY help I can get..............

 

[lupins46]

Sounds like you need to add the Inventory report into the Usage report as a subreport rather than the other way round.

 

[cis12232004]

Will try that, thanks lupins46!

 

[cis12232004]

Okay, I tried the subreport and ran into a problem. I have inventory IDs on both reports and I want these to be together. Right now I have an inventory ID starting with 100 on page 1 and a similar item on a separate page. How can I bring like items together so that the report flows smoothly i.e. from IDs in the 100s to 200s etc.

 

[lbass]

You could create a group on inventory ID and then link the subreport to the main report on inventory ID, placing it in the group header or footer section.

-LB

 

[cis12232004]

I have a problem, I grouped the report as suggested, but what is happening is that no matter what section I put the subreport, it is still duplicating itself 20 times or more over and over again. Am I missing a step?

 

[lbass]

DId you go to edit-> subreport links and link the subreport to the main report on inventory ID?

-LB

 

[cis12232004]

lbass, thank you so much for your suggestions and patience. I linked the reports as stated and now I have another problem. I have more data in the primary report than the linked report. Is there anyway I can get all the data from the main report to show that don't have any corresponding data in the sub report? In other words, I want all the data from the main report AND all the data from the linked report to show and I want them organized based on InventoryID. Is there any way this can be done?

Thanks a mil for ANY help...........

 

[lbass]

A subreport link acts like a left outer join, so you should automatically see all data in the main report and the subset of data in the subreport that matches the ID field in the main report.

You aren't using the table that is in in the subreport in the main report, are you? If that table appears in the linking expert in the main report, remove it. If you need it there for some other reason, make sure you have a left join from the Usage table to that table.

-LB

 

[cis12232004]

lbass, I apologise for all these questions but this is my first attempt at doing subreports and it is really challenging me. These are the steps I am taking to create the report:

1. Open the main report.
2. Insert the sub-report in the Group Footer section of the report.
3. Go to edit subreport links and select the field to link to.
4. Refresh report.

This report should only be approximately 60 pages and I am getting thousands because it is repeating the data from the subreport.

The data generated from the subreport is still being duplicated numerous times. If I open the main report I DON'T see the subreport as a separate report, rather I see the link in the group header. You are asking me if I'm using the table that is in in the subreport in the main report but, sadly, I don't quite understand what you mean. I checked the linking expert and the subreport table is not there. Can you tell what I'm missing?????????????

 

[lbass]

What is the group structure of your main report? Please supply a sample that shows the group header(s), details, and group footers(s) and identifies where the subreport is located.

If in fact you have only one group on inventory ID and you have linked the subreport to the main report on this field, and you have located the subreport in the group footer section, it will execute once for every inventory ID, but the subreport will only show data for that specific inventory ID, so that it would not be duplicate data.

Please explain clearly the results you are getting.

-LB

 

[cis12232004]

Ok, the subreport is in the group header and the information for the main report is in the group footer. There is nothing in the details section and the totals are in the report footer.

The result I am receiving is just a repetition of the subreport. I don't see any of the data from the main report despite the fact that I can see all the inventory IDs in the left "margin" but when I click on them, nothing displays - just a blank screen. Does this give you a better idea????

 

[lbass]

Not really. What is the data that you expect to show up in the group footer? Have you checked to make sure the group footer is not suppressed? Please show how you expect the report to look by showing some sample data, indicating what you think should show up in the subreport and what should appear in the main report.

-LB

 

[cis12232004]

In group footer 2, the main report should display the inventoryID and Description, Ending Balance, the Usage for the period, Unit Cost and Extended Cost, which it is now doing.

In group footer 1, the subreport should display the inventoryID and Description, VendorID and Name, PO Number, PO Date, PO Type, Qty Ordered, Unit Cost and Extended Cost, which it is now doing, albeit at the end of the main report.

The reports are grouped on the inventoryID and what I want to see is the data displayed in numerical order with any item from the subreport and the main report that have the same inventoryID be grouped together and the other items that do not have a matching items in the subreport displaying numerically. Right now I have the main report displaying first and the subreport displaying after that. I have made corrections that prevent it from duplicating the subreport. Now I need to "merge" the two reports based on the inventoryID so that they flow sequentially as "one" report.

Does that help?

 

[lbass]

The subreport needs to be placed in the GROUP footer, not the report footer, and linked on the inventory ID field. You should not be eliminating recurring instances of the subreport. It sounds like you still don't have the link working. When you set up the links, make sure that the inventory ID is selected for the main report and for the subreport.

-LB

 

[cis12232004]

LB, it's me AGAIN! I have been working on this report and there is still a problem with linking. The two reports have one field in common and both fields also have the same name @inventory. When I go to Edit/Subreport Links, I choose the @inventory as the field to link to and I am not selecting the link to "Select data in subreport based on field". The report is printing the Usage Report first and then printing the Open PO report after. It is still not integrating them. When I reversed the process and used the Open PO report as primary, it integrated but I lost all the other data that didn't have any open orders. Any idea why it is not working the way that I need it to work?????????????

 

[lbass]

In the edit subreport links screen, you should select {@inventory} from your main report and move it to the right and then in the bottom right corner select {@inventory} from the subreport and "select data based on field" should be checked.

-LB

 

[cis12232004]

lbass, your patience in helping me resolve this issue is commendable. I did as you suggested and the information from the subreport is not showing at all. Question, eventhough the two fields have the same name, they are made up of different data, would that affect the outcome of the report? The main report @inventory represents vr_xxxx.InvtID and vr_xxxx.Descr while the subreport @inventory represents purOrdDet.Invt.ID and Inventory.Descr.

 

[lbass]

If the results of the two formula are different it would absolutely make a difference. The results have to match in order for linking to take place. Maybe you should show a sample of the fields in your main report and a sample from the subreport and explain what you are trying to achieve by linking.

-LB

 

[Jcfx]

Cis.. is this the data format you are looking for?

10202Y    Marker Light Amber   75  5  $1.20  $90.00
	10202Y Marker Light Amber  TRKLT  Trucklite  125489  08-15-2005  Standard  10 $1.20  $12.00
	10202Y Marker Light Amber  TRKLT  Trucklite   120750  07-12-2005 Credit       -5 $1.20  -$6.00
10202R    Marker Light Red  25   2  $1.20  $30.00
	10202R Marker Light Red  TRKLT  Trucklite  125489  08-15-2005  Standard 2 $1.20 $2.40

Is there a reason you are using @inventory, instead of vr_xxxx.invtID = purOrdDet.InvtID?


Julie Cox
CR 9 and 10
CE 10
Sql Database