matrix and empty columns missing

[sjjustina]

I have a matrix report with Theatre Name as the row and grouping on the theatre name. The columns are items like candy, popcorn, drinks, food etc.

The problem is that not all theatres have all the items so when it gets to say Ice Cream some theatres there is no Ice Cream column and then the columns get off

theatre1 candy drinks combos ice cream popcorn
theatre2 candy drinks combos ice cream misc other popcorn
theatre3 candy drinks combos misc popcorn

This is a separate matrix from the main report because I needed a grand total of the entire theatre dollars to do a percentage. I tried having the theatre names as columns instead of rows but once I grouped on theatre name the matrix didn't expand sideways but instead the theatres were one below the other.

The main report has a matrix with the theatre name in the column and the category details as rows and when one theatre has items but another doesn't it still displays the row correctly just with no data. So how come the columns don't do the same?

It doesn't matter which is the column or row but I have to group by theatre name for the grand totals.

Is there a way to have the matrix display blank columns for categories that have no data? Or group by theatre name and have the matrix expand to the right?

Thanks in advance,
Sarah

 

[bessebo]

Sarah,
If you are only running for one theatre, if this theatre does not have a particular column then it will not show. But, if you are running the report for all theatres and somewhere in the dataset there is at least one occurrence of each column, then all columns should show up.
What I have done in the past is, instead of utilizing a matrix, I will simply perform all of my calculations in SQL either in a stored procedure or just in the dataset. You can then pre-populate all columns totals with 0s and then just create a report rather than using the matrix. This gives you a lot more flexibility and you don't have to be constrained by the almighty matrix.




Cheers,
Bessebo
(website:

http://www.datagonewild.com)