Comparing data between two reports

[adubrovs]

Hi,

I'm trying to create a main report that compares a list of records against a list of records created in a sub-report. If the records match, then I do not want that record to be displayed on the main report.
From what I understand I need to create a shared variable on the sub-report so i can pass that to the main report:

whileprintingrecords;
shared stringvar security := {CONSTRAINT_GROUPS.SECURITY}

then a corresponding variable on the main report:

whileprintingrecords;
shared stringvar security

I placed the sub-report in the details section of the main report.
At this point I can't seem to be able to hide the records that match on both reports.
I've tried using Selection Expert, but not sure if that's the correct route.

Any help would be greatly appreciated.

Thanks.

 

[lbass]

Is the subreport linked to the main report on some unique detail field or is it unlinked? Is it your expectation that there might be a record in the sub but not in the main report as well as vice versa?

What is the rationale for going the subreport route instead of introducing the two tables into the same report?

-LB

 

[adubrovs]

Basically i'm trying to get CR to show me a list of securities held in a portfolio which are not on an approved list (which is my sub report).

My main report contains portfolio code and security code, my sub report only contains security code.

If there is a way to do this just by introducing the two tables in the same report that would be great. I have tried doing that but with no luck.

 

[lbass]

Okay, I see. Link the sub on the security code field, and place the subreport in detail_a, with the main report fields in detail_b. Then assuming you have created your shared variables as in the original post, go into the main report section expert->detail_b->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar security;
{mainreporttable.securitycode} = security;

If you want the subreport not to show, suppress all sections WITHIN the sub, format the sub in the subreport tab of the main report->suppress blank subreport, and in the section expert of the main report->detail_a->check "suppress blank section". This will cause the entire detail_a section to disappear (you might need to remove the borders around the sub).

You should also insert a detail_c (suppress it) where you place a reset formula:

whileprintingrecords;
shared stringvar security := "";

-LB

 

[adubrovs]

thanks...will give it a try first thing Monday....

 

[adubrovs]

for whatever reason i'm still having issues. the main report is not suppressing the securities which are on the sub-report ....from the looks of it i've followed the steps so not sure what the problem is at this point....

 

[lbass]

No, this is set up so that a match in the subreport will suppress the match in the main report, not vice versa. The subreport should not appear at all if you follow my previous instructions, and you would be left with a list in the main report that shows securities that are not on the approved list.

-LB

 

[adubrovs]

ok, i got the subreport working. However, even if i check supress blank sections in detail-a, i'm still showing blank lines when i preview the report.

 

[adubrovs]

also, when i try to do a count of the securities not on the subreport, it still gives me a count of all the securities.

 

[lbass]

Please explain the steps you took to make detail_a disappear. There also can ONLY be the sub in that section.

To do a count, you can't insert a count, since it will still pick up suppressed records. You need to build a separate variable formula to do a count:

//{@cnt} - for the detail_b section:
whileprintingrecords;
shared stringvar security;
numbervar cnt;
if {mainreportsecurity} <> security then
cnt := cnt + 1;

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar cnt;

If you want a count per some group, you would have to add a reset formula in the group header, and place the display formula in the group footer.

//{@reset}:
whileprintingrecords;
numbervar cnt;
if not inrepeatedgroupheader then
cnt := 0;

-LB

 

[adubrovs]

For detail-a, i just have supresse blank sections checked in the selection expert.

 

[lbass]

As I said earlier:

If you want the subreport not to show, suppress all sections WITHIN the sub, format the sub in the subreport tab of the main report->suppress blank subreport, and in the section expert of the main report->detail_a->check "suppress blank section". This will cause the entire detail_a section to disappear (you might need to remove the borders around the sub).

-LB

 

[adubrovs]

Forgot to check the suppress blank subreport... All good.

Thanks for your help....

 

[adubrovs]

One more thing...it appears that if i hide detail-b, my count gets messed up. Once i drill down to the security level it shows correctly. I basically want to be able to drill down to the security level from summary level.
Is there a way around this?

 

[lbass]

You should have mentioned drilldown at the beginning. You can't hide or suppress the section containing the subreport because the shared variable will not then pass.

Are you sure you need a subreport? Could you add the conditions that result in the approved list into a conditional formula that you then use to suppress matching records?

-LB

 

[adubrovs]

The problem is that the securities contained in the sub-report change. That's why I wanted a sub-report to ensure I'm constantly working with the most up to date data.

I'm not sure I could do a conditional formula since I can't link the two reports together.

Anyway, not being able to drill down is a huge deal.

Thanks again for your help.