Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

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.
0
DCRAPACCESS
Asked:
DCRAPACCESS
  • 4
  • 3
1 Solution
 
mbizupCommented:
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.
0
 
DCRAPACCESSAuthor Commented:
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?
0
 
mbizupCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mbizupCommented:
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.
0
 
DCRAPACCESSAuthor Commented:
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.
0
 
mbizupCommented:
What you are asking is not very clear (at least not to me) ... that is why I asked for specific sample data in my last comment.  

You might want to take a look at this article, under the heading "TOP n records per group".

http://allenbrowne.com/subquery-01.html

If implemented correctly, that method will list the TOP 10 records for each period.  Is that what you are looking for?

If not, please post the data I asked for at http:#a38386345.
0
 
DCRAPACCESSAuthor Commented:
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!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now