Embed Crosstab Queries in Reports

[Guest_imported]

Hi,

Does anyone know of a way to embed a query into a report without it becoming a subreport? I have a crosstab query whose column headings change each month due to the level of activity in the field.

Example: The query has a "Branch" field, and an "item" field. If a particular item had no activity for a given month, it will not show in the query.

I need to base a report on it, but reports look at static fields. A query whose information can change dynamically within the report would solve the problem.

Any ideas would be appreciated?

Thank you.

 

[MichaelRed]

Not a direct answer - but ...

You can force the column heading in a crosstab query. There are two approaches I know:

[tab]Use the ColumnHeadings property of the crosstab query itself. This is the easier method, but has the drwback that ONLY the headings listed in the property will 'survive' and be iincluded. If your data changes, you need to A) recognize that there is 'new' info and; B) adjust the column headings property.

[tab]Create a list of the REQUIRED headings in a seperate table / query. Use this as an additional part of the recordsource of the crosstab query. Make it the 'forced' entry part of a Left Join onto the piviot field. EVERY record from this list WILL be included in the result.
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JeffH]

If you post/send me your email, I can send you a procedure and a report that handles crosstab queries. Just call the procedure and pass into it the cross-tab query name, and a title for the report--easy as that.

Jeff
gus_hammond@zdnetonebox.com