Subreport filtering data when no parameter provided.

[raven4]

Crystal 9.

I found an article on this site that seems to be similar to a problem I’m having, but I’m not sure it’s exactly the same:

http://tek-tips.com/viewthread.cfm?qid=1582683

I have a report that is grouped by
GH1 - task force name
GH2 - ranking on task force (chair, vice chair, member, etc.)
GH3 - task force member name last-first

I’ve included a sub-report to list relationships for those members of the task force that have them, with three options for the relationship parameter in the sub-report (assistant, advisor, mentor.) This sub-report is in the GH3 section and it is linked to the main report by task force name and task force member ID number. I can see from the thread that I mentioned above that if the sub-report is in a GH area that it will automatically filter based on the first parameter option (assistant, in my case) whether a parameter for that option is provided or not, which is exactly the result I’m seeing right now.

Where I think my case differs is that I am not trying to filter the records in both reports – only in the sub-report. I want to see all members of the task force in the main report and then in the sub-report what I need to have happen is that if no parameter is provided for the relationships in the sub-report, then no relationship should print at all. If assistant is picked, then print assistant for those records in the main report that have one, if assistant and mentor are picked, print those relationships for those records in the main report that have them, and so on.

This seems like such a simple thing and maybe I’ve just been looking at this for too long so that now I can’t see the forest for the trees.

Thanks in advance.

 

[lbass]

I think you should first be linking on all group fields (not on a separate field that you are NOT grouping on). I'm also assuming that 'ranking' is different from 'relationship'--if it is the same, then the approach would be different. Then create a parameter {?Relationship} in both main report and subreport. In the sub, go into report->selection formula->record and enter:

{table.relationship} = {?Relationship}

In the main report, go to edit subreport links->move {?Relationship} over on the top (as your fourth link) and then use the dropdown in the lower left to select {?Relationsip}, not the default {?pm-?Relationship}. This will ensure that you are only prompted once for this parameter.

-LB

 

[raven4]

Thanks, LB. I followed your recommendations so that now the links to my sub-report are on the 3 group fields. I also created the parameter as you suggested and added that as my fourth link. My result is still the same - even when I select no parameter for the sub-report the first relationship (assistant) prints on the report.

At first I thought this was due to the fact that the sub-report was in the GH field, but if I move it to the details field I get the exact same thing (the assistant relationship even though I didn't pick it) but with duplicated data. If I move it anywhere else I don't get any results.

 

[lbass]

Please show the content of your record selection formula in the sub. Also identify the name of the field that returns the "relationship" and confirm that this is different from rank.

-LB

 

[raven4]

Yes, relationship is different from rank. Here's the record selection formula from the sub:

{vMyView.TASK_FORCE} = {?Pm-vMyView.TASK_FORCE} and
{vMyView.RANK} = {?Pm-vMyView.RANK} and
{vMyView.RELATIONSHIP} = {?Relationship} and
{vMyView.LAST_FIRST} = {?vMyView.LAST_FIRST}

Thank you.

 

[lbass]

Are you by any chance showing the relationship field in a group section of the sub? You should be showing it in the detail section of the sub.

Can one person have more than one relationship? If so, how would you want that displayed in the sub?

-LB

 

[raven4]

Well, of course, this turned out to be much simpler than I was making it out to be. Thanks to a colleague of mine who took a look at the report, it turns out that the relationship parameter was defaulting to the first value, regardless of the fact that I was not actually selecting to "Add" it at the time the report was run. I simply ended up adding a parameter option of "None" to the list and made that my first choice (obviously knowing that "None" would never be the code for a relationship) and now the report is running perfectly.

Thanks, LB, for your willingness to help - at a minimum your suggestions helped me find another issue that I wasn't even aware of (I was missing some relationships because of the way I had the reports linked originally.)

I was right - couldn't see the forest for the trees!