All,
I'm having an irritating problem with a crosstab.
I have one crosstab which has a measure [visits], and a data item: percentage ([Visits] for [Region]) which works fine. See Attachment.
I added a summary line to this crosstab using the automatic aggregation for the two measures.
Again this worked fine but then I had a situation where there was no data for some fact cells, I can display these cells more accurately by forcing the "Missing value characters" to 0 but I find that my percentage aggregation is incorrect.
I have a solution for this by creating a separate query to bring in the Total, joining this to the original query and using this static, unaggregated value to calculate percentages along with a Total percentage. The problem I have is thay I have a series of reports in the same format but with different layouts and I want a nice, clean solution. Can anyone help?
What am I missing?
Stuart
Have you tried using a List instead of Crosstab. In a List, you have more control on the Totals/Percentages etc as every column can have different way of calculating aggregates.
I could, but I like the flexibility of crosstabs when new outcomes are introduced, it automatically creates a new column. With lists, if anything changes I have to go into each report and add two more data iteams.
I've done this in the past where I wanted to show specific columns but in this case I just want to show all possible outcomes.
Stuart