Crosstab in a Subreport running slow...not due to dynamic headings

[Alberto]

I am having problems running a report that utilizes a subreport based on a
crosstab query. It is extraordinarily slow.

I've made the column headings in the crosstab query static. The subreport
(based on the crosstab query) when run independent of the report takes about
2-3 minutes to run. The report when run without the subreport takes 5-10
seconds to run. The query, independent of any report, takes about 10-20
seconds to run. But when I put the subreport based on the crosstab into the
report, I wait over 20 minutes.

Please help.

 

[Alberto]

I am realizing that I did not provide enough information here, so here is
more info:

I have two queries.

One query (qryInventory) pulls the current inventory levels [current], min
inventory levels [min], and optimal inventory [opt] for a given product model
[product_model].

The other query pulls the each transaction for each product model over the
last 14 weeks. I am then utilizing a CrosstabQuery (qryProductSales) to show
the quantity of product models sold each week.

My problem comes when I want to combine these two queries. The way I have
attempted to do it is to create a report based on qryInventory and create a
subreport based on qryProductSales and join then by product_model.

The only query that takes a long time is the qryProduct Sales. It takes
about 20 seconds. All the other queries and reports/subreports, when run
individually take a matter of seconds, but for some reason when I run the
report and subreport together in the fashion described above, it takes an
extremely long time (so long that I have not been able to wait it out...30+
minutes).

Also, I should note that my column headings are static dates (i.e. week 1,
week 2, etc.) so I don't think that this is the problem.

Please help.

 

[Alberto]

I figured out how to get the report done without utilizing a crosstab query
within a subreport and was able to put the crosstab query into the main
report. That solved the issue.

I am realizing that I did not provide enough information here, so here is
more info:

I have two queries.

One query (qryInventory) pulls the current inventory levels [current], min
inventory levels [min], and optimal inventory [opt] for a given product model
[product_model].

The other query pulls the each transaction for each product model over the
last 14 weeks. I am then utilizing a CrosstabQuery (qryProductSales) to show
the quantity of product models sold each week.

My problem comes when I want to combine these two queries. The way I have
attempted to do it is to create a report based on qryInventory and create a
subreport based on qryProductSales and join then by product_model.

The only query that takes a long time is the qryProduct Sales. It takes
about 20 seconds. All the other queries and reports/subreports, when run
individually take a matter of seconds, but for some reason when I run the
report and subreport together in the fashion described above, it takes an
extremely long time (so long that I have not been able to wait it out...30+
minutes).

Also, I should note that my column headings are static dates (i.e. week 1,
week 2, etc.) so I don't think that this is the problem.

Please help.

I am having problems running a report that utilizes a subreport based on a
crosstab query. It is extraordinarily slow.

I've made the column headings in the crosstab query static. The subreport
(based on the crosstab query) when run independent of the report takes about
2-3 minutes to run. The report when run without the subreport takes 5-10
seconds to run. The query, independent of any report, takes about 10-20
seconds to run. But when I put the subreport based on the crosstab into the
report, I wait over 20 minutes.

Please help.