Filtering subreport based on main report

[Guest]

Hoping that there's an easy answer for my dilemma (short of entirely
redesigning my database!!)

I have a main report to summarize data from several medical clinics, which
runs off a form which prompts for the clinic's name to summarize (POS = point
of service). so, the form generates the filter for the main report like
this:

(POS = Forms![POS Selection Dialog]!SelectPOS)

this works fine, and prints the appropriate POS name into the main report.

now, my problem is that a lot of the subreports are based on series of
queries that generate counts of data, for instance, a count of the number of
males and females where the query looks like this:

SELECT Count(*) AS [Count Of Gender], BaselinePatientData.Gender
FROM BaselinePatientData
GROUP BY BaselinePatientData.Gender
HAVING (((BaselinePatientData.Gender) Is Not Null));

the master and child links for this subreport is the field "Gender"

however, the behavior of this subreport (and many of the others) is quite
erratic. with one of the POS selected, i only get the two "Not Documented"
values reported in the gender subreport, and for the other 3 POS, i get only
the females reported (and it's the total number of females across all POS,
not for the POS selected).

i tried adding in the UID for the gender and the BaselinePatientData tables,
but that messed up the gender report altogether, and i didn't get the result
i wanted.

is there a simple way to filter the subreport so that i can get the summary
data that i need only for the POS selected?

thanks in advance,
janaki

 

[Guest]

one more thing here......the report generates multiple copies of the report -
one for each patient in that POS. when i wasn't filtering the main report on
POS, it would just generate the one report as expected.

 

[Allen Browne]

Hi janaki. There might not be a simple answer, but let's try.

You want to be able to filter both the main report and the subreport based
on the POS field, but that field is not present in the subreport's
RecordSource query, so there is no way to filter it. Would it be possible to
redesign this query so it does have this field? Something like this:
SELECT POS, Gender, Count(*) AS [Count Of Gender]
FROM BaselinePatientData
WHERE Gender Is Not Null
GROUP BY POS, Gender;

If you can do this, you can then use the POS field in the LinkMasterFields
and LinkChildFields property of the subreport control, so that the subreport
shows only the count for the POS in it main report.

If you actually want a total count rather than a per-POS count, you could
try:
SELECT POS, Gender, Count(*) AS [Count Of Gender]
FROM BaselinePatientData
WHERE (Gender Is Not Null) AND
((Forms![POS Selection Dialog]!SelectPOS Is Null)
OR (POS = Forms![POS Selection Dialog]!SelectPOS))
GROUP BY POS, Gender;

If none of that works, since you are launching this report from a form, you
do have the chance to build the SQL statement dynamically for just this
launch of the report, and set SQL property of the QueryDef that feeds the
subreport:
Dim strSql As String
strSql = "SELECT ...
dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hoping that there's an easy answer for my dilemma (short of entirely
redesigning my database!!)

I have a main report to summarize data from several medical clinics, which
runs off a form which prompts for the clinic's name to summarize (POS =
point
of service). so, the form generates the filter for the main report like
this:

(POS = Forms![POS Selection Dialog]!SelectPOS)

this works fine, and prints the appropriate POS name into the main report.

now, my problem is that a lot of the subreports are based on series of
queries that generate counts of data, for instance, a count of the number
of
males and females where the query looks like this:

SELECT Count(*) AS [Count Of Gender], BaselinePatientData.Gender
FROM BaselinePatientData
GROUP BY BaselinePatientData.Gender
HAVING (((BaselinePatientData.Gender) Is Not Null));

the master and child links for this subreport is the field "Gender"

however, the behavior of this subreport (and many of the others) is quite
erratic. with one of the POS selected, i only get the two "Not
Documented"
values reported in the gender subreport, and for the other 3 POS, i get
only
the females reported (and it's the total number of females across all POS,
not for the POS selected).

i tried adding in the UID for the gender and the BaselinePatientData
tables,
but that messed up the gender report altogether, and i didn't get the
result
i wanted.

is there a simple way to filter the subreport so that i can get the
summary
data that i need only for the POS selected?

thanks in advance,
janaki

 

[Guest]

Allen, this was perfect!! thank you so much (although this will take a lot
of hours to add the POS field into all the queries, but at least i know it
works!!)

the solution that i wanted was your first one:

redesign this query so it does have this field? Something like this:
SELECT POS, Gender, Count(*) AS [Count Of Gender]
FROM BaselinePatientData
WHERE Gender Is Not Null
GROUP BY POS, Gender;

If you can do this, you can then use the POS field in the LinkMasterFields
and LinkChildFields property of the subreport control, so that the subreport
shows only the count for the POS in it main report.

This worked absolutely perfectly!!

I might need to use your second option later, so i'll keep it in mind...

If you actually want a total count rather than a per-POS count, you could
try:
SELECT POS, Gender, Count(*) AS [Count Of Gender]
FROM BaselinePatientData
WHERE (Gender Is Not Null) AND
((Forms![POS Selection Dialog]!SelectPOS Is Null)
OR (POS = Forms![POS Selection Dialog]!SelectPOS))
GROUP BY POS, Gender;

Again, thanks - your help was timely and much needed to complete this
project!!