Don't Render Blank Crosstab

[Revan]

Hey all,
This seemed like a pretty simple simple report when I was tasked with it, but I'm struggling to figure out an acceptable report expression to NOT render a crosstab when it is blank (shows up on Excel as "No Data Available").

I was tasked with creating a dynamic Fiscal Year report for the amount of transactions we do. I created a Base Query that pulls in all the info for that fiscal year, then I made 12 new queries pulling from that Base Query, one for each month, and filtered each of those so they only return their month's data from the Base Query. I then set up a page view with 13 crosstabs (the top one is Year-to-Date, then one for each month). Our fiscal year is July-June, so if we run this report in September, there should be data for crosstabs Year-to-Date, July, August, and September, and then the report should end. Instead, it's pulling in that data, then returning the other 9 crosstabs as "No Data Available". I've tried a whole variety of report expressions to create a Render Variable, but I keep getting errors. The crosstabs are set in a table, if that makes any difference.

Code Select Expand

[June].[Transaction Count] < 1
gives error: RSV-VAL-0032 The following expression is not valid: [June].[Transaction Count] < 1. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error occurred at or near the position '0'. The variable named '[June].[Transaction Count]' is invalid.

[Transaction Count] exists in each query, AND is referenced in the layout (in the crosstab, as the measure), so I'm not sure what to change.

Code Select Expand

[June].[Transaction Count] is null
gives the same error. I figured maybe since theres no data at all, that '< 1' was the wrong syntax, and that it's just null, but nope. I've tried suppression on the crosstabs to no avail as well.

Also, if this is a clunky way to set up a Fiscal Year report, and you have a better suggestion, I'm all ears. This setup makes sense to me (and works, if I can get the "No Data Available" thing worked out), but it takes about 20 minutes to run. I'd love to be able to shave that down quite a bit.

Thanks!

Relational Data source.

[New_Guy]

Hi,
Have you tried to set the No data contents property of the crosstab to 'No Content' and you won't be seeing the No Data available for the 9 empty crosstabs.
Good luck
New guy

[Revan]

Haha of course it would be something that easy! I had hoped I was missing something simple. Seemed like too simple of a problem for the creators NOT to have thought of. Running the report now. I'll get back in about 20 minutes when it's finished running to verify its cleared up.

Thank you.

[Revan]

So it seems like it's still reserving space on the page for the crosstabs, even though there's no data showing (which is a nice change). So I have my Year to Date crosstab, then multiple blank lines on my worksheet, then the crosstabs with data showing. Is there a way to make the crosstabs (or table cells) just not render at all on the output?

[New_Guy]

Hi,
Instead of doing a separate query for each crosstab, try doing one crosstab for YTD. And use a list with Month as the first column and in the 2nd column place the crosstab and do a sectioning based on the month. This way it will produce a crosstab for each month. And do box type none for the month column of the list.
New guy

[Revan]

This worked like magic, New_Guy! Thanks for the assistance.