DCRAPACCESS
asked on
Filter subreport with value from parent report
Hi Experts.
I have a Report where i group on a value named "Period".
For each Period in the main/parent report i need 2 sub reports to be filtered with the value Period from the Main/parent report.
The problem is that is only need the TOP 10 records to be showing in both sub reports.
As recordsource in the sub reports i have been trying to have a SQL like this:
Select Top 10 * from table_a.
But when i the filter the sub report with the Period value=1 i only get the values from the TOP 10 where Period=1.
Example before filter:
Some_text Period
a 1
b 2
c 2
d 2
e 2
f 1
g 2
h 1
i 2
j 1
Then after i filter the sub report i get:
Some_text Period
a 1
f 1
h 1
j 1
I hope this makes sence and that some one can help me.
I have a Report where i group on a value named "Period".
For each Period in the main/parent report i need 2 sub reports to be filtered with the value Period from the Main/parent report.
The problem is that is only need the TOP 10 records to be showing in both sub reports.
As recordsource in the sub reports i have been trying to have a SQL like this:
Select Top 10 * from table_a.
But when i the filter the sub report with the Period value=1 i only get the values from the TOP 10 where Period=1.
Example before filter:
Some_text Period
a 1
b 2
c 2
d 2
e 2
f 1
g 2
h 1
i 2
j 1
Then after i filter the sub report i get:
Some_text Period
a 1
f 1
h 1
j 1
I hope this makes sence and that some one can help me.
mbizup
What do you want to see after applying a filter where Period =1? If you filter with Period =1, by the nature of filtering you will be excluding any records where Period is not 1.
ASKER
maybe it me that is doing it all wrong.
I have one report and then i need 2 sub reports that changes it query/recordsource each time the Detail section in the parent reports repeats.
And a value from the parent report called Period is the one that decides what the query/recordsource on the subreports should look like.
How do i handle that, in a effective way?
I have one report and then i need 2 sub reports that changes it query/recordsource each time the Detail section in the parent reports repeats.
And a value from the parent report called Period is the one that decides what the query/recordsource on the subreports should look like.
How do i handle that, in a effective way?
Give this a try in the detail section of your report...
1. Set the Recordsource property of your subreport to:
SELECT TOP 10 * from table_a
2. Apply no filters (leave the filter property blank)
3. Use Period as the Master/Child link for your subreport or add it to the list of master/child links if there are other links already.
1. Set the Recordsource property of your subreport to:
SELECT TOP 10 * from table_a
2. Apply no filters (leave the filter property blank)
3. Use Period as the Master/Child link for your subreport or add it to the list of master/child links if there are other links already.
If that doesn't help, try posting some sample data to show what your Main Report's data looks like, and what you expect your subreport data to look like for each main record.
ASKER
sorry mbizup,
The solution that you describes is the one i was trying to do before writing this question.
The result is that the sub report only shows 3 records and not10. Because it first selects 10 records and then uses that the period from the Master/Child link to filter.
The solution that you describes is the one i was trying to do before writing this question.
The result is that the sub report only shows 3 records and not10. Because it first selects 10 records and then uses that the period from the Master/Child link to filter.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works, but weird that it works if you have the TOP10 SQL as a table and not i the TOP10 is the main SQL for the report. But it works!