How do I create a crosstab report with variable headings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a crosstab query and want the same format in
my report.

TOP20 Col1 Col2 Col3
Row1 X x
Row2 x x

Column headings will be variable as data changes from month to month. Does
anyone have any ideas. Help please.
 
Dynamic field names are a problem for reports. The answer is not simple, so
you will need to be familiar with VBA code and SQL strings to achieve this.

One solution is to put enough text boxes into your report to meet the
demands of the maximum number of fields the query could generate. Leave them
unbound, and name them Text0, Text1, Text2, ...

Then in the Open event procedure of the report:
1. Retrieve a list of the values the column names.
2. Assign them to the Control Source of the text boxes.
3. Assign the Caption of each label above the controls.
4. Hide the unused text boxes and labels.
5. Set the Left and Width of each text box and label to make best use of the
available width of the report.

Unfortunately, a crosstab has to run to completion before all the column
names are known. If the query is complex or has lots of data, running the
query again in Report_Open to get the field names will mean the report takes
a long time to open. You may be able to work around that by using
OpenRecordset() on a query string that gets you all the possible values for
the column headings, and use that. In this case, you might also want to
assign a SQL statement to the report's RecordSource so you can include the
PIVOT clause (the Column Headings property of the crosstab).
 
Back
Top