Percentage Aggregation in crosstab

[SynexusStuart]

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

[Steve]

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.

[SynexusStuart]

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