Subreport data incorrect 1

[Luvsql]

I have a standalone report which prints the correct information, however, when I use this report as a subreport on another (linked by a single field called EquipmentCode) the reports show different data. For example, on my subreport on its own, one invoice will show 3 different items. When I have this report as a subreport, linked on the item, it only shows a single item per invoice. If I'm only linked by item (field EquipmentCode) should the reports not be identical?

The only difference is that I have to use a subreport as I need to show items with no invoicing (so there are nulls in the subreport). It should those items correctly but could that be what is affecting only showing a single item per invoice?

 

[lbass]

Linking on a field means that the sub will only display fields that match. It sounds like you need to remove the link altogether.

-LB

 

[Luvsql]

My main report does have those items in the report as does the subreport. If I remove the link then the subreport prints everything. I only want the data in the subreport to print items from the main report as the main report contains the restritions on which items to print. The subreport contains the item invoice details (which have a date restriction).

 

[Turkbear]

Hi,
Where in the main report are you inserting the sub and where is the main report's field that you are linking the sub's field to?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Luvsql]

The Subreport is in GH1, which is a formula based on grouping the items. The subreport is linked to the main report on the item code. The same formula and group appears in the subreport, but if I link the main and subreport by that formula, it hangs and hangs.

 

[lbass]

So if the first instance of the groups shows items 1,2,3, do you want the sub to show those same items? If so, then you have to link on the group field. Maybe you should show the content of the formula you are grouping on.

-LB

 

[Luvsql]

If I add a GH2 on the Item, then insert the subreport into GH2, I do then see the missing items, however, I don't want it broken out by Item. I want to show the grouping as it shows in the subreport. If I place the subreport into the details section, it shows the missing items, but then is still grouping the subreport data by item.

 

[Luvsql]

I really don't want to use subreports because of their issues, but I need to be able to join to a second table (invoice detail) but show all items from Equipment Table and those records from teh Invoice detail table for a date restriction. As soon as I do a date restriction on the Invoice Detail, it becomes an equal join, but I need my report to show items with no data in the invoice detail.

 

[lbass]

You haven't shown the content of your group field, so it is hard to tell what is going on. I'm assuming there are multiple items within each group on the main report, and you want the same items to show up on the subreport for that group. The sub should be in a group section, linked on the group field in that case.

Another approach which would solve the left join problem when there are criteria on the right hand table, is to use a command as your datasource, where you add the criteria in the 'From' clause instead of the Where clause:

select Equipment.field, InvDet.field
from Equipment
left outer join InvDet on
Equipment.Code = InvDet.EquipCode and
InvDet.Date >= {?Start} and
InvDet.Date <= {?End}

...where the {?Start} and {?End} are parameters created within the command. Adding the criteria to the From clause will result in all records from the Equipment table, and only those records from the InvDet table that meet the date criteria.

-LB

 

[Luvsql]

I ended up creating a sql view to create the item groups, then used this in both the main and subreport. I then linked on both the Item group and Item code as suggested and all is good!