Dynamic Crosstab + Sub Report

[deejayquai]

Hi

I've created a crosstab query and displayed it as a sub-report in my
main report. This is fine until the data changes and the column names
become incorrect. I know I have to create a 'dynamic crosstab query'
but I don't know how to!! I've read the "How to..." on the Microsoft
site but it mainly gives an example rather than explain the basics,
which I can't work out.

My context is:

I'm developing an end of year 'Group Member' report for school students
in PE.

Crosstab Query "qryTest" shows [Group Member ID] as the row and then
[Activity] as columns with an [Activity Attainment] as the value. The
activities vary for each group member and can be more or less.

I wish to include this as a sub-report "rptTest" in my main report
linked to the [Group Member ID]. My main report includes other
performance information such as Effort Grades etc and is working fine.
The main report will be previewed from a "frmStudentPerformance" form
and is linked from the [Group Member ID] field on that form. Like I
said- I can get this to work fine as a normal cross-tab report but once
the [Activity] changes between Group Members it falls down.

Regards

David

[David Lloyd]

David:

What you trying to do is quite complex and I do not know the specifics of
your implementation, so my comments here are meant in a general sense.

In the KB "how to" you refer to, the recordset for the dynamic crosstab is
generated in the Report Open event, which unless you are opening and closing
the report/subreport for each group member, the recordset will be generated
the first time the subreport is open and the activities associated with that
particular group member will remain for subsequent group members.

What needs to happen is to regenerate the recordset (which will update the
field names for Activities) for each group member. Depending on how your
report/subreport is setup, you may be able to do this in a PageHeader or
GroupHeader event. You may, although I have not tested this, simply be able
to call the Report_Open event procedure for the subreport from one of these
event procedures, but the general idea is to update the recordset behind the
dynamic crosstab subreport for each group member.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<de********@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Hi

I've created a crosstab query and displayed it as a sub-report in my
main report. This is fine until the data changes and the column names
become incorrect. I know I have to create a 'dynamic crosstab query'
but I don't know how to!! I've read the "How to..." on the Microsoft
site but it mainly gives an example rather than explain the basics,
which I can't work out.

My context is:

I'm developing an end of year 'Group Member' report for school students
in PE.

Crosstab Query "qryTest" shows [Group Member ID] as the row and then
[Activity] as columns with an [Activity Attainment] as the value. The
activities vary for each group member and can be more or less.

I wish to include this as a sub-report "rptTest" in my main report
linked to the [Group Member ID]. My main report includes other
performance information such as Effort Grades etc and is working fine.
The main report will be previewed from a "frmStudentPerformance" form
and is linked from the [Group Member ID] field on that form. Like I
said- I can get this to work fine as a normal cross-tab report but once
the [Activity] changes between Group Members it falls down.

Regards

David

[deejayquai]

Thanks David but I don't quite understand your advice- I'm pretty basic
at this apologies. Are you suggesting that I should set an instruction
up in the event for the page header for the sub-report to referesh the
data? If so what is the syntax?

Regards

David

[David Lloyd]

David:

Yes, the Page Header or another event procedure that represents the point at
which you are switching group members. The purpose is to regenerate the
recordset every time the group member changes. What event you do this in
depends on exactly how your report and subreport are setup, which I do not
have all of the specifics.

The syntax would or should be very similar to the syntax that is in the
Report_Open event procedure of your subreport, or at least very similar to
the Report_Open procedure that is in the following KB article:

http://support.microsoft.com/default...b;en-us;328320

The recordset, which is based on a crosstab query, needs to be regenerated
for each group member, because as I understand you, the activities differ
between individual group members. Therefore the pivot columns will change
with each group member, and you want those changes reflected in the
subreport. Regenerating the recordset is the only way to achieve this since
the recordset fields collection is used to determine the column headings in
the dynamic crosstab report (see same KB article).

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<de********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks David but I don't quite understand your advice- I'm pretty basic
at this apologies. Are you suggesting that I should set an instruction
up in the event for the page header for the sub-report to referesh the
data? If so what is the syntax?

Regards

David