formatting a number inside a crosstab, according to crosstab average

tapani108

I have a crosstab displaying visitor amounts. These numbers can vary greatly, from under 100 to over 10 million. What I would like to do is to look at the general average of the whole crosstab.

If the average is over 1000, then format the numbers like 0.8k, 1.8k, 2.6k and so on. If the average is over 1 000 000, then format the numbers like 0.8M, 1.8M, 2.6M and so on.

To make this more interesting, the crosstab has two groupings: grouping by area, and for each area another grouping by data type. Apart from data type, there are transactions and other types of data. This formatting would only apply to the visitor data type.

Would this be easier to do in the data set, with a computed column perhaps? I looked at computed columns, but haven't found a way to use the whole dataset for calculations. Or should this be done in the dataset's SQL query?

Advice for best practice on this greatly appreciated!
/T

mwilliams

Hi T,

Depending on your data, you may be able to use the average function for a computed column on the column you're wanting to know the average of. If there's multiple values with the same crosstab dimensions, this probably won't work though. Let me know.