Report Pulling from Crosstab Query Fails due to Query Results Not Always Matching with Text Boxes

[C Muth]

Hi, I have a Report (Report1) which has a source Crosstab Query (Crosstab1) which runs from an underlying query (Query2). Query2 prompts the user to enter a date range. Report 1 has customer names across the top, months down the side and sales $s in the body + totals. Depending on the date range entered by the user in Query2, Crosstab1 will not always have all the customer names reflected in Report1.


The result I desire is to simply have Report1 show a blank when there is no data in Crosstab1 for that customer, however still allow totals to calculate.
I am aware of, however have limited experience with the use of Can Grow, Can Shrink. Not sure if a fix.
Thanks

[Homegrownandy]

could you describe what you are trying to achieve without referencing any work you have done pelase?

What is the report for? for example.

[Micron]

This is a common question and there are answers posted in lots of places. I've read them, but unfortunately have never applied the solution, which is to define the fields (make them static). Thus I can't describe the fix for you. A crosstab query isn't appropriate for your report because customers as fields is too dynamic - both in terms of count of customers with orders at any given time but also the size of the customer list itself. The reason it fails is because you're transposing row (records) data into columns (fields) but the number of fields is not static, thus sometimes there are too many fields for the report you designed. IMHO you are defeating the purpose of a report, which is to group and aggregate data but usually vertically. If a normal report has no data for Customer One they just don't show up. Why can't you just design a report based on a normal query?

[apr pillai]

I know this will involve some work to implement, but if it gives you some insight to approach your problem solving task then take a look at this link: Dynamic Report.

[Micron]

I'd be surprised if that approach works. There are only ever 12 months in a year, so it suits the design approach. It is not likely that there will ever be a fixed number of customers, which as far as I can tell here, are the fields, so it won't. As soon as you gain or lose a customer, there are not enough or are too many fields so you're back where you started. That's why I said it wasn't suitable for their situation.